BUG #15248: pg_upgrade fails when a function with an emptysearch_path is encountered - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15248
Logged by:          Steven Winfield
Email address:      steven.winfield@cantabcapital.com
PostgreSQL version: 11beta1
Operating system:   RHEL 7.4
Description:

I suspect this is reproducible with pg_dump / pg_restore too.

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:

pg_restore: creating FUNCTION "public.foo("int")"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3488; 1255 67351571
FUNCTION foo("int") dbadmin
pg_restore: [archiver (db)] could not execute query: ERROR:  zero-length
delimited identifier at or near """"
LINE 3:     SET "search_path" TO ""
                                 ^
    Command was: CREATE FUNCTION "public"."foo"("int") RETURNS int
    LANGUAGE "sql" 
    SET "search_path" TO ""
    AS $_$ SELECT 1 $_$

The fix is to use single quotes in this case, and I suppose the workaround
is to specify 'pg_temp,pg_catalog' instead of the empty string.