Re: objects tied to missing extension

On Mon, Nov 28, 2011 at 3:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Phil Sorber <phil(at)omniti(dot)com> writes:
>> I stumbled upon this situation when playing with extension upgrades.
>> The problem I was having was that auto-generated datatypes were also
>> being added to the extension and it wasn't obvious this was happening.
>> I know this has been changed in 9.1 stable and in master.
>
> I couldn't replicate any funnies with the given test case in 9.1 branch
> tip.  (It might not work nicely if you change the upgrade script to do
> DROP EXTENSION, but I cannot imagine any sane reason to do that ... and
> we are assuming that extension script authors are responsible adults,
> since the scripts are generally executed with superuser permissions.)
>
> After poking in the code for awhile, I believe that the reason you had a
> problem when the table's rowtype is an extension member is that the
> deletion proceeds like this:
>
> 1. We start at table table_a, which is a legitimate drop request.
> 2. We recurse to its internal dependency, the rowtype table_a, and
> decide that that's legitimate to drop too.
> 3. Recursing again, findDependentObjects finds the extension, and since
> it's not at the outermost recursion level, decides that it ought to
> proceed with deleting the extension.
>
> The reason for this behavior is that we want to support deletion of
> dependent extensions --- that is, if some object in extension A depends
> on some object in extension B, and extension B is dropped with CASCADE,
> then extension A ought to go away too.  So the decision at step 3 is not
> wrong for such cases.  It might be that there's some corner case where
> we need to tighten the rules, but AFAICS it's safe as long as every
> directly-deletable object that's within an extension has a direct
> dependency on the extension.  (That's enough to ensure that a DROP on
> the object will encounter the extension at outermost recursion level.)
> So the problem seems to be only due to your ALTER EXTENSION DROP command
> having left an incomplete set of extension dependencies behind.
>
>                        regards, tom lane
>

I compiled 9.1 stable head and tested it out. You are correct my
example no longer works there because of the patch that stopped the
auto-generated types from becoming dependencies of the extension. In
fact, the cascade no longer works even if I don't remove the table or
sequence from the extension. And I agree with your assertions here
that allowing the extension authors to be adults is fine. However, I
don't think leaving the database in a bad state is acceptable. I am
still able to reproduce the "ERROR: cache lookup failed for extension
xxxxx" if I use an explicit 'drop extension'. I am unsure how I can
reverse the state it is now in. I assume there is some system catalog
I can edit that will fix it? I think anything created after the
extension is dropped should be not linked to it, or not created or
maybe have the whole thing fail altogether.