Обсуждение: Can I prevent a sequence from being updated when a transaction fail?
Hello All! :) I have a sequence that I use to get an unique default value for an attribute in one of my tables. The sequence is incremented by one each time I insert a new tuple in the table. Excellent! :) An example: Given the sequence 'serial' and the relation 'test': Table test ---------- number : integer default nextval('serial') whatever : text Table 'test' is constrained by having the attribute 'whatever' being a member of the set {'foo', 'bar'}. Now I execute the following transaction: BEGIN; INSERT INTO test (whatever) values('Mars bar'); ROLLBACK; This INSERT is obviously illegal given the constraint on attribute 'whatever'. BUT, even after the rollback, the value of the sequence 'serial' remains incremented by the failing INSERT statement??! How come sequences are not rolled back when a transaction is aborted, and can I do anything to prevent a failing statement from updating the involved sequence? -- Thomas Holmgren Institute for Computer Science University of Aalborg Denmark
Thomas Holmgren wrote: > How come sequences are not rolled back when a transaction is aborted, and > can I do anything to prevent a failing statement from updating the > involved sequence? No. You can't. To achieve what you want, the sequence counter should be locked. That would mean that parallel insertions in the same table from other transactions, should wait for the first to end. And that is not acceptable. If you don care about it, i.e., either your aplication never inserts parallelly in a table, or a second insert can wait for the first to end, then use your own counter in an auxiliary table. This will do what you asked. Haroldo.