Re: [HACKERS] comment/security label for publication/subscription

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