[pgpool-general: 2144] Postgres Sequence numbers different on backends when inserts run concurrently

[Steve Kuekes]

I have a PGPool that is connected to 2 backends in replication and load 
balance mode one master, one slave.

We have been using this for a while with no problems.  Occasionally we 
get problems where serial sequence numbers for the same database record 
are different on each of the backends.

We have been able to replicate the problem by writing a java program to 
do 10,000 inserts and running it concurrently on 4 systems connected to 
pgpool.  We have enabled pgpool insert lock and created the insert lock 
table to try to keep this from happening.

Here is a short java program that re-creates the problem reliably when 
running 4 instances concurrently.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PgTester {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		 Integer startValue = 0;
	        try{
	            startValue = Integer.parseInt(args[0]);
	        } catch (Exception e){
	            System.out.println("Please provide a valid number as the 
first argument to the program");
	            System.exit(-1);
	        }

		
		Connection conn=null;
		try {
			Class.forName ("org.postgresql.Driver");

			conn = 
DriverManager.getConnection("jdbc:postgresql://hostname/pharman?user=userid&password=password");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {

             PreparedStatement query = conn.prepareStatement("INSERT 
INTO public.sequencetester (recordno) VALUES (?)");
             int max = startValue + 10000;
             for(int cv = startValue; cv <= max; cv++){
                 query.setInt(1, cv);
                 query.execute();
                 //System.out.println("interted "+cv);
             }
             query.close();
             conn.close();
             System.out.println("Done");
         } catch (SQLException e) {
             e.printStackTrace();
         } 	
}

The table is created using

CREATE TABLE sequencetester
(
   id serial NOT NULL,
   recordno integer,
   CONSTRAINT sq_pk PRIMARY KEY (id )
)

After running the program if you compare the id and recordno column on 
each of the backend postgres systems there are entries that do not have 
the same values.

for example

Backend 1
id = 5, recordno = 5
id = 6, recordno = 6
id = 7, recordno = 6000
id = 8, recordno = 6001

Backend 2
id = 5, recordno = 5
id = 6, recordno = 6000
id = 7, recordno = 6
id = 8, recordno = 6001

It appears that the serial sequence was run in a different order on each 
of the back ends.

We are running Postgres 9.3 from the Debian apt repository and PgPool 
3.3.1 downloaded from the pgpool.net web site.

This problem also occurs on our current production system running 
Postgres 9.2 PgPool 3.2.3.

Is this a configuration problem or an internal PgPool/Postgres problem?

Steve Kuekes
-- 
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