Re: Delete all records NOT referenced by Foreign Keys

On Sun, Dec 14, 2003 at 03:02:49AM -0600, D. Dante Lorenso 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.

Ah, I see. There is no counter. When you delete a row, it does a check on
the referencing table to see if it would break any foreign keys. The system
has a defined trigger for that purpose. If you don't want to have the
trigger error out, you do the same test. That's what my query did.

In fact, you'll be able to do it more efficiently, since you know you'll be
deleting many rows, you can arrange to only scan the referencing table once.

The table with the foreign keys in it would be pg_constraint I think. You
can use -E on psql to see you \d gets the info.

> This will loop through the records one at a time and try to delete them.
> However, I don't want to have any exceptions thrown if the DELETE action
> can not be performed. Is there a TRY/CATCH type of code that I can
> surround the DELETE with to prevent the entire operation from being
> aborted on the first error found?

That would be subtransactions, and they're not done yet.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce