[Pgpool-general] Different insert query order on nodes

[Karsten Düsterloh]

Hi!

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?)
- 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.

Should I check the pgpool/postgresql for certain settings? Which?
Or is this a known bug?


Karsten