Re: SIGQUIT and lost sequence WAL records

On Wed, Feb 17, 2016 at 11:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Michael Paquier <michael.paquier@gmail.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