Re: BUG #4860: Indexes gone after restore

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