[pgpool-general: 2149] Re: Postgres Sequence numbers different on backends when inserts run concurrently
Tatsuo Ishii
ishii at postgresql.org
Sat Sep 21 13:59:51 JST 2013
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
More information about the pgpool-general
mailing list