Delete all records NOT referenced by Foreign Keys

[D. Dante Lorenso]

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_B Y_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.

Thanks,

Dante

---------
D. Dante Lorenso
da***@lorenso.c om


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

On Sun, 14 Dec 2003, D. Dante Lorenso wrote:

Stephan Szabo wrote:

My hope was that there was some sort of (semaphore? / counter?) associated
with each row that indicated whether a dependency existed at all. Although
that would most likely not be an indexed column, I could apply additional
WHERE constraints to avoid a full table scan.

If such a counter existed, it would certainly speed up deletes when no
foreign key checks were necessary. But I suppose it would also slow
down deletes when it becomes necessary to decrement a counter for the
dependencies created by the row being deleted.
More importantly, unless you can defer the counter updates, that means
taking out a write lock on the pk row in even more situations than now
which makes things even more deadlock prone. If you do defer the updates
in some way that removes that likelihood, you need a way to store the
local changes to the counters.
So...internally , how does a row KNOW that it can not be deleted because of
a foreign key constraint? Whatever that mechanism is, isn't there a way for
me to make use of that as I try my query?

It's a bunch of triggers each doing their own statements in C. The row
has no knowledge of it, only the triggers do.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html