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