After ~Crash Sequence not correct

"Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk> writes:

Setup: PostgreSQL 7.1.2 on Dual PIII WinNT4 server + Cygwin 1.3.2
PostgreSQL is run as a service and when rebooting (which doesn't shutdown -
previously on cygwin list (hence the ~Crash in title)) I have twice noticed
that a sequence was not properly updated, however records with Primary keys
based upon the sequence where present in the table.

Hmm. You're about the third person to report something like this, so
there's clearly something not right. I'm guessing a bug in the WAL
logging or redo code for sequences ...

regards, tom lane

I have verified that killing the postmaster after a few nextval's
leaves things in a bad state after restart.

I think I see the problem: in nextval(), the sequence data written to
the WAL log is different from that written to the data page. Isn't
that bogus?

To WAL:
seq->last_value = next;
seq->is_called = true;
seq->log_cnt = 0;

To disk:
seq->last_value = last; /* last fetched number */
seq->is_called = true;
seq->log_cnt = log; /* how much is logged */

(also, "log" has been changed between these two points)

regards, tom lane

I have verified that killing the postmaster after a few nextval's
leaves things in a bad state after restart.

I think I see the problem: in nextval(), the sequence data written to
the WAL log is different from that written to the data page. Isn't
that bogus?

It was made to avoid WAL-loging on each nextval call, ie it should work
like OID pre-fetching: value stored in WAL must always "exceed" values
returned by nextval so on the after-crash-restart sequence should be
advanced to value which was never returned by nextval (for non-cycled
sequences). Maybe I made some mistakes in implementation?

Vadim

"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:

It was made to avoid WAL-loging on each nextval call, ie it should work
like OID pre-fetching: value stored in WAL must always "exceed" values
returned by nextval so on the after-crash-restart sequence should be
advanced to value which was never returned by nextval (for non-cycled
sequences). Maybe I made some mistakes in implementation?

Oh, okay. What I saw was that the next nextval() after restart was
higher than what I was expecting; but that's correct given the prefetch
behavior.

But we've seen several reports wherein the value appeared to go
backwards after a crash.

Stuart, you weren't running with -F (fsync off) by any chance, were you?

regards, tom lane

No. I had fsync on.
- Stuart

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 19 December 2001 18:49
To: Mikheev, Vadim
Cc: Henshall, Stuart - WCP; pgsql-bugs@postgreSQL.org
Subject: Re: [BUGS] After ~Crash Sequence not correct

"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:

It was made to avoid WAL-loging on each nextval call, ie it should work
like OID pre-fetching: value stored in WAL must always "exceed" values
returned by nextval so on the after-crash-restart sequence should be
advanced to value which was never returned by nextval (for non-cycled
sequences). Maybe I made some mistakes in implementation?

Oh, okay. What I saw was that the next nextval() after restart was
higher than what I was expecting; but that's correct given the prefetch
behavior.

But we've seen several reports wherein the value appeared to go
backwards after a crash.

Stuart, you weren't running with -F (fsync off) by any chance, were you?

regards, tom lane

Mikheev, Vadim wrote:

I have verified that killing the postmaster after a few nextval's
leaves things in a bad state after restart.

I think I see the problem: in nextval(), the sequence data written to
the WAL log is different from that written to the data page. Isn't
that bogus?

It was made to avoid WAL-loging on each nextval call, ie it should work
like OID pre-fetching: value stored in WAL must always "exceed" values
returned by nextval so on the after-crash-restart sequence should be
advanced to value which was never returned by nextval (for non-cycled
sequences). Maybe I made some mistakes in implementation?

Was this ever fixed?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026