> On Aug 9, 2026, at 01:35, Andres Freund <[email protected]> wrote: > > Hi, > > On 2017-03-24 00:18:26 -0400, Peter Eisentraut wrote: >> Here is a patch to add COMMENT support for publications and subscriptions. >> >> On a similar issue, do we need SECURITY LABEL support for those? Does >> that make sense? > > It looks like this was committed (87dee41f3ed). > > Unfortunately I found, during an investigation of something completely > independent, that it leads to comments and (and presumably security labels) to > be orphaned on DROP. > > In fact, our regression database actually contains such an orphaned comment: > > regression[1536656][1]=# SELECT * FROM pg_description WHERE classoid = > 'pg_subscription'::regclass; > ┌────────┬──────────┬──────────┬───────────────────┐ > │ objoid │ classoid │ objsubid │ description │ > ├────────┼──────────┼──────────┼───────────────────┤ > │ 123718 │ 6100 │ 0 │ test subscription │ > └────────┴──────────┴──────────┴───────────────────┘ > (1 row) > > Seems we need to beef up oidjoins.sql to find orphaned objects. > > > I can't entirely blame this commit, it seems pretty cruddy that the drop > routine of every global object needs to have a synchronized copy of various > Delete* routines. It's bad enough that drop functions for global objects need > to know about having to drop dependencies manually, but copying the set of > objects that need to be dropped in each seems like a bad idea. > > > Trivial repro: > > DROP SUBSCRIPTION IF EXISTS s; > > CREATE SUBSCRIPTION s CONNECTION '' PUBLICATION p > WITH (connect = false, slot_name = NONE); > COMMENT ON SUBSCRIPTION s IS 'leaked'; > DROP SUBSCRIPTION s; > > SELECT * FROM pg_description WHERE classoid = 'pg_subscription'::regclass; > > Which will show something like: > ┌────────┬──────────┬──────────┬─────────────┐ > │ objoid │ classoid │ objsubid │ description │ > ├────────┼──────────┼──────────┼─────────────┤ > │ 116868 │ 6100 │ 0 │ leaked │ > └────────┴──────────┴──────────┴─────────────┘ > > > Greetings, > > Andres Freund >
I just debugged the code. Dropping a publication uses the generic
deleteOneObject() path, so that comment, security label etc. dependencies are
deleted automatically. While for some reason, doDeletion() explicitly reject
subscription:
```
/*
* These global object types are not supported here.
*/
case AuthIdRelationId:
case DatabaseRelationId:
case TableSpaceRelationId:
case SubscriptionRelationId:
case ParameterAclRelationId:
elog(ERROR, "global objects cannot be deleted by
doDeletion");
break;
```
Therefore, DropSubscription() has to perform the cleanup explicitly. I guess
that is why commit 87dee41f3ed missed adding the deletion of comments and
security labels to DropSubscription().
I have prepared a patch to fix the bug. The fix is straightforward, and I have
added tests for both comments and security labels.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
v1-0001-Remove-comments-and-security-labels-when-dropping.patch
Description: Binary data
