Thread: DROP EXTENSION

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.5/static/sql-dropextension.html
Description:

I dropped an extension in my database with a function dependency.  The
function was dropped (without messages of dependencies) and leaving the
function intact.
The lines "DROP EXTENSION removes extensions from the database. Dropping an
extension causes its component objects to be dropped as well." is not clear.
 It should be stated that by default the dependencies remain intact after
the drop.
However, I think this is an error in the command.  It should be the default
behavior to produce an error message stating there are dependencies and the
extension cannot be dropped.

On Tue, Jun 21, 2016 at 1:00 AM,  <mschwan@opentext.com> wrote:
> Page: https://www.postgresql.org/docs/9.5/static/sql-dropextension.html
> Description:
> I dropped an extension in my database with a function dependency.  The
> function was dropped (without messages of dependencies) and leaving the
> function intact.
> The lines "DROP EXTENSION removes extensions from the database. Dropping an
> extension causes its component objects to be dropped as well." is not clear.
>  It should be stated that by default the dependencies remain intact after
> the drop.

I am not following here. If an extension is dropped, so are its
dependencies. See that for example:
=# create extension pg_trgm;
CREATE EXTENSION
Time: 27.978 ms
=# \dx+ pg_trgm
                                  Objects in extension "pg_trgm"
                                        Object Description
--------------------------------------------------------------------------------------------------
 function gin_extract_query_trgm(text,internal,smallint,internal,internal,internal,internal)
 function gin_extract_value_trgm(text,internal)
[... lots of objects, including functions ...]
=# DROP EXTENSION pg_trgm;
DROP EXTENSION
=# \df gin_extract_query_trgm
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
--
Michael

Michael Paquier wrote:
> On Tue, Jun 21, 2016 at 1:00 AM,  <mschwan@opentext.com> wrote:
> > Page: https://www.postgresql.org/docs/9.5/static/sql-dropextension.html
> > Description:
> > I dropped an extension in my database with a function dependency.  The
> > function was dropped (without messages of dependencies) and leaving the
> > function intact.
> > The lines "DROP EXTENSION removes extensions from the database. Dropping an
> > extension causes its component objects to be dropped as well." is not clear.
> >  It should be stated that by default the dependencies remain intact after
> > the drop.
>
> I am not following here.

I think the use case involves the user creating a function that depends
on something (probably another function) in the extension.  So you drop
the extension, which drops the function your own function depends on,
but your own function remains in place.

This seems very similar to the use case for which we added the ALTER
OBJECT DEPEND ON EXTENSION (cited below) but not quite the same.
Perhaps mschwan can clarify.

commit f2fcad27d59c8e5c48f8fa0a96c8355e40f24273
Author:     Alvaro Herrera <alvherre@alvh.no-ip.org>
AuthorDate: Tue Apr 5 18:38:54 2016 -0300
CommitDate: Tue Apr 5 18:38:54 2016 -0300

    Support ALTER THING .. DEPENDS ON EXTENSION

    This introduces a new dependency type which marks an object as depending
    on an extension, such that if the extension is dropped, the object
    automatically goes away; and also, if the database is dumped, the object
    is included in the dump output.  Currently the grammar supports this for
    indexes, triggers, materialized views and functions only, although the
    utility code is generic so adding support for more object types is a
    matter of touching the parser rules only.

    Author: Abhijit Menon-Sen
    Reviewed-by: Alexander Korotkov, Álvaro Herrera
    Discussion: http://www.postgresql.org/message-id/20160115062649.GA5068@toroid.org


--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera wrote:
> Michael Paquier wrote:
> > On Tue, Jun 21, 2016 at 1:00 AM,  <mschwan@opentext.com> wrote:
> > > Page: https://www.postgresql.org/docs/9.5/static/sql-dropextension.html
> > > Description:
> > > I dropped an extension in my database with a function dependency.  The
> > > function was dropped (without messages of dependencies) and leaving the
> > > function intact.
> > > The lines "DROP EXTENSION removes extensions from the database. Dropping an
> > > extension causes its component objects to be dropped as well." is not clear.
> > >  It should be stated that by default the dependencies remain intact after
> > > the drop.
> >
> > I am not following here.
>
> I think the use case involves the user creating a function that depends
> on something (probably another function) in the extension.  So you drop
> the extension, which drops the function your own function depends on,
> but your own function remains in place.

Right; mschwan wrote private email to indicate that the function in
question is:

CREATE OR REPLACE FUNCTION public.f_unaccent(text)
  RETURNS text AS
$$
  select public.unaccent('public.unaccent', $1)
$$
  LANGUAGE sql IMMUTABLE;

so when the unaccent extension is dropped, this function remains (of
course) but it stops working.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Right; mschwan wrote private email to indicate that the function in
> question is:

> CREATE OR REPLACE FUNCTION public.f_unaccent(text)
>   RETURNS text AS
> $$
>   select public.unaccent('public.unaccent', $1)
> $$
>   LANGUAGE sql IMMUTABLE;

> so when the unaccent extension is dropped, this function remains (of
> course) but it stops working.

Ah, I kind of suspected that: the issue is that we don't analyze function
bodies to detect dependencies therein.  In a perfect world we would, but
there are daunting obstacles in the way.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Right; mschwan wrote private email to indicate that the function in
> > question is:
>
> > CREATE OR REPLACE FUNCTION public.f_unaccent(text)
> >   RETURNS text AS
> > $$
> >   select public.unaccent('public.unaccent', $1)
> > $$
> >   LANGUAGE sql IMMUTABLE;
>
> > so when the unaccent extension is dropped, this function remains (of
> > course) but it stops working.
>
> Ah, I kind of suspected that: the issue is that we don't analyze function
> bodies to detect dependencies therein.  In a perfect world we would, but
> there are daunting obstacles in the way.

Right :-(

So the ALTER FUNCTION .. DEPENDS ON EXTENSION thingy would kind-of help,
but instead of preventing the drop of the extension (which is probably
what mschwan would like to happen), it would silently drop the
public.f_unaccent() function when the extension was dropped.

I think (untested) that adding a pg_depend row with deptype='n' instead
of 'e' would have the desired property, i.e. DROP EXTENSION says "cannot
drop extension because function depends on it", and DROP EXTENSION
CASCADE drops both extension and function.  Maybe we could add
   ALTER FUNCTION .. DEPENDS ON EXTENSION unaccent WITH (autodrop=false)
or something similar.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Michelle Schwan <mschwan@opentext.com> writes:
> Ok - I understand!
> However, could that be stated in the documentation so that is clear?

I had thought it *was* documented, but can't find such a statement at
the moment.  However, the natural place to document it would be in
section 5.13 "Dependency Tracking", and/or in discussions of user-defined
functions; this behavior really has nothing to do with extensions per se.
So I'm not sure it would have helped you :-(

            regards, tom lane

Michelle Schwan <mschwan@opentext.com> writes:
> That sounds good (but in my case there was no "silent drop"!).
> I would presume that standard functionality would be drop cascade drops all dependent and just drop errors if
extensionis used anywhere.  But, in the meantime, I think the documentation should reflect this feature. 

Done at
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=342921078a76a34fd2f44f121f225126778eb2cb

Results should be visible at
https://www.postgresql.org/docs/devel/static/ddl-depend.html
in half an hour or so.

            regards, tom lane

Ok - I understand!

However, could that be stated in the documentation so that is clear?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 21, 2016 2:27 PM
To: Alvaro Herrera
Cc: Michael Paquier; Michelle Schwan; pgsql-docs@postgresql.org
Subject: Re: [DOCS] DROP EXTENSION

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Right; mschwan wrote private email to indicate that the function in
> question is:

> CREATE OR REPLACE FUNCTION public.f_unaccent(text)
>   RETURNS text AS
> $$
>   select public.unaccent('public.unaccent', $1) $$
>   LANGUAGE sql IMMUTABLE;

> so when the unaccent extension is dropped, this function remains (of
> course) but it stops working.

Ah, I kind of suspected that: the issue is that we don't analyze function bodies to detect dependencies therein.  In a
perfectworld we would, but there are daunting obstacles in the way. 

            regards, tom lane

On Tue, Jun 21, 2016 at 08:15:35PM -0400, Tom Lane wrote:
> Michelle Schwan <mschwan@opentext.com> writes:
> > That sounds good (but in my case there was no "silent drop"!).
> > I would presume that standard functionality would be drop cascade drops all dependent and just drop errors if
extensionis used anywhere.  But, in the meantime, I think the documentation should reflect this feature. 
>
> Done at
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=342921078a76a34fd2f44f121f225126778eb2cb
>
> Results should be visible at
> https://www.postgresql.org/docs/devel/static/ddl-depend.html
> in half an hour or so.

Uh, I don't think that builds every 30 minutes --- I think it is 4-6
hours.  Comments?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

On Thu, Jun 23, 2016 at 09:25:26AM +0200, Magnus Hagander wrote:
>
>
> On Thu, Jun 23, 2016 at 3:32 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
>     On Tue, Jun 21, 2016 at 08:15:35PM -0400, Tom Lane wrote:
>     > Michelle Schwan <mschwan@opentext.com> writes:
>     > > That sounds good (but in my case there was no "silent drop"!).
>     > > I would presume that standard functionality would be drop cascade drops
>     all dependent and just drop errors if extension is used anywhere.  But, in
>     the meantime, I think the documentation should reflect this feature.
>     >
>     > Done at
>     > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=
>     342921078a76a34fd2f44f121f225126778eb2cb
>     >
>     > Results should be visible at
>     > https://www.postgresql.org/docs/devel/static/ddl-depend.html
>     > in half an hour or so.
>
>     Uh, I don't think that builds every 30 minutes --- I think it is 4-6
>     hours.  Comments?
>
>
> Correct, the developer docs are updated every 4 hours.
>
> But as it's a fairly predictable every-4-hours (unless there is something
> broken in the build), the actual remaining time can be estimated by looking at
> the buildfarm.

OK.  I do maintain a doc build that is <20 minutes so I can quickly post
URLs for doc review:

    http://momjian.us/pgsql_docs/

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

Magnus Hagander <magnus@hagander.net> writes:
> On Thu, Jun 23, 2016 at 3:32 AM, Bruce Momjian <bruce@momjian.us> wrote:
>> On Tue, Jun 21, 2016 at 08:15:35PM -0400, Tom Lane wrote:
>>> Results should be visible at
>>> https://www.postgresql.org/docs/devel/static/ddl-depend.html
>>> in half an hour or so.

>> Uh, I don't think that builds every 30 minutes --- I think it is 4-6
>> hours.  Comments?

> Correct, the developer docs are updated every 4 hours.
> But as it's a fairly predictable every-4-hours (unless there is something
> broken in the build), the actual remaining time can be estimated by looking
> at the buildfarm.

Yup.  That's what I did.

            regards, tom lane