On Thu, Feb 17, 2022 at 10:04 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.
