Re: BUG #6172: DROP EXTENSION error without CASCADE - Mailing list pgsql-bugs

Tom Lane <tgl@sss.pgh.pa.us> writes:
> I'm betting it's got something to do with
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=eb15f26d577a11319b9429fb84f752a0135918db

You're right, once more.  Here's what I understand is happening from
reading the code.  No patch attached, the scope of change I did is not
calling for one.  I include full analysis in case you want to fix it in
another way, I could have missed something important here.


For reference, the error we're tracking begins with:

ERROR:  cannot drop extension cube because other objects depend on it
DETAIL:  operator <>(cube,cube) depends on function cube_ne(cube,cube)


The problem is that the following SQL will create the <> operator as a
Shell Operator then complete its definition later.

  CREATE OPERATOR = (
      LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_eq,
      COMMUTATOR = '=', NEGATOR = '<>',
      RESTRICT = eqsel, JOIN = eqjoinsel,
      MERGES
  );

Here it's quite obvious that the '<>' operator (created as a shell) is
missing the dependency:

~:54320=# select oid, oprname from pg_operator
  where oprleft = 'cube'::regtype and oprright = 'cube'::regtype and oprname in ('=', '<>');
  oid  | oprname
-------+---------
 16421 | <>
 16422 | =
(2 rows)

~:54320=# select * from pg_depend
  where classid = 'pg_operator'::regclass and objid in (16421, 16422);
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
    2617 | 16421 |        0 |       1255 |    16393 |           0 | n
    2617 | 16421 |        0 |       1247 |    16386 |           0 | n
    2617 | 16421 |        0 |       1247 |    16386 |           0 | n
    2617 | 16421 |        0 |       2615 |     2200 |           0 | n

    2617 | 16422 |        0 |       3079 |    16385 |           0 | e
    2617 | 16422 |        0 |       1255 |    16392 |           0 | n
    2617 | 16422 |        0 |       1247 |    16386 |           0 | n
    2617 | 16422 |        0 |       1247 |    16386 |           0 | n
    2617 | 16422 |        0 |       2615 |     2200 |           0 | n
(9 rows)

The code in pg_operator.c records the dependency on the Extension both
for a shell operator (in OperatorShellMake) and for a complete operator,
in OperatorCreate.

But in makeOperatorDependencies() we find the following piece of code:

    /* In case we are updating a shell, delete any existing entries */
    deleteDependencyRecordsFor(myself.classId, myself.objectId, false);

false is for bool skipExtensionDeps.


And now at the end of the same function, dependency is recorded back,
except in some case:

            oldext = getExtensionOfObject(object->classId, object->objectId);
            if (OidIsValid(oldext))
            {
                /* If already a member of this extension, nothing to do */
                if (oldext == CurrentExtensionObject)
                    return;

The problem lies in catalog scans and SnapshotNow, I think.  My fix is
to have deleteDependencyRecordsFor use true for skipExtensionDeps.  Then:

~:54320=# drop extension cube;
DROP EXTENSION

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support