Re: SIGQUIT and lost sequence WAL records

On Wed, Feb 17, 2016 at 11:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
>> Lately a couple of colleagues have pinged me regarding the fact that a
>> server signaled with SIGQUIT, or "pg_ctl stop -m immediate", may
>> ignore sequence WAL records.
>
> I've not gone through this in detail, but I think what is happening
> is that since the test case never commits any transactions, the
> sequence-advance WAL records may not get flushed to disk, and thus
> of course are not available for replay. This is not a bug IMO, as
> by the exact same token, no uses of those nextval() values can appear
> in any committed tuples, so no database inconsistency is possible.
>
> If an application is using nextval() results *externally to the
> database*, it's unsafe for it to rely on those values being unique
> unless/until it commits the nextval() calls.

That's for the confirmation. Yeah I already suggested those folks to
issue a commit to ensure that nextval() remains consistent.

> I seem to recall some past discussions about whether a transaction
> that commits after writing only a sequence advance (that is, it
> did nextval() and nothing else) needs to flush WAL. I think at one
> point it did not but we changed it because of this consideration.
>
> [ digs around... ] That was a long time ago: see 01747692f.

That's a part of history...

The documentation is stating the following regarding this behavior:
<important>
<para>
To avoid blocking concurrent transactions that obtain numbers from the
same sequence, a <function>nextval</function> operation is never
rolled back; that is, once a value has been fetched it is considered
used, even if the transaction that did the
<function>nextval</function> later aborts. This means that aborted
transactions might leave unused <quote>holes</quote> in the sequence
of assigned values.
</para>
</important>
Do you think we should add a mention regarding SIGQUIT/stop-immediate
regarding the fact that the application needs to commit the
transaction that used nextval() with synchronous_commit = on to keep
it consistent at recovery?
--
Michael