Forum OpenACS Q&A: Disable constraints in PG

Collapse
Posted by Koroush Iranpour on
Hi

I was wondering if there is a way to disable a constraint temporarily
in postges. Any other trick would work as long as I can delete a few
rows. I am trying to delete a few rows from the acs_objects table but
the acs_objects_context_id_fk is giving me a hard time.

Thanks

Collapse
Posted by Koroush Iranpour on
Got it.

Had to first delete composition_rels and membership_rels. Then the rows can be easily deleted.

The object_id of the rows that I wanted to delete were the context_id of a couple of relations. Once those relations are deleted the rest works fine.

Collapse
Posted by Jonathan Ellis on
to answer your original question, you can disable constraints on a table (in 7.2, I don't think you can do this in older versions) with
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'mytable';
and to re-enable
UPDATE pg_class SET reltriggers =
    (SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid)
WHERE relname = 'mytable';
this is NOT how you want to solve your problem of deleting a few rows, but I mention it because it can be useful at other times, e.g. populating tables who each have a FK referencing the other.
Collapse
Posted by Koroush Iranpour on
It can certainly be very useful. Thank you.