Date: Tue, 21 Jun 2016 14:32:58 -0400
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael.paquier@gmail.com>, mschwan@opentext.com,
	pgsql-docs@postgresql.org
Subject: Re: DROP EXTENSION
Message-ID: <20160621183258.GA101603@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <12382.1466533609@sss.pgh.pa.us>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

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


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