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

[Yugo Nagata]

Hi,

I confirmed this. I ran your program on 4 terminals
and checked the table contents of each backend, then
I found inconsistencies.

# id  recordno
 ...
 11957	289
-11958	642
-11959	235
+11958	235
+11959	642
 11960	852
 11961	290
-11962	236
-11963	643
+11962	643
+11963	236
 11964	853
 11965	291
 ...

I'll look into this and fix it.

On Fri, 24 Jul 2015 15:41:59 -0400
Steve Kuekes <steve.kuekes at physicianspharmacy.com> wrote:

> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


-- 
Yugo Nagata <nagata at sraoss.co.jp>