Add ALTER .. NO DEPENDS ON

Commit f2fcad27d59c (9.6 era) added the ability to mark objects as
dependent an extension, but forgot to add a way for such dependencies to
be removed.  This commit fixes that oversight.

Strictly speaking this should be backpatched to 9.6, but due to lack of
demand we're not doing so at this time.

Discussion: https://postgr.es/m/20200217225333.GA30974@alvherre.pgsql
Reviewed-by: ahsan hadi <ahsan.hadi@gmail.com>
Reviewed-by: Ibrar Ahmed <ibrar.ahmad@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>

4 files changed, 22 insertions, 15 deletions

diff --git a/doc/src/sgml/ref/alter_function.sgml b/doc/src/sgml/ref/alter_function.sgml
index 03ffa5945a2..70b1f24bc00 100644
--- a/doc/src/sgml/ref/alter_function.sgml
+++ b/doc/src/sgml/ref/alter_function.sgml
@@ -30,7 +30,7 @@ ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="param
 ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
     SET SCHEMA <replaceable>new_schema</replaceable>
 ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ]
-    DEPENDS ON EXTENSION <replaceable>extension_name</replaceable>
+    [ NO ] DEPENDS ON EXTENSION <replaceable>extension_name</replaceable>
 
 <phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
 
@@ -153,10 +153,14 @@ ALTER FUNCTION <replaceable>name</replaceable> [ ( [ [ <replaceable class="param
    </varlistentry>
 
    <varlistentry>
-    <term><replaceable class="parameter">extension_name</replaceable></term>
+    <term><literal>DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
+    <term><literal>NO DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
     <listitem>
      <para>
-      The name of the extension that the function is to depend on.
+      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.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml
index 6d34dbb74e5..de6f89d458c 100644
--- a/doc/src/sgml/ref/alter_index.sgml
+++ b/doc/src/sgml/ref/alter_index.sgml
@@ -100,11 +100,14 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable>
    </varlistentry>
 
    <varlistentry>
-    <term><literal>DEPENDS ON EXTENSION</literal></term>
+    <term><literal>DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
+    <term><literal>NO DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable></literal></term>
     <listitem>
      <para>
-      This form marks the index as dependent on the extension, such that if the
-      extension is dropped, the index will automatically be dropped as well.
+      This form marks the index as dependent on the extension, or no longer
+      dependent on that extension if <literal>NO</literal> is specified.
+      An index that's marked as dependent on an extension is automatically
+      dropped when the extension is dropped.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml
index 03e3df1ffdb..9df8a799771 100644
--- a/doc/src/sgml/ref/alter_materialized_view.sgml
+++ b/doc/src/sgml/ref/alter_materialized_view.sgml
@@ -69,12 +69,6 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</r
   </para>
 
   <para>
-   The <literal>DEPENDS ON EXTENSION</literal> form marks the materialized view
-   as dependent on an extension, such that the materialized view will
-   automatically be dropped if the extension is dropped.
-  </para>
-
-  <para>
    The statement subforms and actions available for
    <command>ALTER MATERIALIZED VIEW</command> are a subset of those available
    for <command>ALTER TABLE</command>, and have the same meaning when used for
@@ -110,7 +104,10 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</r
      <term><replaceable class="parameter">extension_name</replaceable></term>
      <listitem>
       <para>
-       The name of the extension that the materialized view is to depend on.
+       The name of the extension that the materialized view is to depend on (or no longer
+       dependent on, if <literal>NO</literal> is specified).  A materialized view
+       that's marked as dependent on an extension is automatically dropped when
+       the extension is dropped.
       </para>
      </listitem>
     </varlistentry>
diff --git a/doc/src/sgml/ref/alter_trigger.sgml b/doc/src/sgml/ref/alter_trigger.sgml
index 6cf789a67a3..6d4784c82f1 100644
--- a/doc/src/sgml/ref/alter_trigger.sgml
+++ b/doc/src/sgml/ref/alter_trigger.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
-ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable>
+ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable> [ NO ] DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable>
 </synopsis>
  </refsynopsisdiv>
 
@@ -78,7 +78,10 @@ ALTER TRIGGER <replaceable class="parameter">name</replaceable> ON <replaceable
     <term><replaceable class="parameter">extension_name</replaceable></term>
     <listitem>
      <para>
-      The name of the extension that the trigger is to depend on.
+      The name of the extension that the trigger is to depend on (or no longer
+      dependent on, if <literal>NO</literal> is specified).  A trigger
+      that's marked as dependent on an extension is automatically dropped when
+      the extension is dropped.
      </para>
     </listitem>
    </varlistentry>