Horton
Menu

Thread

  1. Bug #807: Sequence currupted on recovery after kill -9

    PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2002-10-29T15:36:20Z

    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

  2. Re: Bug #807: Sequence currupted on recovery after kill -9

    Neil Conway <neilc@samurai.com> — 2002-10-29T16:23:50Z

    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

  3. Re: Bug #807: Sequence currupted on recovery after kill -9

    Darcy Buskermolen <darcy@wavefire.com> — 2002-10-29T16:38:55Z

    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

  4. Re: Bug #807: Sequence currupted on recovery after kill -9

    Robert Hentosh <hentosh@io.com> — 2002-10-29T17:18:23Z

    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.

- robert

  5. Re: Bug #807: Sequence currupted on recovery after kill -9

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-29T17:49:34Z

    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

  6. Re: Bug #807: Sequence currupted on recovery after kill -9

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-29T18:54:22Z

    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

  7. Re: Bug #807: Sequence currupted on recovery after kill -9

    Florian Weimer <weimer@cert.uni-stuttgart.de> — 2002-10-31T20:25:59Z

    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