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]