[pgpool-hackers: 44] Implementing SERIAL rewriting

[Tatsuo Ishii]

Hi,

I have been thinking about implementing SERIAL rewriting in pgpool's
native replication mode. Currently we deal with SERIAL by using
insert_lock. This has some shortcomings however:

1) Using locks degrades performance

2) Once a sequence is not in sync for some reason, there is no way to
   force re-sync except using online recovery.

So here is the idea...

Suppose we have a table:

CREATE TABLE t1(i SERIAL, j int);

If an INSERT is issued:

INSERT INTO t1 VALUES(default, 1);

then we rewrite it to following forms:

on the master:
SELECT nextval('t1_i_seq');		-- suppose this returns 100
INSERT INTO t1 VALUES(100, 1);

on the slaves:
SELECT setval(('t1_i_seq', 100);
INSERT INTO t1 VALUES(100, 1);

This way, no table lock is necessary and sequences are forced to sync
among servers.

One problem I could forsee is:

Suppose we have two different session A and B to pgpool.  It is
possible that following order of SQL execution could happen:

A: SELECT nextval('t1_i_seq');		-- suppose this returns 100
B: SELECT nextval('t1_i_seq');		-- suppose this returns 101
A: INSERT INTO t1 VALUES(100, 1);	-- on the master and slaves
B: INSERT INTO t1 VALUES(101, 1);	-- on the master and slaves
B: SELECT setval('t1_i_seq', 101);  -- on the slaves
A: SELECT setval('t1_i_seq', 100);	-- on the slaves

In this scenario, the sequence' value results in 101 on the master, on
the other hand 100 on the slaves. Any idea how to prevent this?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp