pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbegin/end/abort work for sequences?
7+ messages / 5 participants
[nested] [flat]
* begin/end/abort work for sequences?
@ 1999-06-30 04:11 Kyle <kyle@actarg.com>
1999-06-30 12:08 ` Re: [SQL] begin/end/abort work for sequences? José Soares <jose@sferacarta.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kyle @ 1999-06-30 04:11 UTC (permalink / raw)
To: pgsql-sql
I assumed that wrapping a call to nextval() in a transaction would allow
me to roll back the update of the sequence. But when I do the following:
begin;
select nextval('po_seq');
abort;
The sequence po_seq is advancing to the next value even though the
transaction was aborted. If I put other things like updates, inserts, etc.
inside the transaction, they seem to be rolled back OK, but not the
update of the sequence.
Am I doing something wrong?
----------------------------------------------------
Kyle Bateman President, Action Target Inc.
"Viva Yo!" kyle@actarg.com (801)377-8033x101
----------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] begin/end/abort work for sequences?
1999-06-30 04:11 begin/end/abort work for sequences? Kyle <kyle@actarg.com>
@ 1999-06-30 12:08 ` José Soares <jose@sferacarta.com>
1999-07-01 03:26 ` Re: [SQL] begin/end/abort work for sequences? Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: José Soares @ 1999-06-30 12:08 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql
Kyle ha scritto:
> I assumed that wrapping a call to nextval() in a transaction would allow
> me to roll back the update of the sequence. But when I do the following:
>
> begin;
> select nextval('po_seq');
> abort;
>
> The sequence po_seq is advancing to the next value even though the
> transaction was aborted. If I put other things like updates, inserts, etc.
> inside the transaction, they seem to be rolled back OK, but not the
> update of the sequence.
>
> Am I doing something wrong?
No. I can't explain you why but AFAIK sequences don't rollback.
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] begin/end/abort work for sequences?
1999-06-30 04:11 begin/end/abort work for sequences? Kyle <kyle@actarg.com>
1999-06-30 12:08 ` Re: [SQL] begin/end/abort work for sequences? José Soares <jose@sferacarta.com>
@ 1999-07-01 03:26 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-01 14:24 ` Re: [SQL] begin/end/abort work for sequences? Kyle Bateman <kyle@actarg.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 1999-07-01 03:26 UTC (permalink / raw)
To: [Jos_] Soares <jose@sferacarta.com>; +Cc: Kyle <kyle@actarg.com>; pgsql-sql
> > The sequence po_seq is advancing to the next value even though the
> > transaction was aborted. If I put other things like updates, inserts, etc.
> > inside the transaction, they seem to be rolled back OK, but not the
> > update of the sequence.
> >
> > Am I doing something wrong?
>
> No. I can't explain you why but AFAIK sequences don't rollback.
If we rolled back sequence numbers, we would have to lock the table
until the transaction commited or was rolled back. That is too much
locking so was not worth it.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] begin/end/abort work for sequences?
1999-06-30 04:11 begin/end/abort work for sequences? Kyle <kyle@actarg.com>
1999-06-30 12:08 ` Re: [SQL] begin/end/abort work for sequences? José Soares <jose@sferacarta.com>
1999-07-01 03:26 ` Re: [SQL] begin/end/abort work for sequences? Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-07-01 14:24 ` Kyle Bateman <kyle@actarg.com>
1999-07-01 15:30 ` Re: [SQL] begin/end/abort work for sequences? Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-01 16:26 ` Re: [SQL] begin/end/abort work for sequences? D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 2 replies; 7+ messages in thread
From: Kyle Bateman @ 1999-07-01 14:24 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bruce Momjian <maillist@candle.pha.pa.us>; [Jos_] Soares <jose@sferacarta.com>
Bruce Momjian wrote:
> > > The sequence po_seq is advancing to the next value even though the
> > > transaction was aborted. If I put other things like updates, inserts, etc.
> > > inside the transaction, they seem to be rolled back OK, but not the
> > > update of the sequence.
> > >
> > > Am I doing something wrong?
> >
> > No. I can't explain you why but AFAIK sequences don't rollback.
>
> If we rolled back sequence numbers, we would have to lock the table
> until the transaction commited or was rolled back. That is too much
> locking so was not worth it.
>
That seems like a pretty big thing to sacrifice. Did sequence locking work before
6.5? It was my impression that it did.
Do you know if there is a workaround? In my particular situation, it is critical
that all instances of the sequence actually get used. If a transaction is
aborted, I lose an instance and everything gets messed up.
--
----------------------------------------------------
Kyle Bateman President, Action Target Inc.
"Viva Yo!" kyle@actarg.com (801)377-8033x101
----------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] begin/end/abort work for sequences?
1999-06-30 04:11 begin/end/abort work for sequences? Kyle <kyle@actarg.com>
1999-06-30 12:08 ` Re: [SQL] begin/end/abort work for sequences? José Soares <jose@sferacarta.com>
1999-07-01 03:26 ` Re: [SQL] begin/end/abort work for sequences? Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-01 14:24 ` Re: [SQL] begin/end/abort work for sequences? Kyle Bateman <kyle@actarg.com>
@ 1999-07-01 15:30 ` Bruce Momjian <maillist@candle.pha.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 1999-07-01 15:30 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql@postgreSQL.org, "[Jos_] Soares" <jose@sferacarta.com>
> > If we rolled back sequence numbers, we would have to lock the table
> > until the transaction commited or was rolled back. That is too much
> > locking so was not worth it.
> >
>
> That seems like a pretty big thing to sacrifice. Did sequence locking work before
> 6.5? It was my impression that it did.
No, sequence numbers of aborted transactions never were saved.
> Do you know if there is a workaround? In my particular situation, it is critical
> that all instances of the sequence actually get used. If a transaction is
> aborted, I lose an instance and everything gets messed up.
I recommend hand-rolling a sequence number, similar to how the sequence
mechanism works, but explicitly lock your sequence table, retrieve the
value, and the commit/abort will unlock the table.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] begin/end/abort work for sequences?
1999-06-30 04:11 begin/end/abort work for sequences? Kyle <kyle@actarg.com>
1999-06-30 12:08 ` Re: [SQL] begin/end/abort work for sequences? José Soares <jose@sferacarta.com>
1999-07-01 03:26 ` Re: [SQL] begin/end/abort work for sequences? Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-01 14:24 ` Re: [SQL] begin/end/abort work for sequences? Kyle Bateman <kyle@actarg.com>
@ 1999-07-01 16:26 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-07-01 23:29 ` Re: [SQL] begin/end/abort work for sequences? Kyle Bateman <kyle@actarg.com>
1 sibling, 1 reply; 7+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-07-01 16:26 UTC (permalink / raw)
To: kyle@actarg.com; +Cc: pgsql-sql
Thus spake Kyle Bateman
> Do you know if there is a workaround? In my particular situation, it is critical
> that all instances of the sequence actually get used. If a transaction is
> aborted, I lose an instance and everything gets messed up.
I had to solve this once in another RDBMS (Progress) and this is how I
did it. I had a table of available sequence numbers consisting of two
fields, a name and a number. The table was seeded with 10 entries for
each required sequence numbered 1 to 10. When I needed a sequence I
started a transaction and grabbed and locked the lowest numbered record
for that sequence that wasn't locked. I save the number then add 10 to
it. If I abort the transaction, the number remains unchanged. Under
this scheme 10 users can be working on the same sequence without locking
others out. Numbers can be used out of sequence from time to time if
someone aborts but they all get used eventually.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] begin/end/abort work for sequences?
1999-06-30 04:11 begin/end/abort work for sequences? Kyle <kyle@actarg.com>
1999-06-30 12:08 ` Re: [SQL] begin/end/abort work for sequences? José Soares <jose@sferacarta.com>
1999-07-01 03:26 ` Re: [SQL] begin/end/abort work for sequences? Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-01 14:24 ` Re: [SQL] begin/end/abort work for sequences? Kyle Bateman <kyle@actarg.com>
1999-07-01 16:26 ` Re: [SQL] begin/end/abort work for sequences? D'Arcy J.M. Cain <darcy@druid.net>
@ 1999-07-01 23:29 ` Kyle Bateman <kyle@actarg.com>
0 siblings, 0 replies; 7+ messages in thread
From: Kyle Bateman @ 1999-07-01 23:29 UTC (permalink / raw)
To: pgsql-sql
"D'Arcy J.M. Cain" wrote:
> Thus spake Kyle Bateman
> > Do you know if there is a workaround? In my particular situation, it is critical
> > that all instances of the sequence actually get used. If a transaction is
> > aborted, I lose an instance and everything gets messed up.
>
> I had to solve this once in another RDBMS (Progress) and this is how I
> did it. I had a table of available sequence numbers consisting of two
> fields, a name and a number. The table was seeded with 10 entries for
> each required sequence numbered 1 to 10. When I needed a sequence I
> started a transaction and grabbed and locked the lowest numbered record
> for that sequence that wasn't locked. I save the number then add 10 to
> it. If I abort the transaction, the number remains unchanged. Under
> this scheme 10 users can be working on the same sequence without locking
> others out. Numbers can be used out of sequence from time to time if
> someone aborts but they all get used eventually.
>
> --
> D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
> http://www.druid.net/darcy/ | and a sheep voting on
> +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
Thanks for taking the time to reply!
--
----------------------------------------------------
Kyle Bateman President, Action Target Inc.
"Viva Yo!" kyle@actarg.com (801)377-8033x101
----------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-07-01 23:29 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-30 04:11 begin/end/abort work for sequences? Kyle <kyle@actarg.com>
1999-06-30 12:08 ` José Soares <jose@sferacarta.com>
1999-07-01 03:26 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-01 14:24 ` Kyle Bateman <kyle@actarg.com>
1999-07-01 15:30 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-07-01 16:26 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-07-01 23:29 ` Kyle Bateman <kyle@actarg.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox