How to temporarily disable all constraints of a schema in postgresql?-CodePudding

I need to temporarily disable all constraints and triggers of a schema in order to anonymize data in several tables. As there are dependencies between tables, I prefer to disable everything and once the anonymization treatment is over I can enable all constraints and triggers one more time.

I tried SET FOREIGN_KEY_CHECKS=0; and I got this error:

ERROR: unrecognized configuration parameter "foreign_key_checks" SQL state: 42704

I've been reading a lot about that and some people say this is not possible.

Do you know a way to do that?

Thank you!

CodePudding user response：

To disable foreign keys and deferrable unique and primary key constraints for a table, you can use

ALTER TABLE ... DISABLE TRIGGER ALL;

To disable all such constraints for the duration of a database session, you can

SET session_replication_role = replica;

Both tricks won't work for non-deferrable constraints and check constraints.

CodePudding user response：

I found this solution,

I created a temporal table to keep all constraints definition:

CREATE TEMPORARY TABLE temp_constraints AS
SELECT conname constraintname, conrelid::regclass tablename, pg_get_constraintdef(oid) definition, contype 
FROM pg_catalog.pg_constraint;

Then I drop all constraints:

DO $$
DECLARE constraint_name TEXT;
DECLARE constraint_table TEXT;
BEGIN
    FOR constraint_name, constraint_table IN
    SELECT constraintname ,  tablename FROM temp_constraints ORDER BY contype DESC
        LOOP
            EXECUTE 'ALTER TABLE ' || constraint_table || ' DROP CONSTRAINT IF EXISTS ' || constraint_name || ' CASCADE;';
        END LOOP;
END $$;

And after anonymizing data I restore all constraints using the definitions in the temporal table and I drop the temporal table:

DO $$
DECLARE constraint_table TEXT;
DECLARE constraint_definition TEXT;
BEGIN
    FOR constraint_table, constraint_definition IN 
    SELECT tablename, definition FROM temp_constraints ORDER BY contype DESC
        LOOP
            EXECUTE 'ALTER TABLE ' || constraint_table || ' ADD ' || constraint_definition || ';';
        END LOOP;
    DROP TABLE IF EXISTS temp_constraints;
END $$;

I hope this can help someone else. Thank you!