1 Reply Latest reply on Mar 17, 2006 6:33 AM by Adrian Brock

    JBoss 4.0.4RC1 with Postgres 8.1.3

    Mark Raadsen Newbie

      Hi everybody.

      On JBoss 4.0.4, I succesfylly replaced Hypersonic with PostgreSQL (as service under Windows XP) mainly by following the steps explained in the thread http://www.jboss.com/index.html?module=bb&op=viewtopic&t=62379.

      The one thing that didn't work after those steps was the infamous XAConnectionFactory which was not bound. I noticed earlier during the boot process a

      "org.postgresql.util.PSQLException: ERROR: subquery in FROM must have an alias"

      occurred. Via some digging I noticed that bug http://jira.jboss.com/jira/browse/JBADMCON-140 mentioned the same exception, and luckily for me, somehow it was fixed in the to the bug attached files (such as persistenceSql.properties). The fix for that bug was a correction to the SELECT_MAX_TX query, which I remembered seeing in jms/postgres-jdbc2-service.xml (indeed, not the postgres-jdbc3-service.xml file as was stated in forementioned steps) which I had to copy from the JBOSS_HOME/docs/examples/jms.

      So in postgres-jdbc2-service.xml I appended

      SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES)
      


      with

      AS TXID


      so the full query became

      SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES) AS TXID


      A restart of JBoss resolved two things:
      1. No PSQLException about the subquery anymore.
      2. No unbound XAConnectionFactory anymore.

      Compared to the things stated in http://www.jboss.org/wiki/Wiki.jsp?page=IGetXAConnectionFactoryNotBoundHowDoIFixIt I was succesfully able to remove the libraries hsqldb.jar and hsqldb-plugin.jar from /lib directory.

      As far as I can see the \jboss-4.0.4RC1\docs\examples\jms\postgres-jdbc2-service.xml file needs to be corrected so the SELECT_MAX_TX query uses an alias.

      I hope I've helped a few people today with this :)[/url]