Thread: RE: Sequences in transaction

Sequences are not supposed to be undoable. A number in the sequence must
"never" turn up twice. Just because you didn't commit the transaction
selecting a new number from the sequence doesn't mean you didn't use it for
something else.

The point being to always getting a unique number, not using every number in
the sequence. You would have to create your own table for that, remembering
"holes" of unused numbers in the sequence for later usage.

/Roger

-----Original Message-----
From: igor [mailto:igor_kh@mailru.com]
Sent: Monday, November 13, 2000 12:18 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Sequences in transaction


Hi,

Help me please to understand such a thing:
Rollback of sequences doesn't work in transaction.
Is it right? Or may be there is another way to restore
it's value?
Running PG 7.02 on RH 6.0

Thanks ,
Igor.

Not even to ensure order.  Some databases allow sequence caching (something we should perhaps look at, if we don't already) which means that when a session requests it's first number from a sequence a whole bundle are actually given to the session.  It may not even use them all, and certainly, if another session is also allocated a bundle of sequence numbers from the same sequence, then different transactions could commit them in any combination of orders.  However, the principle of uniqueness will not be violated.

Cheers...

MikeA

-----Original Message-----
From: Mike Castle [mailto:dalgoda@ix.netcom.com]
Sent: 04 December 2000 23:30
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sequences in transaction

On Mon, Dec 04, 2000 at 05:56:04PM -0500, Camm Maguire wrote:
> Greetings!  I've run into this too, and it appears to me not to roll
> back either.  A pity, as it forced us to make a separate table to hold
> the last sequence value and update the table in a trigger.

This is a dangerous thing if you ever have more than one update going on.

One transaction reads the value, increments it, saves it back.  Another
transaction does the same thing.  Now you have two transactions trying to
use the same value.  Sure, one will probably fail a uniqueness constraint,
but then you have to increment again.  Meanwhile, another transaction comes
in and steals the next number, and one of the first two clashes again.  And
again.  And again.  And again.  You have NO way of guaranteeing that
starvation will never be an issue.

Solution:

Don't require that your sequence values be absolutely sequential.  They're
there to ensure uniquness and order.  Not to be used as counters.

mrc
--
       Mike Castle       Life is like a clock:  You can work constantly
  dalgoda@ix.netcom.com  and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
    We are all of us living in the shadow of Manhattan.  -- Watchmen

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************


On Tue, Dec 05, 2000 at 09:58:13AM -0000, Michael Ansley wrote:
> Not even to ensure order.  Some databases allow sequence caching (something

Doh!  Of course.  My bad.

Actually, I believe pgsql DOES allow for that.  At least I remember reading
about it, but the details escape me.

mrc
--
       Mike Castle       Life is like a clock:  You can work constantly
  dalgoda@ix.netcom.com  and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
    We are all of us living in the shadow of Manhattan.  -- Watchmen