Re: DROP EXTENSION

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)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