Re: pg_ctl stop -m immediate on the primary server inflates sequences

Boszormenyi Zoltan írta:
> 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(at)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(at)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 | b
> 35 | 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(at)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 | e
> 103 | 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(at)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 | e
> 103 | f
> 136 | g
> (8 rows)
>
> But another restart and:
>
> zozo=# \q
> [zozo(at)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 | e
> 103 | f
> 136 | 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 | e
> 103 | f
> 136 | g
> 169 | 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
>

And to show that it doesn't happen with "-m fast", I tried it three times.
Before quitting from psql, I stopped the primary with "-m fast" each time:

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

zozo=# insert into t1 (t) values ('i');
INSERT 0 1
zozo=# \q
[zozo(at)db00 pgd90dev-xlog-slave]$ psql -p 65432 zozo
psql (9.0devel)
Type "help" for help.

zozo=# insert into t1 (t) values ('i');
INSERT 0 1
zozo=# \q
[zozo(at)db00 pgd90dev-xlog-slave]$ psql -p 65432 zozo
psql (9.0devel)
Type "help" for help.

zozo=# insert into t1 (t) values ('i');
INSERT 0 1
zozo=# select * from t1;
id | t
-----+---
1 | a
2 | b
35 | b
36 | c
69 | d
70 | e
103 | f
136 | g
169 | h
170 | i
171 | i
172 | i
(12 rows)

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/