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)
AS TXID
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
Bug report:
http://jira.jboss.com/jira/browse/JBAS-2956