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

[Tatsuo Ishii]

>> >> 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.
>> 
>> How do you get the current sequence value without
>> incrementing(i.e. nextval()) the sequence value?
> 
> SELECT last_value FROM the_sequence;
> 
> Not saying this is a good idea, though :)

Thanks for good point. Now I realize that we need lock anyway.

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 last_value FROM t1_i_seq;		-- suppose this returns 99
A: SELECT last_value FROM t1_i_seq;		-- suppose this returns 99 as well
B: SELECT setval('t1_i_seq', 101);		-- 101 > 99. So execute this
A: SELECT setval('t1_i_seq', 100);      -- 100 > 99. So execute this

You got sequence value 101 on the master whereas 100 on the slave. No
good.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp