Re: tracking owner of extension-managed objects - Mailing list pgsql-hackers

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
surprisedby an entry that wasn't (for example, default: elog(ERROR, "unrecognized object class..." in getObjectClass).
Buteven now, looking at recordDependencyOn or shdepAddDependency, I don't see that the code would prevent such an entry
beingadded.
 

- It still would make no sense as a refclassid in pg_shdepend. All three other cases (classid in pg_shdepend, classid
orrefclassid 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-catalogclassid 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.objectIdcan   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_shdependmentions of the row - that relation MAY have another DELETE trigger that was allowed to be
createdby 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
theextension isn't being    dropped, or on the refclassid side of a normal dependency, and    the dependent object
isn'tbeing dropped), b. in all other cases, to allow the deletion, while also removing    associated
pg_depend/pg_shdependentries. That's why no non-system table is allowed to be mentioned in pg_depend or pg_shdepend
unlessit 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
onthe 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