Re: BUG #6172: DROP EXTENSION error without CASCADE

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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