X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CF85BD1E12C for ; Fri, 27 Feb 2004 13:44:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 69241-03 for ; Fri, 27 Feb 2004 09:44:31 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 5D99DD1E0EB for ; Fri, 27 Feb 2004 09:44:27 -0400 (AST) Received: (qmail 5117 invoked by uid 500); 27 Feb 2004 13:46:53 -0000 Date: Fri, 27 Feb 2004 07:46:53 -0600 From: Bruno Wolff III To: Martin Langhoff Cc: PostgreSQL Bugs List , pgsql-sql@postgresql.org Subject: Re: BUG #1083: Insert query reordering interacts badly with Message-ID: <20040227134653.GA13683@wolff.to> Mail-Followup-To: pgsql-sql@postgresql.org References: <20040224012657.B86E5CF4A21@www.postgresql.com> <18400.1077601067@sss.pgh.pa.us> <403AF354.5030901@catalyst.net.nz> <18920.1077605515@sss.pgh.pa.us> <403B2BD4.1060908@catalyst.net.nz> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <403B2BD4.1060908@catalyst.net.nz> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/154 X-Sequence-Number: 7942 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 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);