On Sat, Jul 25, 2009 at 8:45 AM, Sam Mason<sam@samason.me.uk> 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