Re: Re: sequences - Mailing list pgsql-general

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
> >
>
>
>