[Pgpool-general] Different insert query order on nodes

[Karsten Düsterloh]

Tatsuo Ishii wrote:
>>> 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

Yes, the complete queries from above as passed to pgpool are:

BEGIN
SELECT begintime FROM zaehler
  WHERE typ='XXX' AND ref=4712 AND begintime>='YYY'
  ORDER BY begintime;/*s04.powerkill*/;
INSERT INTO zaehler(timestamp,typ,ref,begintime,val1)
  SELECT Now(),'XXX',4712,'YYY',
    COALESCE(sum(CASE WHEN source='AAA' THEN BBB+CCC END),0) AS val1
    FROM dslsessions
    WHERE ref_user=4712 AND usertyp='ZZZ'
      AND date_trunc('month', begintime)='2009-11-01';/*s04.powerkill*/;

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

Yes and no:
- it only happens for this pattern, used by that particular program
(both last instances failed)
- the insert...select pattern is used with proper locking elsewhere

> If so, can you create a self contained test case?

I'll try, but no success so far.

Interestingly, when I first investigated the insert...select by using
the psql shell I had already open for a couple of hours, it did *not*
LOCK! Passing the very same insert...select query from a newly opened
psql shell *did* LOCK...

The logs for both the non-locking psql shell connection and the program
connection (of the real "powerkill" misbehaviour above) show nothing
unusual, no errors - and no common warnings.
The only common thing I see so far are long times without any activity,
over 2.5 hours in the psql case, almost 24 hours in the "powerkill" case.


Karsten