Re: DROP FUNCTION failure: cache lookup failed for relation X

I wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>> I've found a situation that causes DROP FUNCTION to fail (tested
>> in 8.1.6, 8.2.1, and 8.3devel):
>> http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php

> Ugh ... I haven't traced this through in detail, but I'm pretty sure
> the problem arises from the fact that dependency.c traces through
> auto/internal dependencies before actually starting to do the deletions
> (and thus before acquiring locks).

I looked into this a bit more, and found that it's actually a pretty
general issue with the dependency.c code: we delete objects depending
on a target object before we delete the target itself. Which is fine,
except that we don't try to take out any lock on the target object until
we get to the object-type-specific subroutine that's supposed to delete
it.

I think we could fix this for tables by acquiring lock on a table at the
instant it's put into a list for deletion inside dependency.c. That
would be enough to fix Michael's problem instance, but what of other
types of objects? There doesn't seem to be anything preventing somebody
from, say, deleting a function at the same time someone else is creating
an operator depending on the function. We mostly don't take locks on
non-table objects while working with them, and for the most part this is
fairly sane because those objects are defined by a single system catalog
row anyway: either you see the row or you don't. But this means that
the depended-on object could be gone by the time you finish adding a
dependency on it.

It seems a general solution would involve having dependency.c take
exclusive locks on all types of objects (not only tables) as it scans
them and decides they need to be deleted later. And when adding a
pg_depend entry, we'd need to take a shared lock and then recheck to
make sure the object still exists. This would be localized in
dependency.c, but it still seems like quite a lot of mechanism and
cycles added to every DDL operation. And I'm not at all sure that
we'd not be opening ourselves up to deadlock problems.

I'm a bit tempted to fix only the table case and leave the handling of
non-table objects as is. Comments?

regards, tom lane