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

Alvaro Herrera <alvherre@2ndquadrant.com>

From: Alvaro Herrera <alvherre@2ndquadrant.com>

To: Jeremy Schneider <schnjere@amazon.com>

Cc: jeff.janes@gmail.com, Tom Lane <tgl@sss.pgh.pa.us>, Vinicius Abrahao <vinnix.bsd@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Vinícius Schmidt <vinics@amazon.com>

Date: 2020-05-14T22:47:29Z

Lists: pgsql-hackers

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