To answer my own question. This turned out to be a limitation in pgpool, but to fix the problem we actual had to patch the jboss messaging
The short story is in some situation pgpool return inconsistent state of the database cluster.
My findings and a possible work around can be found in this thread. We choosed a slightly different work aorund to split up this SQL command into two statments to force to always to run on the same node
FROM JBM_MSG WHERE MESSAGE_ID = $1 AND NOT EXISTS (SELECT
JBM_MSG_REF.MESSAGE_ID FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID =
your probably better of posting this on the JBoss Messaging forum not HornetQ
sure okay. it just because when you enter the JBoss Messaging space this messaged is displayed, so I thought it was only an archive
"All new messaging work is now occurring on the HornetQ project, which was branched from the JBoss Messaging 2.0 code-base."
But I just move it, thanks!