Thread: objects tied to missing extension

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.

What happened was that I was able to delete the extension in the
upgrade script, either by dropping a table with cascade or by outright
dropping the extension. It's debatable whether or not that should be
allowed, but what happens afterward is what I am more concerned about.
In the same upgrade script, after dropping the extension, I created
another table and now it is tied to an extension that no longer
exists. The output of my psql session is below and the extension files
are attached.

postgres=# select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.6.0 20110603 (Red Hat 4.6.0-10), 64-bit
(1 row)

postgres=# create database bug_example;
CREATE DATABASE
postgres=# \c bug_example
You are now connected to database "bug_example" as user "postgres".
bug_example=# create extension bug_example with version '1.0';
CREATE EXTENSION
bug_example=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | table_a        | table    | postgres
 public | table_a_id_seq | sequence | postgres
(2 rows)

bug_example=# alter extension bug_example update to '2.0';
ALTER EXTENSION
bug_example=# \d
               List of relations
 Schema |      Name      |   Type   |  Owner
--------+----------------+----------+----------
 public | table_b        | table    | postgres
 public | table_b_id_seq | sequence | postgres
(2 rows)

bug_example=# drop extension bug_example;
ERROR:  extension "bug_example" does not exist
bug_example=# drop table table_b;
ERROR:  cache lookup failed for extension 17439
bug_example=# drop table table_b cascade;
ERROR:  cache lookup failed for extension 17439

Phil Sorber <phil@omniti.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

On Mon, Nov 28, 2011 at 3:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Phil Sorber <phil@omniti.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. =A0(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. =A0So the decision at step 3 is not
> wrong for such cases. =A0It 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. =A0(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.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, 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.

Phil Sorber <phil@omniti.com> writes:
> 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 you have some dangling entries in pg_depend --- manually
deleting any rows that reference the missing pg_extension OID should fix
it.

            regards, tom lane

On Mon, Nov 28, 2011 at 4:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Phil Sorber <phil@omniti.com> writes:
>> I am still able to reproduce the "ERROR: =A0cache lookup failed for exte=
nsion
>> 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 you have some dangling entries in pg_depend --- manually
> deleting any rows that reference the missing pg_extension OID should fix
> it.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>

That fixes it. At least I have a workaround for current installs.

Thanks.

Phil Sorber <phil@omniti.com> writes:
> 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.

My initial reaction was that this wouldn't be worth the trouble, but
on reflection it occurred to me that the case can still be produced in
HEAD without anything as obviously bogus as dropping the script's own
extension.  For instance, suppose the upgrade script in your example
attempts to add the rowtype to the extension explicitly:

    alter extension bug_example add type table_a;
    alter extension bug_example drop table table_a;
    alter extension bug_example drop sequence table_a_id_seq;

    drop table table_a cascade;

    create table table_b ( id serial, primary key (id) );

With this script, the failure naturally will occur just the same way
in HEAD, since the extension will get dropped due to CASCADE and then
the CREATE TABLE step will add a pg_depend entry referencing the
already-dropped extension.

This is still a bit improbable, but it demonstrates that non-obvious
mistakes in dependency management could lead to the situation happening,
so it convinces me that it's worth expending a little bit of code to
prevent it.  Fortunately it's quite trivial to do: we can just make
RemoveExtensionById refuse to delete the extension if its OID is equal
to the CurrentExtensionObject state variable.  Committed at
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=871dd024a6adf7766702b1cdacfb02bd8002d2bb

            regards, tom lane