Re: Delete all records NOT referenced by Foreign Keys

Bruno Wolff III wrote:

>On Sat, Dec 13, 2003 at 02:20:15 -0600,
> "D. Dante Lorenso" <dante(at)lorenso(dot)com> wrote:
>
>
>>I'd like to run a clean up command on my tables to
>>eliminate rows that I'm no longer using in the database.
>>
>>I want to do something like this:
>>
>> DELETE FROM tablename
>> WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE;
>>
>>Does anyone know how something like this could be done
>>in PostgreSQL? I know I can search all the tables that
>>I know refer to this table and see if my primary key
>>exists, but I want a solution that does not require me to
>>rewrite my code every time a new foreign key constraint
>>is added to the database.
>>
>>There must be a way to ask PostgreSQL for a reference count
>>on a given row or something.
>>
>>
>
>If you are more concerned about flexibility than speed you can do something
>like the following:
>
>Set all of your foreign key references to the desired table to use an
>on delete restrict clause.
>
>Have your application read all of the key values from the desired table
>and for each key issue a delete of that key in its own transaction.
>This will fail for keys that are referenced (because of the restrict clause).
>
>
This is something very ugly indeed and is what I'll have to resort to unless
I can find something cleaner. Ideally, I would be able to run this cleanup
on a subset of the table data after an insert into the table. I would like
the query to be fast, though.

Does anyone know if there is any way to say something like:

DELETE FROM tablename
IGNORE ERRORS;

Where a delete that is possible is performed but ones that throw referencial
integrity voilations would silently fail without abandoning the entire
transaction?

I have the 'on delete restrict' clause on my foreign keys already.

>A more complicated, less future proof, but more efficient approach would
>be to have your application find out which tables have references to the
>table of interest by looking at the system catalog and then write a
>delete query using appropiate where not exist clauses.
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>