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

> On Mon, Jan 16, 2023 at 11:57:30PM -0500, Regina Obe wrote:
>
> > What would be more bullet-proof is having an extra column in
> > pg_extension or adding an extra array element to
> > pg_extension.extcondition[] that allows you to say "Hey, don't allow
> > this to be relocatable cause other extensions depend on it that have
explicitly
> referenced the schema."
>
> I've given this some more thoughts and I think a good compromise could be
to
> add the safety net in ALTER EXTESION SET SCHEMA so that it does not only
> check "extrelocatable" but also the presence of any extension effectively
> depending on it, in which case the operation could be prevented with a
more
> useful message than "extension does not support SET SCHEMA" (what is
> currently output).
>
> Example query to determine those cases:
>
> SELECT e.extname, array_agg(v.name)
> FROM pg_extension e, pg_available_extension_versions v
> WHERE e.extname = ANY( v.requires )
> AND e.extrelocatable
> AND v.installed group by e.extname;
>
> extname | array_agg
> ---------------+--------------------------
> fuzzystrmatch | {postgis_tiger_geocoder}
>
> --strk;

The only problem with the above is then it bars an extension from being
relocated even if no extensions reference their schema. Note you wouldn't
be able to tell if an extension references a schema without analyzing the
install script. Which is why I was thinking another property would be
better, cause that could be checked during the install/upgrade of the
dependent extensions.

I personally would be okay with this and is easier to code I think and
doesn't require structural changes, but not sure others would be as it's
taking away permissions they had before when it wasn't necessary to do so.

Thanks,
Regina