Bug #807: Sequence currupted on recovery after kill -9 - Mailing list pgsql-bugs

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