Re: alter function/procedure depends on extension

On Thu, Feb 17, 2022 at 10:04 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 2/17/22 08:52, Bryn Llewellyn wrote:
> > **Briefly**
> >
>
> > *qu. 2.* Why does "drop extension restrict" quietly succeed—having the
> > effect of cascade-dropping any subprogram that you've altered to depend
> > on the extension in question? This is at odds with the documented
> > meaning of "restrict".
>
> But not the ALTER FUNCTION docs:
>
> https://www.postgresql.org/docs/current/sql-alterfunction.html
>
> "DEPENDS ON EXTENSION extension_name
> NO DEPENDS ON EXTENSION extension_name
>
> This form marks the function as dependent on the extension, or no
> longer dependent on that extension if NO is specified. A function that's
> marked as dependent on an extension is automatically dropped when the
> extension is dropped.
> "
>
> And from DROP EXTENSION
>
> https://www.postgresql.org/docs/current/sql-dropextension.html
>
> "RESTRICT
>
> Refuse to drop the extension if any objects depend on it (other
> than its own member objects and other extensions listed in the same DROP
> command). This is the default.
> "
>
> The DEPENDS ON EXTENSION turns a function into a member object. The
> '...any objects depend on it....' is shown in the EXAMPLES:
>

This is categorically wrong on its face. I can depend on an extension
without being a member thereof. We already have a command to add a
function to the extension - it's in ALTER EXTENSION. The ALTER FUNCTION
command does not simply duplicate this but rather does it's own thing -
establishes a dependency.

I find the described behavior buggy - the documentation being unclear at
minimum. A function that has added an extension as a dependency should
expect that it MAY be dropped if the extension is dropped. But if the
extension dropping command says "don't do this if any [functions] depend on
me" that safety constraint should apply. The function depends on the
extension and so the dropping of the extension should fail due to that
dependency. The function doesn't get a vote here but the person executing
a command with RESTRICT does.

So the behavior of DROP EXTENSION needs to be fixed and the documentation
for ALTER FUNCTION should read "...as dependent on an extension may be
dropped when the extension is dropped".

David J.

P.S. The absence of a clean multi-extension declaring version of the ALTER
FUNCTION syntax is just a missing feature that someone could very well
spend time adding. Its absence doesn't seem like a big deal though.