Thread: deferring ForeignKey checks when you didn't set a deferrable constraint ?
Try the following queries. It will give you two .sql files (create_fkeys.sql & drop_fkeys.sql).
First review them to make sure they look ok.Do your deletes, then rebuild your fk's with
\i create_fkeys.sql
Good luck.
==========================================================
-- CREATE STATEMENT FOR ALL FK's
\o create_fkeys.sql
SELECT E'\\timing';
SELECT 'ALTER TABLE ' || n.nspname || '.' || '"' || t.relname || '"'
|| ' ADD CONSTRAINT ' || '"' || c.conname || '"'
|| ' '
|| pg_get_constraintdef( c.oid)
|| ';'
FROM pg_class t
JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f')
JOIN pg_namespace n ON (n.oid = t.relnamespace)
JOIN pg_class f ON (f.oid = c.confrelid)
WHERE c.contype = 'f'
AND t.relkind = 'r'
AND t.relname NOT LIKE 'pg_%'
AND t.relname NOT LIKE 'sql_%'
ORDER BY n.nspname,
t.relname;
-- DROP FK's
\o drop_fkeys.sql
SELECT E'\\timing';
SELECT 'ALTER TABLE ' || n.nspname || '.' || '"' || t.relname || '"'
|| ' DROP CONSTRAINT ' || '"' || c.conname || '"' || ' CASCADE;'
FROM pg_class t
JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f')
JOIN pg_namespace n ON (n.oid = t.relnamespace)
JOIN pg_class f ON (f.oid = c.confrelid)
WHERE c.contype = 'f'
AND t.relkind = 'r'
AND t.relname NOT LIKE 'pg_%'
AND t.relname NOT LIKE 'sql_%'
ORDER BY 1;
-- CREATE STATEMENT FOR ALL FK's
\o create_fkeys.sql
SELECT E'\\timing';
SELECT 'ALTER TABLE ' || n.nspname || '.' || '"' || t.relname || '"'
|| ' ADD CONSTRAINT ' || '"' || c.conname || '"'
|| ' '
|| pg_get_constraintdef( c.oid)
|| ';'
FROM pg_class t
JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f')
JOIN pg_namespace n ON (n.oid = t.relnamespace)
JOIN pg_class f ON (f.oid = c.confrelid)
WHERE c.contype = 'f'
AND t.relkind = 'r'
AND t.relname NOT LIKE 'pg_%'
AND t.relname NOT LIKE 'sql_%'
ORDER BY n.nspname,
t.relname;
-- DROP FK's
\o drop_fkeys.sql
SELECT E'\\timing';
SELECT 'ALTER TABLE ' || n.nspname || '.' || '"' || t.relname || '"'
|| ' DROP CONSTRAINT ' || '"' || c.conname || '"' || ' CASCADE;'
FROM pg_class t
JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f')
JOIN pg_namespace n ON (n.oid = t.relnamespace)
JOIN pg_class f ON (f.oid = c.confrelid)
WHERE c.contype = 'f'
AND t.relkind = 'r'
AND t.relname NOT LIKE 'pg_%'
AND t.relname NOT LIKE 'sql_%'
ORDER BY 1;
On Thu, Nov 20, 2014 at 5:13 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:
I have a core table with tens-of-millions of rows, and need to delete about a million records.
There are 21 foreign key checks against this table. Based on the current performance, it would take a few days to make my deletions.
None of the constraints were defined as `DEFERRABLE INITIALLY IMMEDIATE', so I'm out of luck on deferring them.
Dropping/redefining constraints looks to be an ordeal -- and something I'm scared to make a mistake on.
i looked into disabling triggers on a table, but I couldn't find any info on how to trigger at the end of the transaction so I can ensure integrity.
does anyone have suggestions on things that might work?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.