Skip site navigation (1) Skip section navigation (2)

pg_ctl stop -m immediate on the primary server inflates sequences

From: Boszormenyi Zoltan <zb(at)cybertec(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Hans-Juergen Schoenig <hs(at)cybertec(dot)at>
Subject: pg_ctl stop -m immediate on the primary server inflates sequences
Date: 2010-04-10 07:26:41
Message-ID: 4BC02831.7010505@cybertec.at (view raw or flat)
Thread:
Lists: 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(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

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


Responses

pgsql-hackers by date

Next:From: Boszormenyi ZoltanDate: 2010-04-10 07:31:04
Subject: Re: pg_ctl stop -m immediate on the primary server inflates sequences
Previous:From: Martijn van OosterhoutDate: 2010-04-10 07:20:16
Subject: Re: Set LC_COLLATE to de_DE_phoneb

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group