[Pgpool-hackers] Idea to handle sequences

[Tatsuo Ishii]

From: Guillaume Lelarge <guillaume at lelarge.info>
Subject: Re: [Pgpool-hackers] Idea to handle sequences
Date: Fri, 07 Jan 2011 18:54:48 +0100
Message-ID: <4D275368.4090601 at lelarge.info>

> Le 07/01/2011 11:46, Tatsuo Ishii a écrit :
>> Hi,
>> 
>> I have an idea for better handling of sequesence and SERIAL data type.
>> In replication mode, currently the only way to keep sequences be in
>> sync among DB nodes is auqire a lock on the target table or sequence
>> table to serialize accesses to the sequence. Downside of this is, the
>> method is very fragile: once the sequence values starts to differ by
>> some reasons, it will keep out of sync until doing an online recovery.
>> 
>> Here is another idea. Suppose we have a table:
>> 
>> CREATE TABLE t1(i SERIAL, j SERIAL, k TEXT);
>> 
>> We rewrite this:
>> 
>> INSERT INTO t1 VALUES(k) 'foo';
>> 
>> into following sequence of SQLs.
>> 
>> On master we do:
>> 
>> BEGIN;
>> SELECT 1 FROM t1_i_seq FOR UPDATE;
>> SELECT nextval('t1_i_seq');
>> SELECT nextval('t1_j_seq');
>> INSERT INTO t1 VALUES(i, j, k) ....
>> END;
>> 
>> On slaves:
>> 
>> BEGIN;
>> SELECT setval('t1_i_seq', value obatined on the master node);
>> SELECT setval('t1_j_seq', value obatined on the master node);
>> INSERT INTO t1 VALUES(i, j, k) ....
>> END;
>> 
>> This way, it is guaranteered the sync of sequences because of setval
>> calls.
>> 
>> Comments?
> 
> Seems interesting but the thing I don't understand is why yo do twice
> the nextval and the setval?

Just because the sample table 't1' has two SERIAL columns, namely 'i'
and 'j'.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp