PostgreSQL - disabling constraints

Solution 1:

Per previous comments, it should be a problem. That said, there is a command that may be what you're looking to - it'll set the constraints to deferred so they're checked on COMMIT, not on every delete. If you're doing just one big DELETE of all the rows, it won't make a difference, but if you're doing it in pieces, it will.

SET CONSTRAINTS ALL DEFERRED

is what you are looking for in that case. Note that constraints must be marked as DEFERRABLE before they can be deferred. For example:

ALTER TABLE table_name
  ADD CONSTRAINT constraint_uk UNIQUE(column_1, column_2)
  DEFERRABLE INITIALLY IMMEDIATE;

The constraint can then be deferred in a transaction or function as follows:

CREATE OR REPLACE FUNCTION f() RETURNS void AS
$BODY$
BEGIN
  SET CONSTRAINTS ALL DEFERRED;

  -- Code that temporarily violates the constraint...
  -- UPDATE table_name ...
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Solution 2:

What worked for me was to disable one by one the TRIGGERS of those tables that are gonna be involved in the DELETE operation.

ALTER TABLE reference DISABLE TRIGGER ALL;
DELETE FROM reference WHERE refered_id > 1;
ALTER TABLE reference ENABLE TRIGGER ALL;

Solution is working in version 9.3.16. In my case time went from 45 minutes to 14 seconds executing DELETE operations.

As stated in the comments section by @amphetamachine, you will need to have admin privileges to the tables to perform this task.

Solution 3:

If you try DISABLE TRIGGER ALL and get an error like permission denied: "RI_ConstraintTrigger_a_16428" is a system trigger (I got this on Amazon RDS), try this:

set session_replication_role to replica;

If this succeeds, all triggers that underlie table constraints will be disabled. Now it's up to you to make sure your changes leave the DB in a consistent state!

Then when you are done, reenable triggers & constraints for your session with:

set session_replication_role to default;