Re: 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.
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 <postgres(at)2xlp(dot)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(at)postgresql(dot)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.