Re: alter function/procedure depends on extension - Mailing list pgsql-general

On Thu, Feb 17, 2022 at 8:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Feb 17, 2022 at 6:17 PM Tom Lane <tgl@sss.pgh.pa.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:

Author: David G. Johnston <david.g.johnston@gmail.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>