Re: Feature Request: Better handling of foreign keys in DELETE statements

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Feature Request: Better handling of foreign keys in DELETE statements
Date: 2011-12-19 16:14:31
Message-ID: CAFj8pRBMz3NBme5Kg10HzsTxWxQtBgHykfW-y2Qoz129c_0b3g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello

2011/12/19 Daniel Migowski <dmigowski(at)ikoffice(dot)de>:
> Hi,
>
>
>
> I face the following problem: I have a large table with 12 million
> addresses, referenced by 20 other tables (some containing about one million
> entries). There are indexes on the foreign keys.
>
>
>
> Now I wanted to delete about 10 million addresses (that are not referenced
> anymore from anywhere), and have a statement like:
>

you can disable check per session if you need

ALTER TABLE ... DISABLE TRIGGER ALL;

Regards

Pavel Stehule

>
>
> DELETE FROM address
>
> WHERE id NOT IN (SELECT address_id FROM bank where address_id IS NOT NULL)
>
>    AND id NOT IN (SELECT poboxaddress_id FROM bank where poboxaddress_id IS
> NOT NULL)
>
>    AND id NOT IN (SELECT address_id FROM bankconnection where address_id IS
> NOT NULL)
>
> ...lots more...
>
>
>
> This takes more than 10 hours here (I had to cancel the statement).
>
>
>
> I have two suggestions:
>
>
>
> 1.       Currently for each row to be deleted, a SELECT is done in each
> column referencing the deleted entry. This takes really a lot of time. It is
> possible to check in an elegant way if an entry can be deleted, like in the
> above query.  I know it is not easy to autocreate such a statement, but this
> would make deletions much faster.
>
> 2.       I would have loved a special option “UNREREFENCED” given to the
> delete statement, so all rows referenced from anywhere would automagically
> be excluded from my delete statement. When this keyword is given, no FK
> checks have to be done, because FK referenciality cannot be violated anyway.
>
>
>
> DELETE UNREFERENCED FROM address WHERE …;
>
>
>
> Thanks for your time and this great database product.
>
>
>
> Regards,
>
> Daniel Migowski

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2011-12-19 16:18:02 Re: Changing Passwords as Encrypted not Clear-Text
Previous Message Chris Angelico 2011-12-19 16:11:11 Re: Feature Request: Better handling of foreign keys in DELETE statements