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 i
t.
--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
