Re: CREATE FUNCTION .. SET vs. pg_dump

On 08/18/2013 05:40 PM, Tom Lane wrote:
> 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.)

hmm yeah - just throwing a NOTICE with check_function_bodies=off seems
like reasonable workaround to this problem area.
Not sure it would be required to turn it into a NOTICE in general,
though alter role/alter database seems like an established precedence
for this.

Stefan