On Sat, Dec 13, 2003 at 02:20:15 -0600,This is something very ugly indeed and is what I'll have to resort to unless
"D. Dante Lorenso" <[EMAIL PROTECTED]> 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).
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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
