Thread: Sequencing Problem in Transaction..
Dear All, In postgresql 7.1, there is a problem in sequence. While rollbacking in transaction mode, the sequences are not actually getting rolled back. This problem is still there in 7.2, it seems! When they planned to fix this? Any idea? With Regards, Bhuvaneswaran.
Ummm...this is not a bug - it is utterly essential that sequences are not rolled back!!! Think about this: Sequence counter is 9 Transaction 1 gets sequence no. 10 Transaction 2 gets sequence no. 11 transaction 1 rolls back, sequence no. reset to 9 Transaction 3 gets sequence number 10 Transaction 4 gets sequence number 11 - BAM!!! Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Bhuvan A > Sent: Tuesday, 12 February 2002 1:26 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Sequencing Problem in Transaction.. > > > > Dear All, > > In postgresql 7.1, there is a problem in sequence. > > While rollbacking in transaction mode, the sequences are not actually > getting rolled back. This problem is still there in 7.2, it seems! > > When they planned to fix this? Any idea? > > With Regards, > Bhuvaneswaran. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Tue, 12 Feb 2002, Bhuvan A wrote: > > Dear All, > > In postgresql 7.1, there is a problem in sequence. > > While rollbacking in transaction mode, the sequences are not actually > getting rolled back. This problem is still there in 7.2, it seems! > Probably never, this is the intented behavior. If rollbacks rolled the sequences back then you'd have to wait for the first transaction to rollback or commit before giving the number out to a second transaction running at the same time (thus you'd effectively be limited to a single concurrent inserting transaction for most uses of sequences)
On Feb 12, Christopher Kings-Lynne wrote: > Ummm...this is not a bug - it is utterly essential that sequences are not > rolled back!!! > > Think about this: > > Sequence counter is 9 > Transaction 1 gets sequence no. 10 > Transaction 2 gets sequence no. 11 > transaction 1 rolls back, sequence no. reset to 9 > Transaction 3 gets sequence number 10 > Transaction 4 gets sequence number 11 - BAM!!! > Good and Thankx!!! Regards, Bhuvaneswar. > Chris > > > -----Original Message----- > > From: pgsql-sql-owner@postgresql.org > > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Bhuvan A > > Sent: Tuesday, 12 February 2002 1:26 PM > > To: pgsql-sql@postgresql.org > > Subject: [SQL] Sequencing Problem in Transaction.. > > > > > > > > Dear All, > > > > In postgresql 7.1, there is a problem in sequence. > > > > While rollbacking in transaction mode, the sequences are not actually > > getting rolled back. This problem is still there in 7.2, it seems! > > > > When they planned to fix this? Any idea? > > > > With Regards, > > Bhuvaneswaran. > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > >
Bhuvan A wrote: > > > On Feb 12, Christopher Kings-Lynne wrote: > > > Ummm...this is not a bug - it is utterly essential that sequences are not > > rolled back!!! > > > > Think about this: > > > > Sequence counter is 9 > > Transaction 1 gets sequence no. 10 > > Transaction 2 gets sequence no. 11 > > transaction 1 rolls back, sequence no. reset to 9 > > Transaction 3 gets sequence number 10 > > Transaction 4 gets sequence number 11 - BAM!!! > > > Good and Thankx!!! Believe it or not, this _wasn't_ in the FAQ, though it certainly is asked enough times. Added now as item 4.15.4: 4.15.4) Why aren't my sequence numbers reused on transaction abort? Why are there gaps in the numbering of my sequence/SERIALcolumn? To improve concurrency, sequence values are given out to running transactions as needed and arenot locked until the transaction completes. This causes gaps in numbering from aborted transactions. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026