This has been bugging me for a long time and I finally complained about it and Tom Lane kindly gave a reason for the problem and that its by design and not a bug.
So I thought I would post the situation here without getting into too many embarassing specifics in case others have suffered from a similar fate and can learn from this.
The situation:
Your super nifty function is doing its thing; your table is happy; the planner is spitting out your queries lightning fast using the super nifty index on your super table; The world is good.
One day you decide to restore your nifty database backup and to your chagrin, your nifty index is not there. The planner is no longer happily spitting out your queries lighting fast and everything has come to a painful crawl. Your super nifty index is gone. What happened to super nifty functional index?
I have to admit that I'm the type of person that assumes the public schema is always there and always in search_path and that my assumption is a flawed one. After all the public schema is there by default on new databases for convenience, but one can change it not to be in the search_path and in fact pg_dump does just that. So if everything you have is kept in public schema -- you don't run into this particular misfortune. If however you have your functions in public and your tables in different schemas, during restore -- the search path is changed to the schema being restored and your super functional indexes based on super functions that depend on other super functions fail because public is no longer in the search_path.
Below is a simple script to recreate the issue so its clear:
CREATE DATABASE superdata;
CREATE OR REPLACE FUNCTION myniftyfunc(myint integer) RETURNS integer AS
$$ SELECT 1 + $1;$$
LANGUAGE 'sql' IMMUTABLE;
CREATE OR REPLACE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer AS
$$ SELECT myniftyfunc($1); $$
LANGUAGE 'sql' IMMUTABLE;
CREATE SCHEMA mysuperdata;
CREATE TABLE mysuperdata.mysupertable(sid integer PRIMARY KEY, super_key integer);
CREATE INDEX idx_mysupertable_super_index
ON mysuperdata.mysupertable USING btree (mysuperniftyfunc(super_key));
INSERT INTO mysuperdata.mysupertable(sid,super_key)
VALUES(1,1);
--Backup superdata
"C:\Program files\postgresql\8.3\bin\pg_dump" --host=localhost --port=5432 --username=postgres --format=plain --verbose --file="C:\superdata.sql" superdata
--Restore
"C:\Program files\postgresql\8.3\bin\psql" -U postgres -h localhost -p 5432 -d superdata2 -f "C:\superduper.sql"
--Get non-super cool error
psql:C:/superduper.sql:99: ERROR: function myniftyfunc(integer) does not exist
LINE 1: SELECT myniftyfunc($1);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT myniftyfunc($1);
CONTEXT: SQL function "mysuperniftyfunc" during startup
Normally I do my backup in compressed format, but did it in plain to demonstrate the problem and here is what pg_dump produces.
--
-- PostgreSQL database dump
--
-- Started on 2009-06-18 21:45:59
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 6 (class 2615 OID 1086067)
-- Name: mysuperdata; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA mysuperdata;
ALTER SCHEMA mysuperdata OWNER TO postgres;
SET search_path = public, pg_catalog;
--
-- TOC entry 21 (class 1255 OID 1086065)
-- Dependencies: 3
-- Name: myniftyfunc(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION myniftyfunc(myint integer) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $_$ SELECT 1 + $1;$_$;
ALTER FUNCTION public.myniftyfunc(myint integer) OWNER TO postgres;
--
-- TOC entry 22 (class 1255 OID 1086066)
-- Dependencies: 3
-- Name: mysuperniftyfunc(integer); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $_$ SELECT myniftyfunc($1); $_$;
ALTER FUNCTION public.mysuperniftyfunc(myint integer) OWNER TO postgres;
SET search_path = mysuperdata, pg_catalog;
-- this is not a bug, but would be super
cool if public or whatever the default search path of the database was in here.
SET default_tablespace = '';
SET default_with_oids = false;
--
-- TOC entry 1465 (class 1259 OID 1086068)
-- Dependencies: 6
-- Name: mysupertable; Type: TABLE; Schema: mysuperdata; Owner: postgres; Tablespace:
--
CREATE TABLE mysupertable (
sid integer NOT NULL,
super_key integer
);
ALTER TABLE mysuperdata.mysupertable OWNER TO postgres;
--
-- TOC entry 1735 (class 0 OID 1086068)
-- Dependencies: 1465
-- Data for Name: mysupertable; Type: TABLE DATA; Schema: mysuperdata; Owner: postgres
--
COPY mysupertable (sid, super_key) FROM stdin;
1 1
\.
--
-- TOC entry 1734 (class 2606 OID 1086072)
-- Dependencies: 1465 1465
-- Name: mysupertable_pkey; Type: CONSTRAINT; Schema: mysuperdata; Owner: postgres; Tablespace:
--
ALTER TABLE ONLY mysupertable
ADD CONSTRAINT mysupertable_pkey PRIMARY KEY (sid);
--
-- TOC entry 1732 (class 1259 OID 1086073)
-- Dependencies: 22 1465
-- Name: idx_mysupertable_super_index; Type: INDEX; Schema: mysuperdata; Owner: postgres; Tablespace:
--
CREATE INDEX idx_mysupertable_super_index ON mysupertable USING btree (public.mysuperniftyfunc(super_key));
--
-- TOC entry 1740 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
-- Completed on 2009-06-18 21:45:59
--
-- PostgreSQL database dump complete
--
Solution (workaround):
The work around for this situation is to either explicitly qualify the functions you are using within another or for PostgreSQL 8.3 -- add to your function set search_path=public
Solution 1:CREATE OR REPLACE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer AS $$ SELECT public.myniftyfunc($1); $$ LANGUAGE 'sql' IMMUTABLE;Solution 2: (only works for PostgreSQL 8.3+)
CREATE OR REPLACE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer AS $$ SELECT myniftyfunc($1); $$ LANGUAGE 'sql' IMMUTABLE; ALTER FUNCTION mysuperniftyfunc(integer) SET search_path=public;
Of course neither of these solutions is particularly satisfying if you are a package author. If you are and that is how this mess started. You want people to be able to install your functions in whatever schema they like and if they wanted to use it globally they would add it to their database default search path. Though that is arguable. Perhaps all packages should live in specifically named schemas.