[Pgpool-hackers] Idea to handle sequences

[Tatsuo Ishii]

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?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp