[pgpool-general: 3894] Postgres Sequence numbers different on backends when inserts run concurrently
Steve Kuekes
steve.kuekes at physicianspharmacy.com
Sat Jul 25 04:41:59 JST 2015
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
More information about the pgpool-general
mailing list