Re: Exploring sequences (sequence context within a transaction) - Mailing list pgsql-general

On Tue, 5 Jun 2001, NetBeans wrote:

> I searched through mailing list archives but was unable to find full
> coverage of this question -- my apologies if this is a reposted question.
>
> As in the FAQ, I am trying to retrieve the value of a sequence value from a
> newly inserted row.  So, first I call something like:
>
>     insert into foobar (foo, bar) values (nextval('foobar_foo_seq'),
> 'whatever');
>
> Then, I want to retrieve the value that generated from the sequence and
> inserted into the table, so I use a call to currval:
>
>     insert into foobar_rel_table(foo_fk, baz) values
> (currval('foobar_foo_seq', 'something else');
>
> This is (one of the methods that is) prescribed in the FAQ.  However, I'm
> concerned that another transaction attempting to insert into the same table
> might make a call to nextval('foobar_foo_seq') between the two operations
> above.  This would mean that my second statement would use the wrong value
> from the sequence.
> I've tested this scenario with different transaction isolation levels, and
> it appears that any state changes to sequences become immediately visible to
> other transactions (obviously, a read-commited type strategy wouldn't work,
> however, serializing access to sequences, or explicit locking would solve
> this problem).

Pg handles this perfectly fine i.e. the sequence increment is visible to all transactions globally so whoever calls
nextval('seq')will get the correct "next value" for the given sequence, even though the value that another transaction
insertedinto the table is not visible until that transaction commits. But even so a call to currval('seq') will return
thelast value that the current transaction last used from this sequence. 

I tried this in the standard Xion isolation level (read_committed) and it works fine.

thalis=# show TRANSACTION ISOLATION LEVEL ;
NOTICE:  TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE
thalis=# begin;
BEGIN
thalis=# insert into xoxo values (nextval('xoxo_i_seq'));
INSERT 9324957 1
thalis=# select * from xoxo;
 i
----
  0
  1
  2
  3
  4
  6
  7
  8
  9
 10
 11
 12
(12 rows)

In another window I start another transaction:

thalis=# begin;
BEGIN
thalis=# select * from xoxo;
 i
----
  0
  1
  2
  3
  4
  6
  7
  8
  9
 10
 11
(11 rows)

[12 is not yet visible because Xion 1 hasn't committed yet]

thalis=# insert into xoxo values (nextval('xoxo_i_seq'));
INSERT 9324958 1
thalis=# select * from xoxo;
 i
----
  0
  1
  2
  3
  4
  6
  7
  8
  9
 10
 11
 13
(12 rows)

[correctly inserted a value of 13 i.e. nextval() returned the right value]

Now returning to Xion 1 I ask for the currval() of xoxo_i_seq:

thalis=# select currval('xoxo_i_seq');
 currval
---------
      12
(1 row)

[still sees 12 even though 13 has been used. This doesn't mean that a call to nextval('xoxo_i_seq') will return the
wrongvalue] 

thalis=# insert into xoxo values (nextval('xoxo_i_seq'));
INSERT 9324959 1
thalis=# select * from xoxo;
 i
----
  0
  1
  2
  3
  4
  6
  7
  8
  9
 10
 11
 12
 14
(13 rows)


[nextval() indeed returned the correct value. Valu 13 that is inserted by Xion 2 will not be visible until it commits]


Clear as mud? :-)


cheers,
thalis

> Has anyone else come across this problem, and is there a workaround?  If
> not, are there any alternate suggestions for generating a PK on insert and
> immediately retrieving it that is free from concurrency issues?
>
> Any help would be appreciated.  Thanks!  -- Erik
>
> --
> Erik Pearson
> erik@cariboulake.com
> http://www.cariboulake.com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>