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
