Thread: BUG #3240: Unexpected evaluation sequence

The following bug has been logged online:

Bug reference:      3240
Logged by:          Kevin Macdonald
Email address:      kevin.macdonald@pentura.ca
PostgreSQL version: 8.2.3
Operating system:   Windows XP
Description:        Unexpected evaluation sequence
Details:

Take these two operations:

SQL> select currval ('seq')

returns 10

SQL> select currval ('seq'), nextval ('seq')

returns 10, 11

This is ok.

Now, take these two operations:

SQL> select currval ('seq')

returns 11

SQL> select nextval ('seq'), currval ('seq')

returns (12, 12)

I would have expected it to have returned (12, 11). It seems that the value
of currval above should have been the value immediately before the SQL
statement was executed -- a value of 11.


Take this example for comparison:

SQL> create table foo (c1 integer, c2 integer)
SQL> insert into foo values (1, 1);
SQL> update foo set c1=c1+1, c2=c1
SQL> select c1, c2 from foo

returns (2, 1)

In this case, although the expression 'c1=c1+1' is executed before the
expression 'c2=c1', the value of 'c1' in the second expression is the value
immediately before the SQL statement was executed.

This behavior contrasts with that from the evaluation of the sequences
above.

Is this inconsistency done on purpose?

Thank you.

"Kevin Macdonald" <kevin.macdonald@pentura.ca> writes:
> Now, take these two operations:

> SQL> select currval ('seq')

> returns 11

> SQL> select nextval ('seq'), currval ('seq')

> returns (12, 12)

> I would have expected it to have returned (12, 11).

No, because currval reports the latest nextval result in your session,
and the nextval has already been executed.  This is dependent on order
of execution of select-list items, but at the moment that's always
left-to-right.

> It seems that the value
> of currval above should have been the value immediately before the SQL
> statement was executed -- a value of 11.

Since sequence operations are non-transactional by definition, I'm not
really sure why you'd expect that.

            regards, tom lane