Horton
Menu

Thread

  1. SEQUENCE values (duplicated) in some corner cases when crash happens

    Vinícius Abrahão <vinnix.bsd@gmail.com> — 2020-04-29T15:56:11Z

    Hello PG Hackers

Hope you are well and safe!

I'm opening this thread to clarify something that I can observe: duplicated
values for sequences.

[My understanding is that duplication is not something we desire. In fact
it does NOT happen in the majority of cases, for example, when you
immediately insert the value and commit it. But it can eventually happen in
some specific scenarios...describe below]

The duplication can be observed when you only makes use of "nextval" (which
calls sequence.c / nextval_internal function) without - inserting it for
any reason - and the database crashes.
This is reproducible using the steps described on this link:

https://gist.github.com/vinnix/2fe148e3c42e11269bac5fcc5c78a8d1

There are two variants where this is reproducible (steps on the link above):

   - Autocommit + Suspending I/O (simulating a storage issue)
   - Explicitly opening transaction + Not Suspending I/O

** To simulate the "crash" I'm running: `killall -9 postgres`.

I've being debugging sequence.c code, I can see we only want to flush the
WAL segments once every 32 requests (per connection) - this is defined by
SEQ_LOG_VALS. Since, these values are "persisted" in the WAL file
containing the "advanced"/future values; not the current value being
retrieved by nextval_internal().

What I'm trying to understand now if this is a "bug" or a "new feature"...

Kind regards,
Vini

  2. Re: SEQUENCE values (duplicated) in some corner cases when crash happens

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2020-04-29T16:29:07Z

    On 2020-Apr-29, Vinicius Abrahao wrote:

> Hello PG Hackers
> 
> Hope you are well and safe!
> 
> I'm opening this thread to clarify something that I can observe: duplicated
> values for sequences.
> 
> [My understanding is that duplication is not something we desire. In fact
> it does NOT happen in the majority of cases, for example, when you
> immediately insert the value and commit it. But it can eventually happen in
> some specific scenarios...describe below]

Hi Vinicius

I'm not sure that a sequence that produces the same value twice, without
writing it to the database the first time, and with an intervening crash
in between, is necessarily a bug that we care to fix.  Especially so if
the fix will cause a large performance regression for the normal case
where the sequence value is written to the DB by a committed transaction.

(I think you could also cause the same problem with an async-commit
transaction that does write the value to the database, but whose writes
are lost in the crash ... since the WAL record for the sequence would
also be lost.)

Is there some valid reason to be interested in that scenario?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  3. Re: SEQUENCE values (duplicated) in some corner cases when crash happens

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-04-29T18:02:34Z

    Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2020-Apr-29, Vinicius Abrahao wrote:
>> I'm opening this thread to clarify something that I can observe: duplicated
>> values for sequences.

> I'm not sure that a sequence that produces the same value twice, without
> writing it to the database the first time, and with an intervening crash
> in between, is necessarily a bug that we care to fix.  Especially so if
> the fix will cause a large performance regression for the normal case
> where the sequence value is written to the DB by a committed transaction.

I believe this behavior is 100% intentional: the advance of the sequence
value is logged to WAL, but we don't guarantee to make the WAL entry
persistent until the calling transaction commits.  And while I'm too
lazy to check right now, I think the calling transaction might've had
to cause some additional non-sequence-object updates to happen on disk,
too, else we won't think it has done anything that needs committing.

As you say, doing something different would entail a large performance
penalty for a rather dubious semantic requirement.  The normal expectation
is that we have to protect sequence values that get written into tables
someplace.

			regards, tom lane

  4. Re: SEQUENCE values (duplicated) in some corner cases when crash happens

    Jeremy Schneider <schnjere@amazon.com> — 2020-05-06T17:51:59Z

    On 4/29/20 11:02, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> On 2020-Apr-29, Vinicius Abrahao wrote:
>>> I'm opening this thread to clarify something that I can observe: duplicated
>>> values for sequences.
> 
>> I'm not sure that a sequence that produces the same value twice, without
>> writing it to the database the first time, and with an intervening crash
>> in between, is necessarily a bug that we care to fix.  Especially so if
>> the fix will cause a large performance regression for the normal case
>> where the sequence value is written to the DB by a committed transaction.
> 
> I believe this behavior is 100% intentional: the advance of the sequence
> value is logged to WAL, but we don't guarantee to make the WAL entry
> persistent until the calling transaction commits.  And while I'm too
> lazy to check right now, I think the calling transaction might've had
> to cause some additional non-sequence-object updates to happen on disk,
> too, else we won't think it has done anything that needs committing.
The behavior we're observing is that a nextval() call in a committed
transaction is not crash-safe. This was discovered because some
applications were using nextval() to get a guaranteed unique sequence
number [or so they thought], then the application did some processing
with the value and later stored it in a relation of the same database.

The nextval() number was not used until the transaction was committed -
but then the fact of a value being generated, returned and committed was
lost on crash. The nextval() call used in isolation did not seem to
provide durability.


> As you say, doing something different would entail a large performance
> penalty for a rather dubious semantic requirement.  The normal expectation
> is that we have to protect sequence values that get written into tables
> someplace.

Whether or not it's dubious is in the eye of the beholder I guess; in
Oracle I believe the equivalent use of sequences provides the usual
durability guarantees. Probably that's why this particular user was
surprised at the behavior. If PostgreSQL isn't going to provide
durability for isolated use of sequences, then IMO that's fine but the
fact should at least be in the documentation.

-Jeremy


-- 
Jeremy Schneider
Database Engineer
Amazon Web Services

  5. Re: SEQUENCE values (duplicated) in some corner cases when crash happens

    Jeff Janes <jeff.janes@gmail.com> — 2020-05-14T21:58:35Z

    On Wed, May 6, 2020 at 1:52 PM Jeremy Schneider <schnjere@amazon.com> wrote:


> The behavior we're observing is that a nextval() call in a committed
>
transaction is not crash-safe. This was discovered because some
> applications were using nextval() to get a guaranteed unique sequence
> number [or so they thought], then the application did some processing
> with the value and later stored it in a relation of the same database.
>
> The nextval() number was not used until the transaction was committed -
>

I don't know what this line means.  You said it was stored in a relation,
surely that needs to have happened through some command which preceded the
commit chronologically, though formally they may have happened atomically.


> but then the fact of a value being generated, returned and committed was
> lost on crash. The nextval() call used in isolation did not seem to
> provide durability.
>

Are you clarifying the original complaint, or this a new, different
complaint? Vini's test cases don't include any insertions.  Do you have
test cases that can reproduce your complaint?

Cheers,

Jeff

  6. Re: SEQUENCE values (duplicated) in some corner cases when crash happens

    Jeremy Schneider <schnjere@amazon.com> — 2020-05-14T22:09:28Z

    On 5/14/20 14:58, jeff.janes@gmail.com wrote:
>
> *CAUTION*: This email originated from outside of the organization. Do
> not click links or open attachments unless you can confirm the sender
> and know the content is safe.
>
>
> On Wed, May 6, 2020 at 1:52 PM Jeremy Schneider <schnjere@amazon.com
> <mailto:schnjere@amazon.com>> wrote:
>  
>
>     The behavior we're observing is that a nextval() call in a committed
>
>     transaction is not crash-safe. This was discovered because some
>     applications were using nextval() to get a guaranteed unique sequence
>     number [or so they thought], then the application did some processing
>     with the value and later stored it in a relation of the same database.
>
>     The nextval() number was not used until the transaction was
>     committed -
>
>
> I don't know what this line means.  You said it was stored in a
> relation, surely that needs to have happened through some command
> which preceded the commit chronologically, though formally they may
> have happened atomically.

"Later stored it in the table" - I'd have to double check with the other
team, but IIUC it was application pseudo-code like this:

  * execute SQL "select nextval()" and store result in
    my_local_variable_unique_id
  * commit
  * do some processing, tracing, logging, etc identified with
    my_local_variable_unique_id
  * execute SQL "insert into mytable values(my_local_variable_unique_id,
    data1, data2)"
  * commit

They weren't expecting that they could get duplicates from a sequence,
which leads to unique violations and other problems later.  Maybe a
workaround is doing some kind of dummy insert or update or something in
the transaction that gets a sequence value.


>  
>
>     but then the fact of a value being generated, returned and
>     committed was
>     lost on crash. The nextval() call used in isolation did not seem to
>     provide durability.
>
>
> Are you clarifying the original complaint, or this a new, different
> complaint? Vini's test cases don't include any insertions.  Do you
> have test cases that can reproduce your complaint?

Clarification of same issue, not a new issue.

Tom also has said as much in his email - he said it's quite plausible
that sequences used in isolation aren't crash safe.  I just think we
should document it; I'll work on a proposal/doc-update-patch for
everyone to bikeshed on when I have a few minutes  :)

-Jeremy


-- 
Jeremy Schneider
Database Engineer
Amazon Web Services

  7. Re: SEQUENCE values (duplicated) in some corner cases when crash happens

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2020-05-14T22:47:29Z

    On 2020-May-14, Jeremy Schneider wrote:

> "Later stored it in the table" - I'd have to double check with the other
> team, but IIUC it was application pseudo-code like this:
> 
>   * execute SQL "select nextval()" and store result in
>     my_local_variable_unique_id
>   * commit

Yes, simply inserting the sequence value in a (logged!) dummy table
before this commit, as you suggest, should fix this problem.  The insert
ensures that the transaction commit is flushed to WAL.  The table need
not have indexes, making the insert faster.  Just make sure to truncate
the table every now and then.

+1 to documenting this.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services