Bringing military-grade cybersecurity solutions to the enterprise and critical systems.
Telephone: 310.356.7869

ERROR: syntax error at or near TXID

Category: ExceptionOfTheDay

7:37 PM, Wed, Oct 24 2007

It looks like this:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "TXID"
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
                

The problem is in server/default/deploy/jms/hsqldb-jdbc2-service.xml. The offending line is:

CREATE_TX_TABLE = 
CREATE CACHED TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )

and one other line with the CREATE CACHED TABLE instruction. Postgres doesn't support CREATE CACHED TABLE.

This problem comes up when you try to use Postgres as your DefaultDS. The easiest solution is to not use Postgres as the DefaultDS.

Alternatively, create a new HSQLDB datasource, and edit hsqldb-jdbc2-service.xml to use that datasource instead of DefaultDS.