Re: Delete all records NOT referenced by Foreign Keys

"D. Dante Lorenso" <dante@lorenso.com> writes:

> To NOT have this functionality does not cause problems, but it does cause
> me to waste disk space on rows that are no longer in use.  I just want to
> do some automated cleanup on tables and just leave that process running
> in a crontab nightly or something.  I don't want to have to re-write the
> cleanup process every time a new dependency is introduced or removed.

You could just try to delete every record in the desired tables and see if you
get a foreign key violation. You would have to do each delete in a separate
transaction and just ignore any errors.

so you would have to do something like

foreach table in <list of "auto-cleanup" tables>
 select id from table
 delete from table where id = xx

This would mean your auto-cleanup crontab script doesn't even have to look in
the system catalog to find out the dependencies. It just depends on postgres
knowing all the dependencies and checking them all.

Inevitably though there will be some tables that have some implicit
depenencies that cannot be represented as foreign key references. Or are just
the master records and don't need anything else in the database to depend on
them. So you'll need a list somewhere of tables that are purely subservient to
other tables and can be summarily cleaned up this way.


--
greg