Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 67A92475425 for ; Fri, 2 Aug 2002 10:12:14 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id AC0124758FE for ; Fri, 2 Aug 2002 10:12:13 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id E861CD610; Fri, 2 Aug 2002 07:12:13 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id DE1475C04; Fri, 2 Aug 2002 07:12:13 -0700 (PDT) Date: Fri, 2 Aug 2002 07:12:13 -0700 (PDT) From: Stephan Szabo To: =?iso-8859-1?Q?C=E9dric_Dufour?= Cc: Subject: Re: Random resultset retrieving -> performance bottleneck In-Reply-To: Message-ID: <20020802070832.Y39893-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/36 X-Sequence-Number: 8882 On Thu, 1 Aug 2002, [iso-8859-1] C=E9dric Dufour wrote: > ***** > * 2. > ***** > BEGIN; > SET CONSTRAINTS ALL DEFERRED; > CREATE TEMP TABLE tmp_Large AS SELECT * FROM tb_Table; > DELETE FROM tb_Large; -- won't work; RI violation on foreign key > 'tb_Foo(FK_Large)' > INSERT INTO tb_Large SELECT * FROM tb_Table ORDER BY Random; > DROP TABLE tmp_Large; > COMMIT; > > -> Would preserve oids, constraints and indexes... BUT DELETE IS IMPOSSIB= LE, > BECAUSE REFERENTIAL INTEGRITY IS VIOLATED ON FOREIGN KEY 'FK_Large' IN TA= BLE > 'tb_Foo', despite the SET CONSTRAINTS ALL DEFERRED clause Yeah, there's been a bug that should now be patched for upcoming 7.3 that caused this to fail. I believe you should be able to find the patch if you search -patches since it was pretty recent. It might take a little work to patch to a previous version, but it shouldn't be too hard. Failing that, you can turn off all triggers (look at the output of a data only pg_dump for queries to turn off/on trigger).