On Sat, Jul 25, 2009 at 8:45 AM, Sam Mason<sam@xxxxxxxxxxxxx> wrote: > 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. You can also disable triggers completely: begin; alter table foo disable trigger all; <do stuff> alter table foo enable trigger all; commit; of course, if you do this the data is never checked at all, so you have to be super careful with it.... merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
