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@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 | 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/

--On 10. April 2010 09:26:41 +0200 Boszormenyi Zoltan <zb@cybertec.at>
wrote:

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:

AFAIKS sequences are pre-logged with 32 values to WAL to avoid overhead. I
suspect this is why you are seeing those gaps.

--
Thanks

Bernd

Bernd Helmle �rta:

--On 10. April 2010 09:26:41 +0200 Boszormenyi Zoltan <zb@cybertec.at>
wrote:

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:

AFAIKS sequences are pre-logged with 32 values to WAL to avoid
overhead. I suspect this is why you are seeing those gaps.

Then it should happen all the time, even with "-m fast" or "-m smart", no?

It seemed like my sequences have a CACHE 32 setting, which would
apply to every client that connects, runs nextval() once and disconnects.

But it didn't happen all the time, so it's not deterministic.

--
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/

On Sat, Apr 10, 2010 at 02:36:41PM +0200, Boszormenyi Zoltan wrote:

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:

AFAIKS sequences are pre-logged with 32 values to WAL to avoid
overhead. I suspect this is why you are seeing those gaps.

Then it should happen all the time, even with "-m fast" or "-m smart", no?

Nope, because on a normal shutdown it writes out the actual value. When
you say "immediate" you mean "right now, don't bother with anything not
important", like for example gaps in sequences. You're essentially
crashing the DB.

Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout �rta:

On Sat, Apr 10, 2010 at 02:36:41PM +0200, Boszormenyi Zoltan wrote:

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:

AFAIKS sequences are pre-logged with 32 values to WAL to avoid
overhead. I suspect this is why you are seeing those gaps.

Then it should happen all the time, even with "-m fast" or "-m smart", no?

Nope, because on a normal shutdown it writes out the actual value. When
you say "immediate" you mean "right now, don't bother with anything not
important", like for example gaps in sequences. You're essentially
crashing the DB.

Have a ncie day,

OK, thanks for the info.

--
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/