[Pgpool-general] node degeneration on INSERT

[Toshihiro Kitagawa]

On Thu, 30 Jun 2011 11:12:55 +0200
Karsten Düsterloh <pgpool-general-ml at tal.de> wrote:

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

I think it's a rare use case.
We cannot solve all problems such as this even if pgpool-II locks with
nextval. For example, if the sequence is not generated by the SERIAL
data types, pgpool-II cannot lock because lock target table does not
exist.

-- 
Toshihiro Kitagawa
SRA OSS, Inc. Japan