Thread: 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
On Tue, May 31, 2011 at 8:08 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: [snip] > 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. Sorry, I'm not real familiar with pgpool, but have you thought about using an advisory lock on the target table, instead of a "real" lock (SELECT ... FOR UPDATE / LOCK table)? An advisory lock should not interfere with autovacuum. Obviously, this would only work if all the INSERTs in your example were coming from a single application (i.e. pgpool) which would honor the advisory lock. Josh
> On Tue, May 31, 2011 at 8:08 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: > [snip] >> 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. > > Sorry, I'm not real familiar with pgpool, but have you thought about > using an advisory lock on the target table, instead of a "real" lock > (SELECT ... FOR UPDATE / LOCK table)? An advisory lock should not > interfere with autovacuum. Obviously, this would only work if all the > INSERTs in your example were coming from a single application (i.e. > pgpool) which would honor the advisory lock. Problem with the advisory lock is, it will not work if the target table is empty. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Tatsuo Ishii <ishii@postgresql.org> writes:
>> Sorry, I'm not real familiar with pgpool, but have you thought about
>> using an advisory lock on the target table, instead of a "real" lock
>> (SELECT ... FOR UPDATE / LOCK table)?
> Problem with the advisory lock is, it will not work if the target
> table is empty.
(a) why not? You could surely use the table OID as a key for the advisory lock.
(b) advisory lock on the sequence might be better anyway.
regards, tom lane
>> Sorry, I'm not real familiar with pgpool, but have you thought about >> using an advisory lock on the target table, instead of a "real" lock >> (SELECT ... FOR UPDATE / LOCK table)? An advisory lock should not >> interfere with autovacuum. Obviously, this would only work if all the >> INSERTs in your example were coming from a single application (i.e. >> pgpool) which would honor the advisory lock. > > Problem with the advisory lock is, it will not work if the target > table is empty. Oops. I was wrong. the key for advisory lock needs to be a unique value, but not necessarily a row value in a table. Seems this is the way I should go(though need to be carefull since the lock is not released even after a transaction ends). Thanks! -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Wed, Jun 1, 2011 at 1:30 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: >> Problem with the advisory lock is, it will not work if the target >> table is empty. > > Oops. I was wrong. the key for advisory lock needs to be a unique > value, but not necessarily a row value in a table. Seems this is the > way I should go(though need to be carefull since the lock is not > released even after a transaction ends). Thanks! 9.1 has a transactional advisory lock (fyi). I'd still consider keeping to the old method because presumably you want the lock to be as short as possible. Another big headache with advisory locks unfortunately is lockspace conflicts between user code and library (you) code -- I've actually seen this in the wild once. Maybe you could use the double int4 version and allow the users to supply the other optional int4? merlin