Thread: Bug #807: Sequence currupted on recovery after kill -9
Matt Thompson (mthomp@tierfleet.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Sequence currupted on recovery after kill -9
Long Description
If you create a file with an implied sequence and do a couple of inserts, then do a kill -9, when you start up again,
thesequence will have a corrupted next_value field. In the example, the value of last_value is 4 before the kill -9
and34 when it comes back up.
Sample Code
[root@hawk temp]# cat list.txt
[postgres@buzzard tf]$ cat ins2.sh
psql testdb -c "create table test (id serial,name text);"
psql testdb -c "insert into test (name) values ('name1')"
psql testdb -c "insert into test (name) values ('name2')"
psql testdb -c "insert into test (name) values ('name3')"
psql testdb -c "insert into test (name) values ('name4')"
psql testdb -c "select * from test"
psql testdb -c "select * from test_id_seq"
cat data/postmaster.pid
[postgres@buzzard tf]$
[postgres@buzzard tf]$ ins2.sh
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_id_key' for table 'test'
CREATE
INSERT 41140 1
INSERT 41141 1
INSERT 41142 1
INSERT 41143 1
id | name
----+-------
1 | name1
2 | name2
3 | name3
4 | name4
(4 rows)
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled |
is_called
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
test_id_seq | 4 | 1 | 9223372036854775807 | 1 | 1 | 30 | f | t
(1 row)
19177
/home/tf/data
5432001 3342354
[postgres@buzzard tf]$ kill -9 19177
[postgres@buzzard tf]$ pg_ctl -l logfile start
pg_ctl: Another postmaster may be running. Trying to start postmaster anyway.
postmaster successfully started
[postgres@buzzard tf]$ psql testdb -c "select * from test_id_seq"
sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled |
is_called
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
test_id_seq | 34 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t
(1 row)
No file was uploaded with this report
pgsql-bugs@postgresql.org writes: > If you create a file with an implied sequence and do a couple of > inserts, then do a kill -9, when you start up again, the sequence > will have a corrupted next_value field. In the example, the value > of last_value is 4 before the kill -9 and 34 when it comes back up. What version of PostgreSQL is this? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
On Tuesday 29 October 2002 08:23, Neil Conway wrote: > pgsql-bugs@postgresql.org writes: > If you create a file with an implied sequence and do a couple of > inserts, then do a kill -9, when you start up again, the sequence > will have a corrupted next_value field. In the example, the value > of last_value is 4 before the kill -9 and 34 when it comes back up. I'm sure if you read the footers on Tom's email's it's pretty clearly state= d=20 'do not kill -9 the postmaster'.=20 So I'm not so sure that this is really a bug.=20 --=20 Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com
On Tue, 29 Oct 2002 pgsql-bugs@postgresql.org wrote: > Matt Thompson (mthomp@tierfleet.com) reports a bug with a severity of 2 > The lower the number the more severe it is. > > Short Description > Sequence currupted on recovery after kill -9 > > Long Description > If you create a file with an implied sequence and do a couple of inserts, then do a kill -9, when you start up again, thesequence will have a corrupted next_value field. In the example, the value of last_value is 4 before the kill -9 and34 when it comes back up. > The purpose of a sequence is not to insure that the numbers are consecutive, only to insure that they are unique. If you have failed inserts, you will see holes also. I don't know off the top of my head how to do consecutive numbers. Maybe only with a table lock. - robert
pgsql-bugs@postgresql.org writes:
> Sequence currupted on recovery after kill -9
> Long Description
> If you create a file with an implied sequence and do a couple of inserts, then do a kill -9, when you start up again,
thesequence will have a corrupted next_value field. In the example, the value of last_value is 4 before the kill -9
and34 when it comes back up.
This is not corruption, it is the intended behavior. Only if the
sequence were to go backwards would we consider it a bug.
(There were some sequence-can-go-backwards bugs awhile ago ... but
since you didn't say what version you are using, it's impossible
to guess whether you need an upgrade.)
regards, tom lane
Robert Hentosh wrote: > On Tue, 29 Oct 2002 pgsql-bugs@postgresql.org wrote: > > > Matt Thompson (mthomp@tierfleet.com) reports a bug with a severity of 2 > > The lower the number the more severe it is. > > > > Short Description > > Sequence currupted on recovery after kill -9 > > > > Long Description > > If you create a file with an implied sequence and do a couple of inserts, then do a kill -9, when you start up again,the sequence will have a corrupted next_value field. In the example, the value of last_value is 4 before the kill-9 and 34 when it comes back up. > > > > > The purpose of a sequence is not to insure that the numbers are > consecutive, only to insure that they are unique. If you have failed > inserts, you will see holes also. > > I don't know off the top of my head how to do consecutive numbers. Maybe > only with a table lock. Right. Sequences aren't consecutive anyway because an ABORT will not reuse the sequence value. There is an FAQ on that. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Darcy Buskermolen <darcy@wavefire.com> writes: > I'm sure if you read the footers on Tom's email's it's pretty clearly stated > 'do not kill -9 the postmaster'. > So I'm not so sure that this is really a bug. The operating environment is free to kill -9 the postmaster at any time, and PostgreSQL is expected to ensure database consistency nevertheless. After all, PostgreSQL is a database and not a cardbox. (But holes in sequence numbers certainly do not endanger database consistency.) -- Florian Weimer Weimer@CERT.Uni-Stuttgart.DE University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898