Re: [BUGS] BUG #1083: Insert query reordering interacts badly with

#1

Tom Lane wrote:

Quote:

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
-----------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#2

Tom Lane wrote:

Quote:

"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
-----------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#3

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.nz> wrote:

Quote:

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);

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #1083: Insert query reordering interacts badly with

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #1083: Insert query reordering interacts badly with - 02-26-2004 , 07:45 PM

Re: [BUGS] BUG #1083: Insert query reordering interacts badly with - 02-26-2004 , 07:46 PM

Re: [BUGS] BUG #1083: Insert query reordering interacts badly with - 02-27-2004 , 07:51 AM