Re: BUG #15248: pg_upgrade fails when a function with an empty search_path is encountered

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> If a function is defined like this:

> CREATE FUNCTION public.foo(int) RETURNS int
>     LANGUAGE "sql"
>     SET search_path TO ''
>     AS $_$ SELECT 1; $_$;

> ...then, during pg_upgrade, pg_restore renders this SQL to recreate the
> function using double-quotes around the empty search_path:

>     Command was: CREATE FUNCTION "public"."foo"("int") RETURNS int
>     LANGUAGE "sql" 
>     SET "search_path" TO ""
>     AS $_$ SELECT 1 $_$

> [ which isn't legal syntax ]

Hm.  So, basically, this is due to insufficient thought about quoting
rules in commits 742869946 et al.  We made pg_dump print out exactly
what it sees in pg_proc.proconfig for this case --- but that string
has been constructed by flatten_set_variable_args, and while its
quoting rules are a lot like those for double-quoted SQL identifiers,
they aren't identical.  Notably we have this issue, that "" is a legal
empty string as per flatten_set_variable_args, but it's not a legal
SQL identifier.  Another problem, now that I think about it, is that
on reload a double-quoted identifier is going to get truncated to
NAMEDATALEN, but that's not necessarily appropriate --- some
GUC_LIST_QUOTE variables contain file path names that could legally
be longer than that.

If we were working in a green field I'd think about revising
flatten_set_variable_args, but touching those rules is pretty scary;
even if we found all the relevant places in the backend and pg_dump,
there might be third-party code that is looking at proconfig or
related catalog columns.  Also, that'd do nothing for existing
database objects that have config strings in the existing style.

So it seems like what we have to do here is to teach pg_dump and ruleutils
to parse a GUC_LIST_QUOTE value the same way SplitIdentifierString does,
and then quote each extracted list element as a string literal.  Bleah.
It's not *that* much code, but it's annoying, especially because of the
duplicated logic.

            regards, tom lane