Re: Ability to reference other extensions by schema in extension scripts - Mailing list pgsql-hackers

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;