Re: alter function/procedure depends on extension

On Thu, Feb 17, 2022 at 9:32 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Feb 17, 2022 at 8:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>> > On Thu, Feb 17, 2022 at 6:17 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> >> There is no bug here; it's operating as designed and documented.
>> >> Maybe the docs need some refinement.
>>
>> > We should just say that ALTER FUNCTION ALTER DEPENDS ON makes the target
>> > function/procedure a (transient?) member of the extension and it will be
>> > dropped when the extension be dropped.
>>
>> It's not a member though; there's a different syntax for that
>> (ALTER EXTENSION name ADD member_object). The differences are
>> a bit subtle, but for example making the function an extension
>> member would change how pg_dump treats it.
>>
>
> So I amend my suggestions to:
>
>
Concretely as attached (and inlined):

commit 18206a1b2c81ae280ad9927decc5a975011ffb80
Author: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Date: Tue Apr 12 15:30:45 2022 +0000

doc: Elaborate on the interplay between depends on and drop extension

Per discussion on -general the documentation for the
ALTER ROUTINE ... DEPENDS ON EXTENSION and DROP EXTENSION doesn't
clearly indicate that these dependent routines are treated in a
similar manner to the extension's owned objects when it comes to
using RESTRICT mode drop: namely their presence doesn't force
the drop command to abort. Clear that up.

diff --git a/doc/src/sgml/ref/alter_function.sgml
b/doc/src/sgml/ref/alter_function.sgml
index 0ee756a94d..4597b4a4bb 100644
--- a/doc/src/sgml/ref/alter_function.sgml
+++ b/doc/src/sgml/ref/alter_function.sgml
@@ -160,8 +160,10 @@ ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [
<replaceable class="param
<para>
This form marks the function as dependent on the extension, or no
longer
dependent on that extension if <literal>NO</literal> is specified.
- A function that's marked as dependent on an extension is
automatically
- dropped when the extension is dropped.
+ A function that's marked as dependent on an extension is skipped
during
+ dependency checking in restrict mode <xref
linkend="sql-dropextension"/>.
+ A function can depend upon multiple extensions, and will be dropped
when
+ any one of those extensions is dropped.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/alter_procedure.sgml
b/doc/src/sgml/ref/alter_procedure.sgml
index 033fda92ee..fe835acccf 100644
--- a/doc/src/sgml/ref/alter_procedure.sgml
+++ b/doc/src/sgml/ref/alter_procedure.sgml
@@ -147,7 +147,12 @@ ALTER PROCEDURE <replaceable>name</replaceable> [ ( [
[ <replaceable class="para
<term><replaceable
class="parameter">extension_name</replaceable></term>
<listitem>
<para>
- The name of the extension that the procedure is to depend on.
+ This form marks the procedure as dependent on the extension, or no
longer
+ dependent on that extension if <literal>NO</literal> is specified.
+ A procedure that's marked as dependent on an extension is skipped
during
+ dependency checking in restrict mode <xref
linkend="sql-dropextension"/>.
+ A procedure can depend upon multiple extensions, and will be dropped
when
+ any one of those extensions is dropped.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/drop_extension.sgml
b/doc/src/sgml/ref/drop_extension.sgml
index 5e507dec92..ec5f7bc4f7 100644
--- a/doc/src/sgml/ref/drop_extension.sgml
+++ b/doc/src/sgml/ref/drop_extension.sgml
@@ -30,7 +30,9 @@ DROP EXTENSION [ IF EXISTS ] <replaceable
class="parameter">name</replaceable> [

<para>
<command>DROP EXTENSION</command> removes extensions from the database.
- Dropping an extension causes its component objects to be dropped as
well.
+ Dropping an extension causes its component objects, and other explicitly
+ dependent routines (see <xref linkend="sql-alterroutine"/>,
+ the depends on extension action), to be dropped as well.
</para>

<para>
@@ -77,9 +79,10 @@ DROP EXTENSION [ IF EXISTS ] <replaceable
class="parameter">name</replaceable> [
<term><literal>RESTRICT</literal></term>
<listitem>
<para>
- Refuse to drop the extension if any objects depend on it (other than
- its own member objects and other extensions listed in the same
- <command>DROP</command> command). This is the default.
+ For each extension, refuse to drop anything if any objects (other
than the
+ other extensions listed) depend on it. However, its own member
objects, and routines
+ that are explicitly dependent on this extension, are skipped.
+ This is the default.
</para>
</listitem>
</varlistentry>