Tom, Your example seems to work fine for me too. I'm trying to figure out the best way to give an isolated case without requiring you install a bunch of stuff.
The only thing I can think of that is different about my case. Is one its using a gist index instead of btree and my operator classes are defined in public (they are all against PostGIS geometry) as well (not in pgcatalog). The ones that are plain gist (no functional) work fine Its just the ones built on ST_Transform(...) that never seem to come back. Anyrate having some other people on PostGIS dev try this and see if they have similar issues or if its just something about my config. Thanks, Regina -----Original Message----- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thu 6/18/2009 12:53 PM To: Obe, Regina Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #4860: Indexes gone after restore "Regina" <robe....@cityofboston.gov> writes: > If I have a functional index in place in a table in non-public schema that > uses a function in public schema, these indexes never get restored. Works for me (per attached). Please provide a *complete* example and not an abstraction. regards, tom lane $ psql regression psql (8.4rc1) Type "help" for help. regression=# create database test; CREATE DATABASE regression=# \c test psql (8.4rc1) You are now connected to database "test". test=# create function foo(int) returns int as 'select $1+1' language sql test-# strict immutable; CREATE FUNCTION test=# create schema s1; CREATE SCHEMA test=# create table s1.t1 (f1 int); CREATE TABLE test=# create index i1 on s1.t1 (foo(f1)); CREATE INDEX test=# \d s1.t1 Table "s1.t1" Column | Type | Modifiers --------+---------+----------- f1 | integer | Indexes: "i1" btree (foo(f1)) test=# \q $ pg_dump test >test.dump $ psql regression psql (8.4rc1) Type "help" for help. regression=# create database test2; CREATE DATABASE regression=# \c test2 psql (8.4rc1) You are now connected to database "test2". test2=# \i test.dump SET SET SET SET SET SET CREATE SCHEMA ALTER SCHEMA SET CREATE FUNCTION ALTER FUNCTION SET SET SET CREATE TABLE ALTER TABLE CREATE INDEX REVOKE REVOKE GRANT GRANT test2=# \d s1.t1 Table "s1.t1" Column | Type | Modifiers --------+---------+----------- f1 | integer | Indexes: "i1" btree (public.foo(f1)) test2=# ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
