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, 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.
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 stated
'do not kill -9 the postmaster'.
So I'm not so sure that this is really a bug.
--
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 -9Long 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.
- 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, 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.
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 -9Long 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