From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: steven.winfield@cantabcapital.com
Subject: BUG #15248: pg_upgrade fails when a function with an empty search_path is encountered
Date: Tue, 19 Jun 2018 11:17:56 +0000
Message-ID: <152940707697.1452.4922154343836581163@wrigleys.postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, steven.winfield@cantabcapital.com
Subject: Re: BUG #15248: pg_upgrade fails when a function with an empty search_path is encountered
In-Reply-To: <152940707697.1452.4922154343836581163@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox