Re: [pg_dump] 'create index' statement is failing due to search_path is empty
tushar 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
[pg_dump] 'create index' statement is failing due to search_path is empty
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