[Pgpool-general] Different insert query order on nodes

[Tatsuo Ishii]

>> The queries come from two different clients, hence we end up with two
>> different postgresql threads trying to use the table.
>> I extracted both relevant threads from both postgres logs below
>> (irrelevant internals anonymized, other threads removed):
> [snip]
>> Interestingly enough, only the thread using nextval is locking -
>> but, afaict, too late in the second case?
>> 
>> The order of events on node 0 is that of pgpool's own log,
>> so it seems to me that insert_lock=true isn't quite working as expected?
> 
> Maybe. I'll look into this.

BEGIN
SELECT XXX FROM zaehler WHERE XXX ORDER BY XXX;/*s04.powerkill*/;
INSERT INTO "zaehler"("timestamp") SELECT "timestamptz"('2010-11-16 00:16:53.964797+01'::text) FROM "dslsessions" WHERE XXX

for this queries pgpool-II did not issue the LOCK command when it
should despite the fact that insert_lock is specified. I tried to
clarify what query pattern cause the problem but I couldn't. I guess
the INSERT statement is actually something like:

INSERT INTO zaehler(timestamp) SELECT now() FROM "dslsessions" WHERE XXX

Does above query pattern always cause the trouble(no LOCK is issued by
pgpool-II)?

If so, can you create a self contained test case?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp