[pgpool-general: 3991] Re: Postgres Sequence numbers different on backends when inserts run concurrently
Yugo Nagata
nagata at sraoss.co.jp
Thu Aug 20 10:43:11 JST 2015
I fixed it.
Patch is attached and this is already commited.
http://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=43ff7d3de9db4c8a1143258f4ffff98682dab560
On Tue, 28 Jul 2015 13:40:14 +0900
Yugo Nagata <nagata at sraoss.co.jp> wrote:
> 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>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
--
Yugo Nagata <nagata at sraoss.co.jp>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: insert_lock.patch
Type: text/x-diff
Size: 19937 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150820/0a2e4f87/attachment-0001.bin>
More information about the pgpool-general
mailing list