[Pgpool-general] node degeneration on INSERT

[Karsten Düsterloh]

Hi!

We're still experiencing node degeneration with certain INSERTs,
but now (finally!) I have a testcase!

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

This will occasionally fail with
> ERROR:  kind mismatch among backends. Possible last query was: 
> "INSERT INTO locktest(id, ts) VALUES(127, Now());" kind details are:
> 0[C] 1[E: duplicate key value violates unique constraint
> "locktest_pkey"]
> HINT:  check data consistency among db nodes

We didn't test with pgpool-II 3. It'd be good to know if that'd be fixed
there.



Testcase:

1. Setup pgpool with two client nodes:
 replication_mode = true
 load_balance_mode = true
 replication_stop_on_mismatch = true
 failover_if_affected_tuples_mismatch = true

2. Create the following table:
 create table locktest(id SERIAL primary key, ts timestamptz);

3. Create three small shell scripts in one directory:

test1_ex.sh:
============
#!/bin/bash
PSQL="/path/to/your/psql -your -access -options -Aqt -c"
while true; do
  TID=$($PSQL "SELECT nextval('locktest_id_seq');")
  $PSQL "INSERT INTO locktest(id, ts) VALUES($TID, Now());"
done

test1_im.sh:
============
#!/bin/bash
PSQL="/path/to/your/psql -your -access -options -Aqt -c"
while true; do
  $PSQL "INSERT INTO locktest(ts) VALUES(Now());"
done

test1.sh:
=========
#!/bin/bash
./test1_ex.sh &
./test1_im.sh &

4. Run test1.sh and wait for node degeneration.

Step 4 took less then 10 minutes for me until degeneration.



Analysis:

When inserting new rows, the *table* locktest will be locked so that
both INSERTs can't disturb each other. But the dependend *sequence* will
not be locked, hence the explicit call to nextval may happen while the
table is already locked but the implicit nextval didn't happen yet:

Example: (LOCKs are generated by pgpool)

node 0:
[im] LOCK
[im] INSERT INTO locktest(ts) VALUES(Now());
[ex] SELECT nextval('locktest_id_seq');
[ex] LOCK
[ex] INSERT INTO locktest(id, ts) VALUES(xxx, Now());

node 1:
[im] LOCK
[ex] SELECT nextval('locktest_id_seq');
[im] INSERT INTO locktest(ts) VALUES(Now());
[ex] LOCK
[ex] INSERT INTO locktest(id, ts) VALUES(xxx, Now());

Maybe pgpool should/could LOCK implicitly used sequences as well?



Karsten