Re: Random resultset retrieving -> performance bottleneck

On Thu, 1 Aug 2002, [iso-8859-1] Cdric 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).