Re: Very slow joins

On Sat, Jul 25, 2009 at 02:36:19AM -0700, MS wrote:
> I believe the update took so long because pgsql was checking if the
> changes don't break the referential integrity.
> So - problem solved, postgres good. ;) But isn't there a way to make
> some bulk operations without having to drop indexes/FKs?

I've never had the need to use this, but I believe this works using the
"SET CONSTRAINTS" command[1]; e.g. I can do:

CREATE TABLE foo ( id INTEGER PRIMARY KEY );
CREATE TABLE bar ( id INTEGER REFERENCES foo DEFERRABLE );

INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1);

the following will now fail:

BEGIN;
INSERT INTO bar VALUES (2);
INSERT INTO foo VALUES (2);
COMMIT;

but the following is OK:

BEGIN;
SET CONSTRAINTS bar_id_fkey DEFERRED;
INSERT INTO bar VALUES (2);
INSERT INTO foo VALUES (2);
COMMIT;

Unfortunatly only foreign key constraints are affected by this setting,
but I believe there are plans to extend this further.

--
Sam http://samason.me.uk/

http://www.postgresql.org/docs/current/static/sql-set-constraints.html