pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL()
7+ messages / 5 participants
[nested] [flat]
* BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL()
@ 2004-02-24 01:26 PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
2004-02-24 02:11 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Bruno Wolff III <bruno@wolff.to>
2004-02-24 05:37 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: PostgreSQL Bugs List @ 2004-02-24 01:26 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL()
2004-02-24 01:26 BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
@ 2004-02-24 02:11 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2004-02-24 02:11 UTC (permalink / raw)
To: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL()
2004-02-24 01:26 BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
@ 2004-02-24 05:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 06:46 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff (Catalyst IT) <martin@catalyst.net.nz>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2004-02-24 05:37 UTC (permalink / raw)
To: martin@catalyst.net.nz; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #1083: Insert query reordering interacts badly with
2004-02-24 01:26 BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
2004-02-24 05:37 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-24 06:46 ` Martin Langhoff (Catalyst IT) <martin@catalyst.net.nz>
2004-02-24 06:51 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Langhoff (Catalyst IT) @ 2004-02-24 06:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
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
-----------------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL()
2004-02-24 01:26 BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
2004-02-24 05:37 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 06:46 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff (Catalyst IT) <martin@catalyst.net.nz>
@ 2004-02-24 06:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 10:47 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff <martin@catalyst.net.nz>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2004-02-24 06:51 UTC (permalink / raw)
To: Martin Langhoff (Catalyst IT) <martin@catalyst.net.nz>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #1083: Insert query reordering interacts badly with
2004-02-24 01:26 BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
2004-02-24 05:37 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 06:46 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff (Catalyst IT) <martin@catalyst.net.nz>
2004-02-24 06:51 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-24 10:47 ` Martin Langhoff <martin@catalyst.net.nz>
2004-02-27 13:46 ` Re: BUG #1083: Insert query reordering interacts badly with Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Langhoff @ 2004-02-24 10:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
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
-----------------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #1083: Insert query reordering interacts badly with
2004-02-24 01:26 BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
2004-02-24 05:37 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 06:46 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff (Catalyst IT) <martin@catalyst.net.nz>
2004-02-24 06:51 ` Re: BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 10:47 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff <martin@catalyst.net.nz>
@ 2004-02-27 13:46 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2004-02-27 13:46 UTC (permalink / raw)
To: Martin Langhoff <martin@catalyst.net.nz>; +Cc: PostgreSQL Bugs List <pgsql-bugs@postgresql.org>; 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@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);
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-02-27 13:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-24 01:26 BUG #1083: Insert query reordering interacts badly with NEXTVAL()/CURRVAL() PostgreSQL Bugs List <pgsql-bugs@postgresql.org>
2004-02-24 02:11 ` Bruno Wolff III <bruno@wolff.to>
2004-02-24 05:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 06:46 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff (Catalyst IT) <martin@catalyst.net.nz>
2004-02-24 06:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-24 10:47 ` Re: BUG #1083: Insert query reordering interacts badly with Martin Langhoff <martin@catalyst.net.nz>
2004-02-27 13:46 ` Re: BUG #1083: Insert query reordering interacts badly with Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox