Subject: RE: Ability to reference other extensions by schema in extension scripts

From: "Regina Obe" <[email-masked]>
Date: Mon, 6 Feb 2023 05:19:39 -0500

> > > Here is first version of my patch using the
> > > @extschema:extensionname@ syntax you proposed.
> > >
> > > This patch includes:
> > > 1) Changes to replace references of @extschema:extensionname@ with
> > > the schema of the required extension
> > > 2) Documentation for the feature
> > > 3) Tests for the feature.
> > >

Attached is a revised version of the original patch. It is revised to
prevent 

ALTER EXTENSION .. SET SCHEMA  if there is a dependent extension that 
references the extension in their scripts using @extschema:extensionname@
It also adds additional tests to verify that new feature.

In going thru the code base, I was tempted to add a new dependency type
instead of using the existing DEPENDENCY_AUTO.  I think this would be
cleaner, but I felt that was overstepping the area a bit, since it requires
making changes to dependency.h and dependency.c

My main concern with using DEPENDENCY_AUTO is because it was designed for
cases where an object can be dropped without need for CASCADE.  In this
case, we don't want a dependent extension to be dropped if it's required is
dropped.  However since there will already exist 
a DEPENDENCY_NORMAL between the 2 extensions, I figure we are protected
against that issue already.

The issue I ran into is there doesn't seem to be an easy way of checking if
a pg_depend record is already in place, so I ended up dropping it first with
deleteDependencyRecordsForSpecific so I wouldn't need to check and then
reading it.

The reason for that is during CREATE EXTENSION it would need to create the
dependency.
It would also need to do so with ALTER EXTENSION  .. UPDATE, since extension
could later on add it in their upgrade scripts and so there end up being
dupes after many ALTER EXTENSION UPDATE calls.

pg_depends getAutoExtensionsOfObject  seemed suited to that check, as is
done in 

alter.c ExecAlterObjectDependsStmt
		/* Avoid duplicates */
		currexts = getAutoExtensionsOfObject(address.classId,
	
address.objectId);
		if (!list_member_oid(currexts, refAddr.objectId))
			recordDependencyOn(&address, &refAddr,
DEPENDENCY_AUTO_EXTENSION);

but it is hard-coded to only check  DEPENDENCY_AUTO_EXTENSION

Why isn't there a variant getAutoExtensionsOfObject take a DEPENDENCY type
as an option so it would be more useful or is there functionality for that I
missed?

Thanks,
Regina