Problem with pgpool2 degenerate one of the databases with messaging is involved

[fharms]

Hi Guys

I'm investigated a problem we have with pgpool2 degenerate one of the databases in the cluster. JBoss messaging 1.4.8-SP2 is involved in the process and actual it happen when the statement below is executed. Since messaging communicate through pgpool I do not suspect at this point it has anything to do with messaging.

"DELETE

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 =

$2)"

But I got this questions from pgpool forum:

Well there some possibilities:

1) JBoss uses functions which has side effect(updating tables).

2) JBoss uses triggers which updates the table in question.

3) the transaction updating the table in question committed on node 1

   but not committed on node 0 for some reason. Maybe the connection

   between JBoss and pgpool between those two commit commands were

   disconnected?

I have searched through the code and AFAIK messaging are not using any functions or triggers, right?

Link pgpool post http://www.pgpool.net/pipermail/pgpool-general/2012-December/001284.html

Any help will be appreciated, thanks!

Cheers,

Flemming

[fharms]

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

"DELETE

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 =

$2)"

http://www.pgpool.net/pipermail/pgpool-general/2013-January/001324.html

[ataylor]

your probably better of posting this on the JBoss Messaging forum not HornetQ

[fharms]

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!