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.
