[Pgpool-general] Different insert query order on nodes

[Tatsuo Ishii]

> Karsten Düsterloh wrote:
>> We're running pgpool-II 2.3.3 with two Postgresql 8.3.11 nodes.
>> Certain tables sport an 'id' field, tied to a SEQUENCE as the default
>> value. INSERT queries for these tables come in two flavours:
>> - read next id from SEQUENCE and INSERT with that id explicitly set
>> - INSERT without setting the id, let Postgresql set it automatically
>> 
>> Two weeks ago, out second node got degenerated for a "duplicate key"
>> backend mismatch violation, because the order of INSERTs was different
>> on the backend nodes!
> 
> This happened again last night.
> While the actual order of INSERTs into the table 'zaehler' was ok this
> time, I also had a look at the actual calls to nextval etc., which
> resulted in this layout:
> 
> pgpool:
> - INSERT with id ..11   (from client A)
> - INSERT without id     (from client B)
> - select nextval        (from client A)
> - INSERT with id ..13   (from client A)
> 
> node0:
> - INSERT with id ..11
> - INSERT without id
> - select nextval
> - SELECT count(*)
>     FROM pg_catalog.pg_attrdef AS d, pg_catalog.pg_class AS c
>     WHERE d.adrelid = c.oid
>       AND d.adsrc ~ 'nextval'
>       AND c.relname = 'zaehler'
>   (where does this query come from? pgpool?)

Yes, pgpool issues this query to check if the table has SERIAL column
or not.

> - INSERT with id ..13
> 
> node1:
> - INSERT with id ..11
> - select nextval
> - INSERT without id
> - INSERT with id ..13
> 
> So, again, the relevant queries are not passed to the nodes in the same
> order, which obviously results in havoc.

The reason why you see this is, you don't lock the target table to
properly serialize access order between different sessions.  pgpool
guarantees the SQL order is same among servers in *a* session but does
not among sessions. To synchronize the SQL order among sessions LOCK
is nessessary. You could use insert_lock or could issume explicit
LOCK by yourself.

> Should I check the pgpool/postgresql for certain settings? Which?
> Or is this a known bug?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp