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