Any idea for serializing INSERTING SERIAL column?

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 andnew row on D1 will be (1, 101)

S2: INSERT INTO t1 VALUES(2); on D1 -- sequence "t1_j_seq" becomes 102 andnew row on D1 will be (2, 102)

S2: INSERT INTO t1 VALUES(2); on D2-- sequence "t1_j_seq" becomes 101 andnew row on D1 will be (2, 101)

S1: INSERT INTO t1 VALUES(1); on D2 -- sequence "t1_j_seq" becomes 102 andnew 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