Re: Bug #613: Sequence values fall back to previously chec

On Thu, 14 Mar 2002, Mikheev, Vadim wrote:

> And it's not. But behaviour of application *must* be
> conditional on was transaction committed or not.
>
> What's the problem for application that need nextval() for
> external (out-of-database) purposes to use sequence values
> only after transaction commit? What's *wrong* for such application
> to behave the same way as when dealing with other database objects
> which are under transaction control (eg only after commit you can
> report to user that $100 was successfully added to his/her account)?

But sequences should not be under transaction control. Can you
safely rollback a sequence? No! The only way to ensure that would
be to lock the sequence for the duration of the transaction. If
you want an ACID compliant sequential value, you implement it using
a transaction safe method (e.g. a table with rows you can lock for
the duration of a transaction). If you want a number that is
guaranteed to always move in one direction, return the next value
without requiring locks, and may have gaps in the numbers returned,
you choose a sequence.

Placing a restriction on an application that says it must treat
the values returned from a sequence as if they might not be committed
is absurd. What about applications that don't use explicit
transactions? As soon as a result comes back it should be considered
'live', on disk, never think about it again.

> I agree that if nextval-s were only "write" actions in transaction
> and they made some XLogInsert-s then WAL must be flushed at commit
> time. But that's it. Was this fixed? Very easy.

But aren't the nextval's always going to be the only write actions
in their transactions since the nextval isn't really a part of the
transaction that called it? If it were, then it could be rolled
back along with that transaction. This is why you can, right now,
insert data into a table with a serial column, committing after
each row, crash the database and STILL have the sequence fall back
to its initial state. The XLogInserts that occur from the table
inserts must not happen in the same xact as the nextval's
XLogInserts. I can demonstrate the behavior quite easilly, and
Bruce posted results that confirmed it.

-- Ben