Обсуждение: BUG #15418: pg_restore fails with [archiver (db)] could not executequery: ERROR: function XXXXX does not exist

The following bug has been logged on the website:

Bug reference:      15418
Logged by:          David Harper
Email address:      adh@sanger.ac.uk
PostgreSQL version: 10.5
Operating system:   Ubuntu 16.04.5 LTS
Description:

A database dumped using "pg_dump -Fc ..." cannot subsequently be loaded
correctly using pg_restore.  This happens because pg_restore imports
functions in the wrong order, attempting to import function A before
function B, when the definition of function A uses function B.  This bug
manifests itself on versions 9.6.9 and 10.5.1 of the server and client.  The
following shell script reliably manifests the bug:

#!/bin/bash

echo ">>>> Create first database"

dropdb --if-exists testdb1

createdb testdb1

echo ">>>> Populate first database"

psql -d testdb1 <<'EOSQL'
CREATE SCHEMA david;

CREATE TABLE david.featuregroup (
    featuregroup_id bigint NOT NULL,
    subject_id bigint NOT NULL,
    object_id bigint NOT NULL,
    group_id bigint NOT NULL,
    srcfeature_id bigint,
    fmin bigint,
    fmax bigint,
    strand integer,
    is_root integer DEFAULT 0 NOT NULL
);

CREATE FUNCTION public.create_point(bigint, bigint) RETURNS point
    LANGUAGE sql
    AS $_$SELECT point ($1, $2)$_$;

CREATE FUNCTION public.boxrange(bigint, bigint) RETURNS box
    LANGUAGE sql IMMUTABLE
    AS $_$SELECT box (create_point(0, $1), create_point($2,500000000))$_$;

CREATE INDEX bingroup_boxrange ON david.featuregroup
  USING gist (public.boxrange(fmin, fmax)) WHERE (is_root = 1);
EOSQL

echo ">>>> Dump first database"

pg_dump -Fc -f testdb1.dmp testdb1

pg_dump -Fp -f testdb1.sql testdb1

echo ">>>> Create second database"

dropdb --if-exists testdb2

createdb testdb2

echo ">>>> Load dump file into second database"

pg_restore -d testdb2 testdb1.dmp
### End of script

Hello
You have found wrong error root. Restore order is correct. Try with this function definition:

CREATE FUNCTION public.boxrange(bigint, bigint) RETURNS box
    LANGUAGE sql IMMUTABLE
    AS $_$SELECT box (public.create_point(0, $1), public.create_point($2,500000000))$_$;

It works now.
pg_restore (and pg_dump with plaintext format) set search_path to '' and this is reason why function was not found.
This was made to prevent security issue:
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

regards, Sergei

Hello Sergei,

Many thanks for your quick and very helpful reply.  I will advise the owners of the affected database that they need to
modifytheir function definitions as you have advised.  I am only a humble DBA who was given the task of automating the
processof copying a production database to a test cluster! 

Best regards

David

--
 The Wellcome Sanger Institute is operated by Genome Research
 Limited, a charity registered in England with number 1021457 and a
 company registered in England with number 2742969, whose registered
 office is 215 Euston Road, London, NW1 2BE.