Lists: | pgsql-bugspgsql-sql |
---|
From: | "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() |
Date: | 2004-02-24 01:26:57 |
Message-ID: | 20040224012657.B86E5CF4A21@www.postgresql.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-sql |
The following bug has been logged online:
Bug reference: 1083
Logged by: Martin Langhoff
Email address: martin(at)catalyst(dot)net(dot)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
From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() |
Date: | 2004-02-24 02:11:17 |
Message-ID: | 20040224021117.GA29715@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-sql |
On Mon, Feb 23, 2004 at 21:26:57 -0400,
PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)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.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | martin(at)catalyst(dot)net(dot)nz |
Cc: | "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() |
Date: | 2004-02-24 05:37:47 |
Message-ID: | 18400.1077601067@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-sql |
"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)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
From: | "Martin Langhoff (Catalyst IT)" <martin(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #1083: Insert query reordering interacts badly with |
Date: | 2004-02-24 06:46:44 |
Message-ID: | 403AF354.5030901@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-sql |
Tom Lane wrote:
>"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)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
-----------------------------------------------------------------------
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Martin Langhoff (Catalyst IT)" <martin(at)catalyst(dot)net(dot)nz> |
Cc: | PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() |
Date: | 2004-02-24 06:51:55 |
Message-ID: | 18920.1077605515@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-sql |
"Martin Langhoff (Catalyst IT)" <martin(at)catalyst(dot)net(dot)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
From: | Martin Langhoff <martin(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #1083: Insert query reordering interacts badly with |
Date: | 2004-02-24 10:47:48 |
Message-ID: | 403B2BD4.1060908@catalyst.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-sql |
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
-----------------------------------------------------------------------
From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Martin Langhoff <martin(at)catalyst(dot)net(dot)nz> |
Cc: | PostgreSQL Bugs List <pgsql-bugs(at)postgresql(dot)org>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: BUG #1083: Insert query reordering interacts badly with |
Date: | 2004-02-27 13:46:53 |
Message-ID: | 20040227134653.GA13683@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs pgsql-sql |
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(at)catalyst(dot)net(dot)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);