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.