Thread: Re: [BUGS] Bug #613: Sequence values fall back to previously chec
> But sequences should not be under transaction control. Can you > safely rollback a sequence? No! The only way to ensure that would ... > 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. Why? The fact that you are not able to rollback sequences does not necessary mean that you are not required to perform commit to ensure permanent storage of changes made to database. And isn't it absurd to do more XLogFlush-es for non-transactional objects than we do for transactional ones? And why? Just for convenience of << 1% applications which need to use sequences in their own, non-database, external objects? We are not required to care about those objects, but we'd better care about performance of our operations over our objects. > > 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 There are no nextval' transactions. See how XLOG_NO_TRAN flag is used in XLogInsert and you'll see why there is no XLogFlush after transaction-with-nextval-only (which causes N1 reported problem). > 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. Just wait until Tom adds check for system RedoRecPtr in nextval() and try to reproduce this behaviour (N2 reported problem) after that. Vadim
(userland comment) On Fri, Mar 15, 2002 at 01:05:33AM -0800, Vadim Mikheev wrote: | > But sequences should not be under transaction control. Can you | > safely rollback a sequence? No! The only way to ensure that would | ... | > 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. | | Why? The fact that you are not able to rollback sequences does not | necessary mean that you are not required to perform commit to ensure | permanent storage of changes made to database. I use sequences to generate message identifiers for a simple external-to-database message passing system. I also use them for file upload identifiers. In both cases, if the external action (message or file upload) succeeds, I commit; otherwise I roll-back. I assume that the datbase won't give me a duplicate sequence... otherwise I'd have to find some other way go get sequences or I'd have duplicate messages or non-unique file identifiers. With these changes is this assumption no longer valid? If so, this change will break alot of user programs. | And why? Just for convenience of << 1% applications which need | to use sequences in their own, non-database, external objects? I think you may be underestimating the amount of "external resources" which may be associated with a datbase object. Regardless, may of the database features in PostgreSQL are there for 1% or less of the user base... Best, Clark -- Clark C. Evans Axista, Inc. http://www.axista.com 800.926.5525 XCOLLA Collaborative Project Management Software
I do basically the same thing for files. Except I md5 a 4 character random string, and the sequence ID just incase I get the same one twice -- as it's never been written in stone that I wouldn't -- not to mention the high number of requests for returning a sequence ID back to the pool on a rollback. Anyway, you might try using the OID rather than a sequence ID but if you rollback the database commit due to failure of an action externally, shouldn't you be cleaning up that useless external stuff as well? -- Rod Taylor This message represents the official view of the voices in my head ----- Original Message ----- From: "Clark C . Evans" <cce@clarkevans.com> To: "Vadim Mikheev" <vmikheev@sectorbase.com> Cc: <pgsql-hackers@postgresql.org> Sent: Friday, March 15, 2002 8:54 PM Subject: Re: [HACKERS] [BUGS] Bug #613: Sequence values fall back to previously chec > (userland comment) > > On Fri, Mar 15, 2002 at 01:05:33AM -0800, Vadim Mikheev wrote: > | > But sequences should not be under transaction control. Can you > | > safely rollback a sequence? No! The only way to ensure that would > | ... > | > 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. > | > | Why? The fact that you are not able to rollback sequences does not > | necessary mean that you are not required to perform commit to ensure > | permanent storage of changes made to database. > > I use sequences to generate message identifiers for a simple > external-to-database message passing system. I also use > them for file upload identifiers. In both cases, if the > external action (message or file upload) succeeds, I commit; > otherwise I roll-back. I assume that the datbase won't give > me a duplicate sequence... otherwise I'd have to find some > other way go get sequences or I'd have duplicate messages > or non-unique file identifiers. > > With these changes is this assumption no longer valid? If > so, this change will break alot of user programs. > > | And why? Just for convenience of << 1% applications which need > | to use sequences in their own, non-database, external objects? > > I think you may be underestimating the amount of "external resources" > which may be associated with a datbase object. Regardless, may of the > database features in PostgreSQL are there for 1% or less of the > user base... > > Best, > > Clark > > -- > Clark C. Evans Axista, Inc. > http://www.axista.com 800.926.5525 > XCOLLA Collaborative Project Management Software > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
> | > 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. > | > | Why? The fact that you are not able to rollback sequences does not > | necessary mean that you are not required to perform commit to ensure > | permanent storage of changes made to database. > > I use sequences to generate message identifiers for a simple > external-to-database message passing system. I also use > them for file upload identifiers. In both cases, if the > external action (message or file upload) succeeds, I commit; > otherwise I roll-back. I assume that the datbase won't give > me a duplicate sequence... otherwise I'd have to find some So can you do "select nextval()" in *separate* (committed) transaction *before* external action and "real" transaction where you store information (with sequence number) about external action in database? BEGIN; SELECT NEXTVAL(); COMMIT; BEGIN; -- Do external actions and store info in DB -- COMMIT/ROLLBACK; Is this totally unacceptable? Is it really *required* to call nextval() in *the same* transaction where you store info in DB? Why? > other way go get sequences or I'd have duplicate messages > or non-unique file identifiers. > > With these changes is this assumption no longer valid? If 1. It's not valid to assume that sequences will not return duplicate numbers if there was no commit after nextval. 2. It doesn't matter when sequence numbers are stored in database objects only. 3. But if you're going to use sequence numbers in external objects you must (pre)fetch those numbers in separate committed transaction. (Can we have this in FAQ?) > so, this change will break alot of user programs. > > | And why? Just for convenience of << 1% applications which need > | to use sequences in their own, non-database, external objects? > > I think you may be underestimating the amount of "external resources" > which may be associated with a datbase object. Regardless, may of the > database features in PostgreSQL are there for 1% or less of the > user base... Please note that I was talking about some *inconvenience*, not about *inability* of using sequence numbers externally (seems my words were too short). Above is how to do this. And though I agreed that it's not very convenient/handy/cosy to *take care* and fetch numbers in separate committed transaction, but it's required only in those special cases and I think it's better than do fsync() per each nextval() call what would affect other users/applications where storing sequence numbers outside of database is not required. Vadim
On Fri, 15 Mar 2002, Vadim Mikheev wrote: > > But sequences should not be under transaction control. Can you > > safely rollback a sequence? No! The only way to ensure that would > ... > > 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. > > Why? The fact that you are not able to rollback sequences does not > necessary mean that you are not required to perform commit to ensure > permanent storage of changes made to database. I'm not sure I agree, but I'll wait to see the behavior of the db after the changes are made. > And isn't it absurd to do more XLogFlush-es for non-transactional objects > than we do for transactional ones? And why? Just for convenience of > << 1% applications which need to use sequences in their own, > non-database, external objects? We are not required to care about those > objects, but we'd better care about performance of our operations over our > objects. Yes, absolutely - if there's a better way, which apparently there is, then sure, eliminate the calls to XLogFlush. It's a workaround, a hack. I am much more concerned with getting the behavior correct than I am about getting some code with my name on it into a release. My workarounds only served to point out flaws in the design, even if I didn't quite understand at the time why they worked :-) > There are no nextval' transactions. See how XLOG_NO_TRAN flag > is used in XLogInsert and you'll see why there is no XLogFlush > after transaction-with-nextval-only (which causes N1 reported problem). > > Just wait until Tom adds check for system RedoRecPtr in nextval() > and try to reproduce this behaviour (N2 reported problem) > after that. > Thank you! I think I have much better understanding of how this works now. When these bugs are fixed there is still the issue of bug #3 that I came across. The one that I work around by resetting log_cnt to 0 when a backend initializes a sequence. It's this third bug that made the other two so apparent. Fixing them does not obviate the need to fix this one. Is there a way to intercept writes or reads such that when a sequnce is going to or from disk that we can force log_cnt = 0? Right now that's worked around by my 'reset_logcnt' flag in the patch, but I know that it may not be an ideal solution. But, since sequences are just tuples like everything else I don't see an obvious way to prevent it. -- Ben