[DOCS] DROP EXTENSION

[m***@opentext.com]

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.

[Michael Paquier]

Post by m***@opentext.com
Page: https://www.postgresql.org/docs/9.5/static/sql-dropextension.html
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)

[Alvaro Herrera]

Post by Michael Paquier

Post by m***@opentext.com
Page: https://www.postgresql.org/docs/9.5/static/sql-dropextension.html
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 <***@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/***@toroid.org

[Alvaro Herrera]

Post by Alvaro Herrera

Post by Michael Paquier

Post by m***@opentext.com
Page: https://www.postgresql.org/docs/9.5/static/sql-dropextension.html
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.

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.

[Tom Lane]

Post by Alvaro Herrera
Right; mschwan wrote private email to indicate that the function in
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

[Alvaro Herrera]

Post by Tom Lane

Post by Alvaro Herrera
Right; mschwan wrote private email to indicate that the function in
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.

[Tom Lane]

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

[Tom Lane]

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

[Michelle Schwan]

Ok - I understand!

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

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

Post by Alvaro Herrera
Right; mschwan wrote private email to indicate that the function in
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

[Bruce Momjian]

Post by Tom Lane

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?
--
Bruce Momjian <***@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]

Post by Tom Lane

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.

Post by Tom Lane
Done at

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

Post by Tom Lane
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.

[Bruce Momjian]

Post by Tom Lane

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.

Post by Tom Lane
Done at
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=

342921078a76a34fd2f44f121f225126778eb2cb

Post by Tom Lane
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 <***@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 +

[Tom Lane]

Post by Magnus Hagander

Post by Bruce Momjian

Post by Tom Lane
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