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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
