[pgpool-general: 3896] Re: Postgres Sequence numbers different on backends when inserts run concurrently
Yugo Nagata
nagata at sraoss.co.jp
Tue Jul 28 13:40:14 JST 2015
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>
More information about the pgpool-general
mailing list