[Pgpool-hackers] Idea to handle sequences

[Guillaume Lelarge]

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?

-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com