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

[Steve Kuekes]

Tatsuo,

Thanks for looking at this problem.

We ran 3 different tests turning autocommit off with 4 processes doing 
10,000 inserts simultaneously.

Test 1:

Turn autocommit off
Loop 10,000 times doing insert followed by commit

This test had numerous sequence differences between the back ends.

Test 2:
Turn autocommit off
Loop doing 10,000 inserts issuing commit every 1,000 inserts.

This test had numerous sequence differences between the back ends.

Test 3:
Turn autocommit off
Loop doing insert 10,000 inserts issuing commit one time at the end.

This test did not show any differences between the back ends.  We ran 
this test 5 or six times and did not have any differences.  There still 
may be a collision of commits and this run just didn't have enough 
commits to have some collide.

I hope this helps identify the problem.

Let me know if you need any more information.

Steve

On 09/21/2013 12:59 AM, Tatsuo Ishii wrote:
> I confirm the problem using your test program. Will look into this. In
> the mean time, I noticed that if your program runs with autocommit
> off, it seems the problem goes away. Can you please try it out if you
> like?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>> 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
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general

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