Handling foreign_key_violation in plpgsql

Hi list,

I have a table which is referenced with foreign keys from multiple other
tables. Records in this table are deleted by on delete rules on those other
tables. This means that if I want to drop one of the referencing tables, I
first have to delete all records in that referencing table to ensure that no
stale records are left in the referenced tables. (DROP will, of course, not
fire the on delete rules.)

However, due to another "bug" which I'm still investigating, deleting all
records in a table and then dropping the table within one transaction block
will simply fail. For this reason and for the reason of sometimes simply
_forgetting_ to create (or fire) an on delete rule, I decided to write a
simple plpgsql function to delete stale records in the referenced table.

If I make a mock database with a few simple referencing tables and one
referenced table, such a function works fine, but in the actual DB where I
need the function, similar exception handling code will not be reached, even
though I'm checking for the right error condition (foreign_key_violation).
As such, the function will fail with the very error code that I'm catching.

What is so confusing to me is that I've not been able to reproduce this with
a simplified mock database.

Hoping for a hit of the clue bat,

Rowan

--
Morality is usually taught by the immoral.