Hi, Pgpool currently acquires row locks on sequences to sync sequences among PostgreSQL servers in "replication mode".
Suppose you have a table t1(i int, j SERIAL) and two sessions are trying to INSERT the table(initial value of t1_j_seq is 100 on PostgreSQL server D1 and D2): S1: INSERT INTO t1 VALUES(1); on D1 -- sequence "t1_j_seq" becomes 101 and new row on D1 will be (1, 101) S2: INSERT INTO t1 VALUES(2); on D1 -- sequence "t1_j_seq" becomes 102 and new row on D1 will be (2, 102) S2: INSERT INTO t1 VALUES(2); on D2-- sequence "t1_j_seq" becomes 101 and new row on D1 will be (2, 101) S1: INSERT INTO t1 VALUES(1); on D2 -- sequence "t1_j_seq" becomes 102 and new row on D1 will be (1, 102) So you have these rows which are incorrectly replicated: D1: (1, 101) (2, 102) D2: (2, 101) (1, 102) This can be fixed by serializing INSERTs into t1. One idea is acquiring lock on t1. Unfortunately this conflicts with autovacuum. So pgpool-II does following: SELECT 1 FROM LOCK t1_j_seq FOR UPDATE; (LOCK t1_j_seq will not work) INSERT INTO t1... Problem is, "SELECT 1 FROM LOCK t1_j_seq FOR UPDATE" will fail after XID wraparound happens. In summary, 1) "LOCK table foo" cannot be used because of conflict with autovacuum 2) "LOCK sequence" just doesn't work 3) "SELECT 1 FROM LOCK sequece" fails after XID wraparound If you have other idea to serialize concurrent INSERT to a table, I would like to hear from you. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers