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
[z...@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
[z...@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:
[z...@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
[z...@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
[z...@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/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
