Thread: Re: sequences

Edward Q. Bridges's detailed statement regarding sequences, of which I extract merely the most pessimistic part:

>  begin;
>  insert into foo (A,B) values (B);
>  select currval('foo_A_seq');
>  commit;
>
>  note that the transaction is key, without
> which there's no guarantee that some other
> statement will affect the value of the
> sequence.

quite clearly conflicts what what seems to me to be the plain meaning of the manual page for CREATE SEQUENCE which
states,in part: 

  > multiple backends are guaranteed to
  > allocate distinct sequence values

Can some knowledgable person here save a bunch of us plain old user-programmers the trouble of trying to trace down
whatthe source says and just clarify this issue?  Thanks! 
>


Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com

actually they're saying two different things :)

first, to explain my example a bit better:

the difference between this:

> >  begin;
> >  insert into foo (A,B) values (B);
> >  select currval('foo_A_seq');
> >  commit;

and this:

> >  insert into foo (A,B) values (B);
> >  select currval('foo_A_seq');

is that the first is considered (by the rdbms) to be
one "atomic" transaction; the second is considered to
be two.

the rdbms processes one transaction at a time, in no
guaranteed order (basically).  so, in theory, there is
a possibility that an insert by another user to table
foo could occur after your insert and before your select
off the sequence.  the implication being, you would get
a value for A that would not refer to the row you just
inserted.  by grouping the sql statements into a single
transaction, you ensure the rdbms will process them in
the order you specify.

the other statement you quote from the docs (which is not
entirely clear to me without context) seems to refer to
the fact that a sequence will never return the same number
twice when nextval('seq_name') is called.

HTH
--e--


On Wed, 20 Sep 2000 23:13:23 -0700, K Parker wrote:

> Edward Q. Bridges's detailed statement regarding sequences, of which I extract merely the
most pessimistic part:
>
> >  begin;
> >  insert into foo (A,B) values (B);
> >  select currval('foo_A_seq');
> >  commit;
> >
> >  note that the transaction is key, without
> > which there's no guarantee that some other
> > statement will affect the value of the
> > sequence.
>
> quite clearly conflicts what what seems to me to be the plain meaning of the manual page for
CREATE SEQUENCE which states, in part:
>
>   > multiple backends are guaranteed to
>   > allocate distinct sequence values
>
> Can some knowledgable person here save a bunch of us plain old user-programmers the trouble
of trying to trace down what the source says and just clarify this issue?  Thanks!
> >
>
>
> Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at
http://www.eudoramail.com
>

Actually, it looks like currval is defined to give the
value last used in your session.  So, the second case
(not in transaction) should still always give the
value of the last nextval, assuming they're part of
the same session.

Stephan Szabo
sszabo@bigpanda.com

On Thu, 21 Sep 2000, Edward Q. Bridges wrote:

> actually they're saying two different things :)
>
> first, to explain my example a bit better:
>
> the difference between this:
>
> > >  begin;
> > >  insert into foo (A,B) values (B);
> > >  select currval('foo_A_seq');
> > >  commit;
>
> and this:
>
> > >  insert into foo (A,B) values (B);
> > >  select currval('foo_A_seq');
>
> is that the first is considered (by the rdbms) to be
> one "atomic" transaction; the second is considered to
> be two.
>
> the rdbms processes one transaction at a time, in no
> guaranteed order (basically).  so, in theory, there is
> a possibility that an insert by another user to table
> foo could occur after your insert and before your select
> off the sequence.  the implication being, you would get
> a value for A that would not refer to the row you just
> inserted.  by grouping the sql statements into a single
> transaction, you ensure the rdbms will process them in
> the order you specify.
>
> the other statement you quote from the docs (which is not
> entirely clear to me without context) seems to refer to
> the fact that a sequence will never return the same number
> twice when nextval('seq_name') is called.
>
> HTH
> --e--
>
>
> On Wed, 20 Sep 2000 23:13:23 -0700, K Parker wrote:
>
> > Edward Q. Bridges's detailed statement regarding sequences, of which I extract merely the
> most pessimistic part:
> >
> > >  begin;
> > >  insert into foo (A,B) values (B);
> > >  select currval('foo_A_seq');
> > >  commit;
> > >
> > >  note that the transaction is key, without
> > > which there's no guarantee that some other
> > > statement will affect the value of the
> > > sequence.
> >
> > quite clearly conflicts what what seems to me to be the plain meaning of the manual page for
> CREATE SEQUENCE which states, in part:
> >
> >   > multiple backends are guaranteed to
> >   > allocate distinct sequence values
> >
> > Can some knowledgable person here save a bunch of us plain old user-programmers the trouble
> of trying to trace down what the source says and just clarify this issue?  Thanks!
> > >
> >
> >
> > Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at
> http://www.eudoramail.com
> >
>
>
>

 From memory of previous discussions, what a sequence actually does is
guarantee you a unique value, although those vales may not actually occur
in numeric order (ie, a record put in at 1:10pm might get a sequence value
of 12, one at 1:11 might get 15, and a third at 1:12 might get 13.)  This
is because each backend allocates it's own block of values within a
sequence when it sees that it's going to need to use it (or at least used
to with 6.5, dunno if it was changed in 7.)   So if your first backend
opens up, uses a sequence, then you putter around for a couple more minutes
doing other selects/updates/whatever commands on the same connection, then
use the sequence again, your two values will probably be consecutive.  If
in the middle of that putterring around another backend came in and used
the sequence though, it's going to get a higher value than both the
sequences of the previous connection, even if it uses the sequence before
the second one of the first connection.  I'm not sure how this is effected
by transactions / no transactions, but this is how someone explained it to
me a few months ago.

If you really want to test it though, open up two psql connections at the
same time in different terminals and try some insert's to see what you
get.  Might want to try it both inside and outside of transaction blocks
just in case.



At 03:13 AM 9/21/00, K Parker wrote:
>Edward Q. Bridges's detailed statement regarding sequences, of which I
>extract merely the most pessimistic part:
>
> >  begin;
> >  insert into foo (A,B) values (B);
> >  select currval('foo_A_seq');
> >  commit;
> >
> >  note that the transaction is key, without
> > which there's no guarantee that some other
> > statement will affect the value of the
> > sequence.
>
>quite clearly conflicts what what seems to me to be the plain meaning of
>the manual page for CREATE SEQUENCE which states, in part:
>
>   > multiple backends are guaranteed to
>   > allocate distinct sequence values
>
>Can some knowledgable person here save a bunch of us plain old
>user-programmers the trouble of trying to trace down what the source says
>and just clarify this issue?  Thanks!
> >
>
>
>Join 18 million Eudora users by signing up for a free Eudora Web-Mail
>account at http://www.eudoramail.com

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Actually, it looks like currval is defined to give the
> value last used in your session.  So, the second case
> (not in transaction) should still always give the
> value of the last nextval, assuming they're part of
> the same session.

Stephan is correct: you can safely do
    insert into foo(B) values (Bvalue);  -- column A is defaulted
    select currval('foo_A_seq');
even without a transaction block, and be assured of getting the same
value back that was assigned to A.

At least, you don't have to worry about other backends messing you up.
But it's still possible to shoot yourself in the foot.  For example, if
you have rules or triggers on insert to foo, and those rules/triggers
themselves cause additional calls to nextval('foo_A_seq'), then the
eventual currval() will return the latest such result, which might not
have been what got inserted into foo.  Note that using a transaction
block will not protect you from this gaffe.

For this reason, and because it just seems cleaner to me, I prefer to
solve this problem like so:
    select nextval('foo_A_seq');
    insert into foo(A,B) values (just-returned-value, Bvalue);
That gets the same result with about the same amount of work, but
seems more understandable and safer to me.  It's mostly a matter of
taste, though.

            regards, tom lane