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.
