Thread: After ~Crash Sequence not correct
Severity: Annoyance 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. (I *think* it was a different table & sequence last time). - Stuart
"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
"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
> 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 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