pg_ctl stop -m immediate on the primary server inflates sequences - Mailing list pgsql-hackers

Hi,

I wanted to test HS/SR and have setup two instances, one primary
and one secondary, the secondary is obviously a copy of the primary
while pg_start_backup() was in effect.

I started up the secondary server after "SELECT pg_stop_backup()" on
the primary. I stopped and started the primary with "-m fast" and
"-m immediate" and I noticed that the sequence that was created for
my serial field was inflated if I used "-m immediate".

Here's the scenario:

- primary and secondary are running, then:

zozo=# create table t1 (id serial primary key, t text);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for
serial column "t1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey"
for table "t1"
CREATE TABLE
zozo=# insert into t1 (t) values ('a');
INSERT 0 1
zozo=#

- stop the primary with "-m fast" (the connection was still alive to it) and start it again, then:

zozo=# \q
[zozo@db00 pgd90dev-xlog-slave]$ psql -p 65432 zozo
psql (9.0devel)
Type "help" for help.

zozo=# select * from t1;id | t
----+--- 1 | a
(1 row)

zozo=# insert into t1 (t) values ('b');
INSERT 0 1
zozo=# select * from t1;id | t
----+--- 1 | a 2 | b
(2 rows)

- stop the primary with "-m immediate" (connection was alive on it) and start it again, then:

zozo=# \q
[zozo@db00 pgd90dev-xlog-slave]$ psql -p 65432 zozo
psql (9.0devel)
Type "help" for help.

zozo=# select * from t1;id | t
----+--- 1 | a 2 | b
(2 rows)

zozo=# insert into t1 (t) values ('b');
INSERT 0 1
zozo=# select * from t1;id | t
----+--- 1 | a 2 | b35 | b
(3 rows)

The above is quite reproducable, "pg_ctl stop -m immediate"
"usually" inflated my serial sequence, but I had two occasions
when not. The 69 -> 70 was one. The inflated increase is always 33:

[zozo@db00 pgd90dev-xlog-slave]$ psql -p 65432 zozo
psql (9.0devel)
Type "help" for help.

zozo=# insert into t1 (t) values ('f');
INSERT 0 1
zozo=# select * from t1;id  | t
-----+---  1 | a  2 | b 35 | b 36 | c 69 | d 70 | e103 | f
(7 rows)

Let's try with a plain sequence:

zozo=# create sequence s1;
CREATE SEQUENCE
zozo=# select nextval('s1');nextval
---------      1
(1 row)

I stopped the primary at this point with "-m immediate",
and from this first result I thought that a plain sequence is
not bothered by this:

zozo=# \q
[zozo@db00 pgd90dev-xlog-slave]$ psql -p 65432 zozo
psql (9.0devel)
Type "help" for help.

zozo=# select nextval('s1');nextval
---------      2
(1 row)

zozo=# insert into t1 (t) values ('g');
INSERT 0 1
zozo=# select * from t1;id  | t
-----+---  1 | a  2 | b 35 | b 36 | c 69 | d 70 | e103 | f136 | g
(8 rows)

But another restart and:

zozo=# \q
[zozo@db00 pgd90dev-xlog-slave]$ psql -p 65432 zozo
psql (9.0devel)
Type "help" for help.

zozo=# select nextval('s1');nextval
---------     35
(1 row)

zozo=# select * from t1;id  | t
-----+---  1 | a  2 | b 35 | b 36 | c 69 | d 70 | e103 | f136 | g
(8 rows)

zozo=# insert into t1 (t) values ('h');
INSERT 0 1
zozo=# select * from t1;id  | t
-----+---  1 | a  2 | b 35 | b 36 | c 69 | d 70 | e103 | f136 | g169 | h
(9 rows)

It happened with a CVS version of about 2 weeks ago and the
yesterday's version, as well. I think it's not intentional, it must be
a race somewhere, as it doesn't happen all the time.

Best regards,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/