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

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Jeremy Schneider <schnjere(at)amazon(dot)com>
Cc: jeff(dot)janes(at)gmail(dot)com, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 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:47:29
Message-ID: 20200514224729.GA1233@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2020-05-14 22:49:39 Re: [PATCH] Fix ouside scope t_ctid (ItemPointerData)
Previous Message Mark Dilger 2020-05-14 22:23:53 Re: [PATCH] Fix ouside scope t_ctid (ItemPointerData)