-----Original Message-----
From: PG Bug reporting form [
mailto:noreply@postgresql.org]
Sent: 19 June 2018 12:18
To: pgsql-bugs@lists.postgresql.org
Cc: Steven Winfield
Subject: BUG #15248: pg_upgrade fails when a function with an empty search_path is encountered
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.
Also, either pg_get_functiondef is similarly affected, or pg_dump/pg_restore is using that that to generate the SQL containing the double quotes.