Thread: Dump/Reload broken with relocatable extensions

I don't know if this has been discussed before, a cursory search of the
archives didn't turn up anything interesting.  I perhaps didn't put in
the right keywords.

Unfortunately, the easiest way that I've found to reproduce this bug is
to apply the attached patch to the unaccent extension.  This isn't a bug
with that extension, it was just the simplest.  With that patch applied,
the following steps will put a database in a state which cannot be
dumped/restored.

create extension unaccent with schema public;
create schema s;
create table s.t (v text check (public.no_accents(v)));
insert into s.t values ('a');

The problem is the no_accents(text) function, which belongs to the
unaccent extension, calls unaccent(text), also belonging to the unaccent
extension.  If a table living in a different schema to that of the
extension has a CHECK constraint using the function, the dump/restore
behavior of setting the search_path will cause restoration to fail.

At first I thought the solution would be to have all functions of an
extension have a custom search_path equal to that of the extension, but
that doesn't really work because it would cause too many undesirable
side effects.

Another solution could be to postpone adding constraints until after
everything's been set up, but that seems a bit unwieldly.

My preferred solution at the moment is to invent a special $extension
schema analog to the $user schema.  It wouldn't be implicit like the
$user one, but an extension could call one of its own functions as
$extension.funcname().  I'm not sure what should happen if the caller
isn't part of an extension.  I'm leaning towards a "schema does not
exist" error.  This has grammar issues, though, that $user doesn't have.

The original problem came from a CHECK constraint on the PostGIS
_raster_constraint_pixel_types(raster) function which calls
st_bandmetadata(raster, int[]).  I thought that  a simple patch to the
in-core extension unaccent would be more practical.  I am not proposing
adding this patch to the unaccent extension.

--
Vik

Vik Fearing <vik.fearing@dalibo.com> writes:
> I don't know if this has been discussed before, a cursory search of the
> archives didn't turn up anything interesting.  I perhaps didn't put in
> the right keywords.

For others not to spend too much time on this: it seems like a problem
with the extension not abiding by the rules about its relocatable
property and the @extschema@ thingy.
 http://www.postgresql.org/docs/9.3/static/extend-extensions.html#AEN54999

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On 09/19/2013 11:40 PM, Dimitri Fontaine wrote:
> Vik Fearing <vik.fearing@dalibo.com> writes:
>> I don't know if this has been discussed before, a cursory search of the
>> archives didn't turn up anything interesting.  I perhaps didn't put in
>> the right keywords.
> For others not to spend too much time on this: it seems like a problem
> with the extension not abiding by the rules about its relocatable
> property and the @extschema@ thingy.
>
>   http://www.postgresql.org/docs/9.3/static/extend-extensions.html#AEN54999

I can't get this to work.  If I modify my function to be

CREATE FUNCTION no_accents(text)   RETURNS boolean   AS 'select $1 = unaccent($1);'   LANGUAGE sql STABLE STRICT   SET
search_path= '@extschema@';
 

then I get

d=# create extension unaccent;
ERROR:  function unaccent(text) does not exist
LINE 1: select $1 = unaccent($1);                   ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:  select $1 = unaccent($1);


If I modify it to be

CREATE FUNCTION no_accents(text)   RETURNS boolean   AS 'select $1 = unaccent($1);'   LANGUAGE sql STABLE STRICT;
ALTER FUNCTION no_accents(text) SET search_path = '@extschema@';

then I get the same restore problem I originally described.

What am I doing wrong?

-- 
Vik

On 09/19/2013 11:40 PM, Dimitri Fontaine wrote:
> Vik Fearing <vik.fearing@dalibo.com> writes:
>> I don't know if this has been discussed before, a cursory search of the
>> archives didn't turn up anything interesting.  I perhaps didn't put in
>> the right keywords.
> For others not to spend too much time on this: it seems like a problem
> with the extension not abiding by the rules about its relocatable
> property and the @extschema@ thingy.
>
>   http://www.postgresql.org/docs/9.3/static/extend-extensions.html#AEN54999

We've reported this as a PostGIS bug.
http://trac.osgeo.org/postgis/ticket/2485

-- 
Vik