Re: Re: sequences

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
>