[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!
> 
> pgpool:
>  - INSERT with id ...68
>  - INSERT without id
>  - INSERT with id ...70
> 
> node0:
>  - INSERT with id ...68
>  - INSERT without id => 69
>  - INSERT with id ...70
>  - INSERT with id ...71
> 
> node1:
>  - INSERT with id ...68
>  - INSERT with id ...70
>  - INSERT with id ...71
>  - INSERT without id => ERROR!
> 
> This is rather worrisome - any ideas what might have caused that?

The culprit, as I found out now, is the *client's* psql version.
While our PostgeSQL servers are running 8.x, our clients still use a
wild bunch of potentially old psql versions (with 7.0.3, 7.3.4, 8.0.1
and 8.3.14 being the most common ones).

Given the table
  create table x(id SERIAL, val int);
and the query
  insert into x(val) values(0);
the resulting PostgreSQL query sequence is different depending on the
client's psql version:

psql 7.x:
> 2011-05-10 10:39:34.358 CEST [25507] LOG:  duration: 0.415 ms  statement: INSERT INTO xxx(val) values(0);

psql 8.x:
> 2011-05-10 10:38:47.036 CEST [24423] LOG:  duration: 0.042 ms  statement: BEGIN
> 2011-05-10 10:38:47.038 CEST [24423] LOG:  duration: 0.045 ms  statement: LOCK TABLE "xxx" IN SHARE ROW EXCLUSIVE MODE
> 2011-05-10 10:38:47.038 CEST [24423] LOG:  duration: 0.162 ms  statement: INSERT INTO xxx(val) values(0);
> 2011-05-10 10:38:47.041 CEST [24423] LOG:  duration: 0.312 ms  statement: COMMIT

The missing table lock with psql 7.x will allow for SERIAL usage
confusion if multiple complex queries touch the same table.

We were going to remove the 7.x clients anyway, so that's not a
particular problem, but I think the issue should be noted in the manual
for replication/loadbalancing as well — it only mentions parallel mode
being problematic with < 7.4.


Yours,

Karsten