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

[Steve Kuekes]

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