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.
Then execute the drop_fkeys.sql
ie: \i drop_fkeys.sql
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;
On Thu, Nov 20, 2014 at 5:13 PM, Jonathan Vanasco <postg...@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.
