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

Steve Kuekes steve.kuekes at physicianspharmacy.com
Sat Jul 25 04:41:59 JST 2015


We originally reported this PgPool 3.2.3, but we have duplicated it 
again under pgpool 3.4.2 and it still happens occaisionally under our 
production system running PgPool 3.3.2


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 the text from your previous fix for this:

Ok, I think I found the cause of the problem.

Pgpool automatically issues a LOCK command(table lock or row lock
depending on your configuration) to keep consistency while handling
INSERT command. This works fine with your test program until 4
iterated INSERT. In each INSERT cycle, pgpool issues LOCK while
processing "parse" message (which is generated by JDBC driver while
handling "prepare") which is followed by "bind" "describe" and
"execute" message. This is good. However, starting from 5th INSERT,
JDBC skips generating the parse message and starts from
"bind". Unfortunately pgpool does not issue LOCK while processing the
bind message. Note that the behavior of JDBC is legal. Just pgpool is
not prepared for the situation. Fixing it is not a trivial work. Give
me some time...
--
Tatsuo Ishii


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.0 from the Debian apt repository and PgPool 
3.4.2 downloaded from the pgpool.net web site.

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


More information about the pgpool-general mailing list