Forum OpenACS Q&A: Disable constraints in PG

Collapse
1: Disable constraints in PG
Posted by Koroush Iranpour on 10/23/02 07:02 PM
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
2: Response to Disable constraints in PG (response to 1)
Posted by Koroush Iranpour on 10/23/02 07:51 PM
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
3: Response to Disable constraints in PG (response to 1)
Posted by Jonathan Ellis on 10/23/02 08:23 PM
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
4: Response to Disable constraints in PG (response to 1)
Posted by Koroush Iranpour on 10/24/02 05:15 PM
It can certainly be very useful. Thank you.