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
*** a/contrib/unaccent/unaccent--1.0.sql --- b/contrib/unaccent/unaccent--1.0.sql *************** *** 32,34 **** CREATE TEXT SEARCH DICTIONARY unaccent ( --- 32,39 ---- TEMPLATE = unaccent, RULES = 'unaccent' ); + + CREATE FUNCTION no_accents(text) + RETURNS boolean + AS 'select $1 = unaccent($1);' + LANGUAGE sql STABLE STRICT;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers