From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Cédric Dufour <cedric.dufour@freesurf.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: Random resultset retrieving -> performance bottleneck
Date: Fri, 2 Aug 2002 07:12:13 -0700 (PDT)
Message-ID: <20020802070832.Y39893-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <NDBBIFNBODNADCAOFDOAKEIOCDAA.cedric.dufour@freesurf.ch>
On Thu, 1 Aug 2002, [iso-8859-1] Cédric 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 IMPOSSIBLE,
> BECAUSE REFERENTIAL INTEGRITY IS VIOLATED ON FOREIGN KEY 'FK_Large' IN TABLE
> '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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, cedric.dufour@freesurf.ch
Subject: Re: Random resultset retrieving -> performance bottleneck
In-Reply-To: <20020802070832.Y39893-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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