drop operator class..using .. left dependency behind.

Started by Rajkumar Raghuwanshiover 8 years ago2 messages
#1Rajkumar Raghuwanshi
rajkumar.raghuwanshi@enterprisedb.com

Hi,

I have observed that even after dropping operator class, not able to drop
schema containing it. below is a example.

postgres=# CREATE SCHEMA sch_test;
CREATE SCHEMA
postgres=# SET search_path TO 'sch_test';
SET
postgres=# CREATE OR REPLACE FUNCTION sch_test.dummy_hashint4_39779(a int4)
RETURNS int4 AS $$ BEGIN RETURN a; END; $$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE FUNCTION
postgres=# CREATE OPERATOR CLASS sch_test.custom_opclass_test FOR TYPE int4
USING HASH AS OPERATOR 1 = , FUNCTION 1 sch_test.dummy_hashint4_39779(a
int4);
CREATE OPERATOR CLASS

*postgres=# DROP OPERATOR CLASS sch_test.custom_opclass_test USING
HASH;DROP OPERATOR CLASS*
postgres=# DROP FUNCTION sch_test.dummy_hashint4_39779(a int4);
DROP FUNCTION
postgres=# RESET search_path;
RESET

*postgres=# DROP SCHEMA sch_test;ERROR: cannot drop schema sch_test
because other objects depend on itDETAIL: operator family
sch_test.custom_opclass_test for access method hash depends on schema
sch_testHINT: Use DROP ... CASCADE to drop the dependent objects too.*
when investigated found, entry still present in pg_opfamily.

postgres=# select * from pg_opfamily where opfname like
'custom_opclass_test%';
opfmethod | opfname | opfnamespace | opfowner
-----------+---------------------+--------------+----------
405 | custom_opclass_test | 16409 | 10
(1 row)

Is this expected behaviour??

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Rajkumar Raghuwanshi (#1)
Re: drop operator class..using .. left dependency behind.

Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> writes:

I have observed that even after dropping operator class, not able to drop
schema containing it. below is a example.

You didn't read the error message closely:

*postgres=# DROP SCHEMA sch_test;ERROR: cannot drop schema sch_test
because other objects depend on itDETAIL: operator family
sch_test.custom_opclass_test for access method hash depends on schema
sch_testHINT: Use DROP ... CASCADE to drop the dependent objects too.*

The operator class is gone, but the operator family that contained it
still exists. CREATE OPERATOR CLASS will create a containing family
if you don't specify one, but DROP OPERATOR CLASS won't automatically
remove such a family. (If it did, it might destroy other operator
classes that had been added to that family later.)

Probably the easiest answer is to use DROP OPERATOR FAMILY instead.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers