Re: sequences vs. synchronous replication

On 15.01.22 23:57, Tomas Vondra wrote:
>> This approach (and also my previous proposal) seems to assume that the
>> value returned from nextval() should not be used until the transaction
>> executing that nextval() has been committed successfully. But I'm not
>> sure how many applications follow this assumption. Some application
>> might use the return value of nextval() instantly before issuing
>> commit command. Some might use the return value of nextval() executed
>> in rollbacked transaction.
>>
>
> IMO any application that assumes data from uncommitted transactions is
> outright broken and we should not try to fix that because it's quite
> futile (and likely will affect well-behaving applications).
>
> The issue I'm trying to fix in this thread is much narrower - we don't
> actually meet the guarantees for committed transactions (that only did
> nextval without generating any WAL).

The wording in the SQL standard is:

"Changes to the current base value of a sequence generator are not
controlled by SQL-transactions; therefore, commits and rollbacks of
SQL-transactions have no effect on the current base value of a sequence
generator."

This implies the well-known behavior that consuming a sequence value is
not rolled back. But it also appears to imply that committing a
transaction has no impact on the validity of a sequence value produced
during that transaction. In other words, this appears to imply that
making use of a sequence value produced in a rolled-back transaction is
valid.

A very strict reading of this would seem to imply that every single
nextval() call needs to be flushed to WAL immediately, which is of
course impractical.