Thread: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL()

The following bug has been logged online:

Bug reference:      1083
Logged by:          Martin Langhoff

Email address:      martin@catalyst.net.nz

PostgreSQL version: 7.4

Operating system:   Linux irkutsk 2.4.25-piv-smp-server #1 SMP Fri Feb 20
16:56:47 NZDT 2004 i686 unknown

Description:        Insert query reordering interacts badly with
NEXTVAL()/CURRVAL()

Details:

=== SQL ===

CREATE TEMP TABLE testing (col_a integer, col_b integer);
CREATE TEMP SEQUENCE seq;

/* this statement will produce the expected result */
INSERT INTO testing (col_a, col_b) VALUES (NEXTVAL('seq'), CURRVAL('seq'));

/* this statement will reverse the order of CURRVAL()/NEXTVAL() to match the
column order of the table */
INSERT INTO testing (col_b, col_a) VALUES (NEXTVAL('seq'), CURRVAL('seq'));

SELECT * FROM testing;

=== END SQL ===

Output looks like:

 col_a | col_b
-------+-------
     1 |     1
     1 |     2

I was expecting:

 col_a | col_b
-------+-------
     1 |     1
     2 |     2

On Mon, Feb 23, 2004 at 21:26:57 -0400,
  PostgreSQL Bugs List <pgsql-bugs@postgresql.org> wrote:
>
> Details:
>
> === SQL ===
>
> CREATE TEMP TABLE testing (col_a integer, col_b integer);
> CREATE TEMP SEQUENCE seq;
>
> /* this statement will produce the expected result */
> INSERT INTO testing (col_a, col_b) VALUES (NEXTVAL('seq'), CURRVAL('seq'));
>
> /* this statement will reverse the order of CURRVAL()/NEXTVAL() to match the
> column order of the table */
> INSERT INTO testing (col_b, col_a) VALUES (NEXTVAL('seq'), CURRVAL('seq'));

I don't think an order of evaluation is guarenteed for INSERT VALUE lists.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> /* this statement will reverse the order of CURRVAL()/NEXTVAL() to match the
> column order of the table */
> INSERT INTO testing (col_b, col_a) VALUES (NEXTVAL('seq'), CURRVAL('seq'));

This is not a bug.  The order of evaluation of select-lists and
values-lists is not defined anywhere in the SQL standard, nor promised
anywhere in the Postgres documentation.  In fact, we specifically say
here:
http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
that "The order of evaluation of subexpressions is not defined."  To do
otherwise would put horrible crimps in our ability to optimize query plans.

            regards, tom lane

"Martin Langhoff (Catalyst IT)" <martin@catalyst.net.nz> writes:
> Thanks for the clarification. I am curious, however: I can't find a
> means to achieve the same effect in a deterministic manner. Any pointers?

How about

SELECT nextval('seq');       -- ignore result

INSERT INTO ... VALUES (currval('seq'), currval('seq'));

            regards, tom lane

Tom Lane wrote:

>"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
>
>
>>/* this statement will reverse the order of CURRVAL()/NEXTVAL() to match the
>>column order of the table */
>>INSERT INTO testing (col_b, col_a) VALUES (NEXTVAL('seq'), CURRVAL('seq'));
>>
>>
>
>This is not a bug.  The order of evaluation of select-lists and
>values-lists is not defined anywhere in the SQL standard, nor promised
>anywhere in the Postgres documentation.
>
Thanks for the clarification. I am curious, however: I can't find a
means to achieve the same effect in a deterministic manner. Any pointers?

regards,




martin

--
-----------------------------------------------------------------------
Martin @ Catalyst .Net .NZ  Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/           PHYS: Level 2, 150-154 Willis St
OFFICE: +64(4)916-7224                              MOB: +64(21)364-017
      Make things as simple as possible, but no simpler - Einstein
-----------------------------------------------------------------------

Tom Lane wrote:

>How about
>
>SELECT nextval('seq');       -- ignore result
>
>INSERT INTO ... VALUES (currval('seq'), currval('seq'));
>
>

Well, it works for my sample case, I have to agree. Maybe I should
mention that I tried to boil down the bugreport to the simplest repro
case I could.

My actual SQL looks roughly like

    INSERT INTO destination (record_id, page, row)
    SELECT
        (SELECT record_id FROM record ORDERED BY name),
        (NEXTVAL('seq') / 200),
        (CURRVAL('seq') % 200)

While I have a workaround, I am definitely curious as to whether there
is actually a way to do it. Thanks for your patience.



regards,





martin

--
-----------------------------------------------------------------------
Martin @ Catalyst .Net .NZ  Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/           PHYS: Level 2, 150-154 Willis St
OFFICE: +64(4)916-7224                              MOB: +64(21)364-017
      Make things as simple as possible, but no simpler - Einstein
-----------------------------------------------------------------------

I am going to try to move this over the sql list, since it doesn't belong
on bugs.

On Tue, Feb 24, 2004 at 23:47:48 +1300, Martin Langhoff <martin@catalyst.net.nz> wrote:
> Tom Lane wrote:
> 
> >How about
> >
> >SELECT nextval('seq');       -- ignore result
> >
> >INSERT INTO ... VALUES (currval('seq'), currval('seq'));
> > 
> >
> 
> Well, it works for my sample case, I have to agree. Maybe I should 
> mention that I tried to boil down the bugreport to the simplest repro 
> case I could.
> 
> My actual SQL looks roughly like
> 
>    INSERT INTO destination (record_id, page, row)
>    SELECT
>        (SELECT record_id FROM record ORDERED BY name),
>        (NEXTVAL('seq') / 200),
>        (CURRVAL('seq') % 200)
> 
> While I have a workaround, I am definitely curious as to whether there 
> is actually a way to do it. Thanks for your patience.

I think the following will do what you want:
   INSERT INTO destination (record_id, page, row)   SELECT record_id, seq/200, seq%200 FROM       (SELECT record_id,
nextval('seq')as seq FROM record ORDERED BY name);