Skip site navigation (1) Skip section navigation (2)

Re: BUG #6172: DROP EXTENSION error without CASCADE

From: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr>, "Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6172: DROP EXTENSION error without CASCADE
Date: 2011-08-22 13:25:57
Message-ID: 878vqla87u.fsf@hi-media-techno.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-08-22 13:27:03
Subject: Re: BUG #6171: Sockets Issue
Previous:From: Dimitri FontaineDate: 2011-08-22 12:32:18
Subject: Re: BUG #6171: Sockets Issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group