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

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@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 | 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@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@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@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@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@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@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 | e103 | f136 | g169 | h170 | i171 | i172 | 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/