Subject: DROP EXTENSION

From: [email-masked]
Date: Mon, 20 Jun 2016 16:00:47 +0000

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 &quot;DROP EXTENSION removes extensions from the database. Dropping an
extension causes its component objects to be dropped as well.&quot; 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.

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Michael Paquier <[email-masked]>
Date: Tue, 21 Jun 2016 09:21:59 +0900

>  It should be stated that by default the dependencies remain intact after

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

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 21 Jun 2016 12:57:25 -0400

Michael Paquier wrote:
> 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 <[email-masked]>
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/[email-masked]

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

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 21 Jun 2016 14:13:01 -0400

> 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.

---

From: Tom Lane <[email-masked]>
Date: Tue, 21 Jun 2016 14:26:49 -0400

> 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

---

From: Michelle Schwan <[email-masked]>
Date: Tue, 21 Jun 2016 18:28:53 +0000

Ok - I understand!

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

-----Original Message-----
From: Tom Lane [mailto:[email-masked]] 
Sent: Tuesday, June 21, 2016 2:27 PM
To: Alvaro Herrera
Cc: Michael Paquier; Michelle Schwan; [email-masked]
Subject: Re: [DOCS] DROP EXTENSION

>   select public.unaccent('public.unaccent', $1) $$
>   LANGUAGE sql IMMUTABLE;

> 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

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 21 Jun 2016 14:32:58 -0400

> 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.

---

From: Tom Lane <[email-masked]>
Date: Tue, 21 Jun 2016 18:55:55 -0400

> 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

---

From: Tom Lane <[email-masked]>
Date: Tue, 21 Jun 2016 20:15:35 -0400

Michelle Schwan <[email-masked]> 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.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 22 Jun 2016 21:32:12 -0400

> https://www.postgresql.org/docs/devel/static/ddl-depend.html

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

-- 
  Bruce Momjian  <[email-masked]>        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 +

---

From: Magnus Hagander <[email-masked]>
Date: Thu, 23 Jun 2016 09:25:26 +0200

On Thu, Jun 23, 2016 at 3:32 AM, Bruce Momjian <[email-masked]> wrote:

> > > 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
> >

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.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 23 Jun 2016 09:28:54 -0400

On Thu, Jun 23, 2016 at 09:25:26AM +0200, Magnus Hagander wrote:
>     > > 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
> 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/

---

From: Tom Lane <[email-masked]>
Date: Thu, 23 Jun 2016 09:46:26 -0400

> broken in the build), the actual remaining time can be estimated by looking

Yup.  That's what I did.

			regards, tom lane