Re: CREATE FUNCTION .. SET vs. pg_dump

Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> writes:
> While working on upgrading the database of the search system on
> postgresql.org to 9.2 I noticed that the dumps that pg_dump generates on
> that system are actually invalid and cannot be reloaded without being
> hacked on manually...

> CREATE TEXT SEARCH CONFIGURATION pg (
> PARSER = pg_catalog."default" );

> CREATE FUNCTION test() RETURNS INTEGER
> LANGUAGE sql SET default_text_search_config TO 'public.pg' AS $$
> SELECT 1;
> $$;

> once you dump that you will end up with an invalid dump because the
> function will be dumped before the actual text search configuration is
> (re)created.

I don't think it will work to try to fix this by reordering the dump;
it's too easy to imagine scenarios where that would lead to circular
ordering constraints. What seems like a more workable answer is for
CREATE FUNCTION to not attempt to validate SET clauses when
check_function_bodies is off, or at least not throw a hard error when
the validation fails. (I see for instance that if you try
ALTER ROLE joe SET default_text_search_config TO nonesuch;
you just get a notice and not an error.)

However, I don't recall if there are any places where we assume the
SET info was pre-validated by CREATE/ALTER FUNCTION.

regards, tom lane