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

[Steve Kuekes]

Do you have any update on this problem.  We are getting closer to a 
PGPool upgrade here and I'd like for this to be in our new production setup.

By the way, PGPool is fantastic. We run millions of transactions/day 
through our databases using it.

Thanks for your work

Steve

On 09/24/2013 02:36 AM, Tatsuo Ishii wrote:
> 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
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>> 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
>> _______________________________________________
>> 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