[pgpool-hackers: 45] Re: Implementing SERIAL rewriting

[Huang Bambo]

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?


My idea is, at slave, before set the value, check if current value is
bigger than the given one.

Bambo Huang