[pgpool-general: 2153] Re: Postgres Sequence numbers different on backends when inserts run concurrently
Steve Kuekes
steve.kuekes at physicianspharmacy.com
Tue Sep 24 03:37:35 JST 2013
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
More information about the pgpool-general
mailing list