Bug on drop extension dependencies ?

From: Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Bug on drop extension dependencies ?
Date: 2025-07-12 18:30:38
Message-ID: CAB-JLwZ76wmnK9jd6ai1HQYCyfrkS5zp425E2+Wc+A1cvjghGQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David E. Wheeler 2025-07-12 18:31:48 Re: ABI Compliance Checker GSoC Project
Previous Message David E. Wheeler 2025-07-12 18:24:25 Re: PATCH: jsonpath string methods: lower, upper, initcap, l/r/btrim, replace, split_part