Thread

Bug on drop extension dependencies ?

Marcos Pegoraro <marcos@f10.com.br> — 2025-07-12T18:30:38Z

Working with several schemas and the same structure, I created an extension
on one of those schemas, and it worked fine for all schemas. But then I
don't need that schema anymore, and when I drop it with cascade, it drops
that extension too, and doing that it drops all constraints related to it.

So, should there be an exception when I try to drop that schema ?

mydb=# create schema ten_1;
CREATE SCHEMA
mydb=# set search_path to ten_1;
SET
mydb=# create extension btree_gist;
CREATE EXTENSION
--Extension was created on ten_1 schema.

mydb=# create table ten_1.mytable(pk integer generated always as identity,
id integer, myrange tstzrange, constraint rangeexclude EXCLUDE USING gist
(id WITH =, myrange WITH &&));
CREATE TABLE
mydb=# insert into ten_1.mytable(id, myrange)
values(1,'[2025/01/01,2025/02/01)'),(1,'[2025/01/10,2025/02/20)');
ERROR: conflicting key value violates exclusion constraint "rangeexclude"
DETAIL: Key (id, myrange)=(1, ["2025-01-10 00:00:00-03","2025-02-20
00:00:00-03")) conflicts with existing key (id, myrange)=(1, ["2025-01-01
00:00:00-03","2025-02-01 00:00:00-03")).

--As expected gives me an error
--Then I create another schema with same tables

mydb=# create schema ten_2;
CREATE SCHEMA
mydb=# create table ten_2.mytable(pk integer generated always as identity,
id integer, myrange tstzrange, constraint rangeexclude EXCLUDE USING gist
(id WITH =, myrange WITH &&));
CREATE TABLE
mydb=# insert into ten_2.mytable(id, myrange)
values(1,'[2025/01/01,2025/02/01)'),(1,'[2025/01/10,2025/02/20)');
ERROR: conflicting key value violates exclusion constraint "rangeexclude"
DETAIL: Key (id, myrange)=(1, ["2025-01-10 00:00:00-03","2025-02-20
00:00:00-03")) conflicts with existing key (id, myrange)=(1, ["2025-01-01
00:00:00-03","2025-02-01 00:00:00-03")).

--Fine, as expected too.
--Those two constraints are fine.

mydb=# select oid, conname, conrelid::regclass from pg_constraint where
conrelid in (select oid from pg_class where relname = 'mytable');
-[ RECORD 1 ]-----------
oid      | 135979570
conname  | rangeexclude
conrelid | ten_2.mytable
-[ RECORD 2 ]-----------
oid      | 135979562
conname  | rangeexclude
conrelid | ten_1.mytable

--But then I didn't want that ten_1 anymore, so I droped it.
--If I use client_min_messages = notice I'll receive a hint that the
exception is being dropped. But I didn't read that because I want to drop
everything on that schema, but only on that schema.

mydb=# set client_min_messages = warning;
SET
mydb=# drop schema ten_1 cascade;
DROP SCHEMA

--Ten_2 is still there, but not exactly the same

mydb=# insert into ten_2.mytable(id, myrange)
values(1,'[2025/01/01,2025/02/01)'),(1,'[2025/01/10,2025/02/20)');
INSERT 0 2

--Records were inserted
--And those two constraints were removed because my extension was removed.

So, dropping a schema could drop constraints on dozen or hundreds of other
schemas, I think this should raise an exception.

I don't know how to fix it, but I think it's a bug.

regards
Marcos