From: | Jeremy Schneider <schnjere(at)amazon(dot)com> |
---|---|
To: | <jeff(dot)janes(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Vinicius Abrahao <vinnix(dot)bsd(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Vinícius Schmidt <vinics(at)amazon(dot)com> |
Subject: | Re: SEQUENCE values (duplicated) in some corner cases when crash happens |
Date: | 2020-05-14 22:09:28 |
Message-ID: | b2b58767-5a3c-8c03-9cd0-14bb34772cad@amazon.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 5/14/20 14:58, jeff(dot)janes(at)gmail(dot)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(at)amazon(dot)com
> <mailto:schnjere(at)amazon(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Dilger | 2020-05-14 22:23:53 | Re: [PATCH] Fix ouside scope t_ctid (ItemPointerData) |
Previous Message | Jeff Janes | 2020-05-14 21:58:35 | Re: SEQUENCE values (duplicated) in some corner cases when crash happens |