Re: BUG #4860: Indexes gone after restore

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:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thu 6/18/2009 12:53 PM
To: Obe, Regina
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4860: Indexes gone after restore

"Regina" <robe(dot)dnd(at)cityofboston(dot)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.