[pgpool-general: 1313] Re: database detach and I don't understand why

Tatsuo Ishii ishii at postgresql.org
Thu Jan 10 08:02:19 JST 2013


> I think I might have a theory on what might be wrong.
> 
> This is related to JBM_MSG_REF record is deleted/committed in multiple
> threads, and the SQL command has a embedded sub-query that decide if
> it can delete the message or not.
> 
> Because it contains a sub-query it will run on each node and might get
> different result
> 
> "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)"
> 
> I have tried to illustrate the problem in a diagram what I thinks happens.
> 
> Could this be the issue or i'm I on a wild goose chase?

Actually TX #3 was not committed because pgpool detected the number of
deleted rows and did a fail over and TX #3 was roll backed.

Besides this your theory looks correct(I assume "Thread #0 commit #1"
actually means "Thread #0 commit #0"). I think TX #3 (DELETE FROM
JBM_MSG...) should wait for someone modifies JBM_MSG_REF. Is it
possible to modify:

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)

to:

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 FOR SHARE)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


More information about the pgpool-general mailing list