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

[Flemming Harms]

thanks for looking into this.

I chosen a slightly different work around by split the query into two
statements.
But your work around is more simple and I can confirm it also works

2013/1/10 Tatsuo Ishii <ishii at postgresql.org>:
>> 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