Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation X
Added to TODO: * Increase locking when DROPing objects so dependent objects cannot get dropped while the DROP operation is happening http://archives.postgresql.org/pgsql-hackers/2007-01/msg00937.php --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Uh, where are we on this? Still in the think-about-it mode, personally ... my proposed fix is certainly much too invasive to consider back-patching, so unless someone comes up with a way-simpler idea, it's 8.3 material at best ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation X
Uh, where are we on this? --- Tom Lane wrote: I wrote: Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation X
Bruce Momjian [EMAIL PROTECTED] writes: Uh, where are we on this? Still in the think-about-it mode, personally ... my proposed fix is certainly much too invasive to consider back-patching, so unless someone comes up with a way-simpler idea, it's 8.3 material at best ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation X
I wrote: Michael Fuhr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DROP FUNCTION failure: cache lookup failed for relation X
Michael Fuhr [EMAIL PROTECTED] writes: I've found a situation that causes DROP FUNCTION to fail (tested in 8.1.6, 8.2.1, and 8.3devel): 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). Can we fix this without multiple scans of the dependency tree (probably costing O(N^lots))? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq