Обсуждение: [pg_dump] 'create index' statement is failing due to search_path isempty

Hi ,

While testing something else ,i found 1 scenario  where pg_dump  is failing

Below is the standalone scenario -

--connect to psql terminal and create 2 database

postgres=# create database db1;
CREATE DATABASE
postgres=# create database db2;
CREATE DATABASE

--Connect to database db1 and run these below bunch of sql ( got from 
vacuum.sql file)

\c db1

create  temp table vaccluster (i INT PRIMARY KEY);
ALTER TABLE vaccluster CLUSTER ON vaccluster_pkey;
CLUSTER vaccluster;

CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
         AS 'ANALYZE pg_am';
CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
         AS 'SELECT $1 FROM do_analyze()';
CREATE INDEX ON vaccluster(wrap_do_analyze(i));
INSERT INTO vaccluster VALUES (1), (2);

--Take the  dump of db1 database  ( ./pg_dump -Fp db1 > /tmp/dump.sql)

--Restore the dump file into db2 database

You are now connected to database "db2" as user "tushar".
db2=# \i /tmp/dump.sql
SET
SET
SET
SET
SET
  set_config
------------

(1 row)

SET
SET
SET
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
psql:/tmp/dump.sql:71: ERROR:  function do_analyze() does not exist
LINE 1: SELECT $1 FROM do_analyze()
                        ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.
QUERY:  SELECT $1 FROM do_analyze()
CONTEXT:  SQL function "wrap_do_analyze" during inlining
db2=#

Workaround -

reset search_path ; before 'create index' statement in the dump.sql file .

-- 
regards,tushar
EnterpriseDB  https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

tushar <tushar.ahuja@enterprisedb.com> writes:
> While testing something else ,i found 1 scenario  where pg_dump  is failing

> CREATE FUNCTION do_analyze() RETURNS VOID VOLATILE LANGUAGE SQL
>          AS 'ANALYZE pg_am';
> CREATE FUNCTION wrap_do_analyze(c INT) RETURNS INT IMMUTABLE LANGUAGE SQL
>          AS 'SELECT $1 FROM do_analyze()';
> CREATE INDEX ON vaccluster(wrap_do_analyze(i));
> INSERT INTO vaccluster VALUES (1), (2);

You failed to schema-qualify the function reference.  That's not
a pg_dump bug.

While we're on the subject: this is an intentionally unsafe index.
The system doesn't try very hard to prevent you from lying about the
volatility status of a function ... but when, not if, it breaks
we're not going to regard the consequences as a Postgres bug.
Basically, there isn't anything about this example that I'm not
going to disclaim as "that's not supported".

            regards, tom lane