Re: tracking owner of extension-managed objects

On 12/23/15 15:02, Jim Nasby wrote:
> BTW, I've been pondering a very similar problem to this. I'm working on a
> metacoding framework, and it's inevitable that at some point it will want to
> know what objects it's created....
>
> I was planning on just making a best possible attempt and solving this in an
> extension via a combination of event triggers, reg* and other voodoo,

In the voodoo department, I guess PL/Java could accompany each loading of
a jar 'foo' with a 'CREATE TYPE sqlj.voodoo_foo AS ()' which is about as
lightweight as it gets, and an extra regtype column in the jar_repository
table could carry the Oid of that type. The type would be in pg_shdepend for
the owner, and its ACL could even be used to implement the SQL/JRT
requirement for jars to have an ACL (and be automatically represented
in pg_shdepend for any roles mentioned in the ACL). The type would also
end up in pg_depend for the owning extension, if the jar was installed
by an extension script. And (in 9.3+ anyway), I could have an sql_drop
event trigger to detect when the type goes away for any reason, and
remove the corresponding jar_repository entry.

How well will pg_dump/restore handle that voodoo? I suppose they'll
reliably recreate the types before loading the table with a regtype
column, and by typing the column as regtype, the dump will refer to
the type by name, and therefore work even if pg_dump is not given
the --oids option?

But that's all voodoo. What if we wanted to not need voodoo?

On 12/21/15 12:46, Tom Lane wrote:
> (I guess there are problems with extensions trying to do such things at
> all, since we don't provide a way for extensions to hook into the DROP
> mechanisms. Perhaps that should be fixed.)

Ok, how numerous would be the problems with this:

- The classid and refclassid columns (in both pg_shdepend and pg_depend)
are currently Oid columns referencing pg_class.oid. The catalog
definition would not preclude putting the oid of a non-system table
there. The *documentation* says it has to be the oid of a system
catalog, and surely there is code that currently would be surprised by
an entry that wasn't (for example, default: elog(ERROR, "unrecognized
object class..." in getObjectClass).
But even now, looking at recordDependencyOn or shdepAddDependency,
I don't see that the code would prevent such an entry being added.

- It still would make no sense as a refclassid in pg_shdepend. All three
other cases (classid in pg_shdepend, classid or refclassid in pg_depend)
can make sense in a world of extension-managed objects.

- So, what would be needed to make those 3 cases supportable? For starters,
how about a strict rule for *when* a non-system-catalog classid or
refclassid can be allowed into either of those tables:

IF an ObjectAddress.classId IS NOT a system catalog
(currently detectable by getObjectClass(classId) throwing an error),
THEN:
- it MUST be the Oid of an existing (non-system) relation
- that relation MUST be WITH OIDS (so the ObjectAddress.objectId can
identify a row in it)
... alternatively, must have an int unique key, and the objSubId can
be what identifies the row
- that relation MUST have a DELETE FOR EACH ROW trigger that calls
pg_extension_check_depend, a system-provided trigger function to enforce
reference integrity for any pg_depend/pg_shdepend mentions of the row
- that relation MAY have another DELETE trigger that was allowed to be
created by the restrictions on triggers below.

- The pg_extension_check_depend trigger function has two duties:
a. to ereport(ERROR) and prevent deletion in some circumstances
(for example, when the row to be deleted is mentioned on the
classid side of an 'e' dependency, and the extension isn't being
dropped, or on the refclassid side of a normal dependency, and
the dependent object isn't being dropped),
b. in all other cases, to allow the deletion, while also removing
associated pg_depend/pg_shdepend entries.
That's why no non-system table is allowed to be mentioned in pg_depend
or pg_shdepend unless it has a trigger that calls this function.

- CREATE TRIGGER refuses to create a trigger that calls
pg_extension_check_depend UNLESS:
- creating_extension is true, AND
- the trigger is being created on a table belonging to the current
extension, AND
- no other DELETE trigger exists on the table, unless the next rule
would allow it.

- CREATE TRIGGER refuses to create any other DELETE trigger on a table
that has a pg_extension_check_depend DELETE trigger, UNLESS:
- creating_extension is true, AND
- the table and the trigger function both belong to the current extension.

With that set of rules, extensions (and only extensions) are able to
invent and manage new kinds of dependency-managed objects, representing
them as rows in a table with appropriate triggers. When doDeletion, for
example, encounters a pg_depend record with a non-system classid, it is
simply treated as a deletion of the row oid=objectId from that relation,
invoking the delete trigger(s) normally.

DROP EXTENSION will have to rid pg_shdepend and pg_depend of all records
referring to tables in the extension (by silently removing the record of
the dependency, or by cascading deletion, as each case entails).

One remaining piece: the pg_depend and pg_shdepend logic both make use
of getObjectDescription, so they can produce useful messages like
"can't drop that because operator class baz depends on it".

Somehow, getObjectDescription needs a hook mechanism, so an extension that
adds a new kind of managed thing can return a description string for it
("can't drop that user because _PL/Java jar file foo_ depends on it" /
"deletion would cascade to _Jim Nasby metaprogramming artifact quux_").

So perhaps one final condition should be checked when adding any depend/
shdepend entry mentioning a non-system table row: getObjectDescription
must return a value for it, confirming it's been properly hooked.

Perhaps my last piece of brainstorming of 2015....

-Chap