[pgpool-general: 1670] Re: Pgpool insert_lock not maintaining serial sequences between multiple back ends

[Tatsuo Ishii]

I have tested replication mode with serial data type. pgpool-II
version is 3.2.3 and PostgreSQL is 9.2. In summary, I couldn't
reproduce your problem. Here is how I tested:

1) set up replication mode environment using pgpool_setup.
   pgpool_setup -m r

   This creates two node replication environment.

2) create pgbench script file.
   $ cat test.sql
   \setrandom j 1 100000
   INSERT INTO t1 VALUES(default, :j);

3) create test table.
   CREATE TABLE t1(i SERIAL, J INT);

4) run pgbench.

   pgbench -p 11002 -n -c 10 -t 100000 -f test.sql test

   This executes insert by 10 clients simultaneously.

5) examin result.

   psql -p 11000 -c "copy (select * from t1 order by i) to '/tmp/s1'" test
   psql -p 11001 -c "copy (select * from t1 order by i) to '/tmp/s2'" test
   cmp /tmp/s1 /tmp/s2

   cmp did not show any difference, out of 100,000 rows.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> We have a test Pgpool system running Pgpool 3.2.3 and Postgres 9.1.
> 
> The configuration is one master postgres and one slave postgres in
> replication mode with load balancing and a separate system running
> pgpool.
> 
> When there are multiple systems that are inserting rows into tables
> that contain a "serial" datatype the values of the serials sometimes
> get different values on the different back end systems.  We have
> enabled insert_lock in the config file and created the insert_lock
> table. According to a debugging trace it appears that inserts to these
> tables are using the insert_lock table to lock a row by the oid of the
> destination table.
> 
> This can be replicated by having 5 instances of clients (Using the
> jdbc postgres driver) to perform 10,000 inserts each to the same table
> at the same time.  We then compare the data on both the master and
> slave system and the serial id's are different for almost 1/2 of the
> 50,000 rows.
> 
> If we do not use the insert_lock table and rely on complete table
> locking we still get a few hundred serial ids that are wrong out of
> the 50,000.
> 
> It seems that the insert_lock option in the configuration file has a
> problem whether we use the insert_lock table or not.
> 
> Thanks for any help on this.
> -- 
> Steve Kuekes
> 
> Physicians Pharmacy Alliance
> 118 MacKenan Drive, Suite 200
> Cary, NC  27511
> 919-465-5801 direct
> 919-463-5555 main
> 919-463-5566 fax
> 
> steve.kuekes at physicianspharmacy.com
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general