Обсуждение: BUG #4860: Indexes gone after restore

The following bug has been logged online:

Bug reference:      4860
Logged by:          Regina
Email address:      robe.dnd@cityofboston.gov
PostgreSQL version: 8.4rc1
Operating system:   Windows
Description:        Indexes gone after restore
Details:

This I think is an issue with prior versions as well, but was hoping it
would have been changed in this.

I'm not sure this is considered a bug, but it is annoying.  I dumped a 8.2
using pg_dump 8.4 and restored using pg_restore 8.4. Using 8.2 makes no
difference.

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.

Something like

CREATE INDEX idx_the_geom_2249_parceltime_2008
  ON assessing.parceltime_2008
  USING gist
  (st_transform(the_geom, 2249))
  WHERE the_geom IS NOT NULL;

I always have to manually recreate these.

I think someone else on PostGIS group complained about this a while ago.  I
think its because of the SET SCHEMA in the restore.

http://postgis.refractions.net/pipermail/postgis-users/2008-September/021393
.html

"Regina" <robe.dnd@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=#

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.=

"Obe, Regina" <robe.dnd@cityofboston.gov> writes:
> Your example seems to work fine for me too.  I'm trying to figure out the best way to give an isolated case without
requiringyou install a bunch of stuff. 

Well, the only two possibilities for an index not being restored are
(1) it's not listed in the dump file, or
(2) the CREATE INDEX command gets an error during the restore.

Which is it, and if (2) what's the error message?

            regards, tom lane

Okay seems to be a sequencing problem. Sorry I should have been paying atte=
ntion to the screen notices. =0D
=0D
I tried with a simpler db and this is what I get=0D
=0D
QUERY:  SELECT proj4text FROM spatial_ref_sys WHERE srid =3D 4326 LIMIT 1=
=0D
    Command was: CREATE INDEX assets_building_idx_the_geom_4326 ON building=
 USIN=0D
G gist (public.st_transform(the_geom, 4326));=0D
WARNING: errors ignored on restore: 1=0D
=0D
pg_restore: [archiver (db)] Error while PROCESSING TOC:=0D
pg_restore: [archiver (db)] Error from TOC entry 2612; 1259 27845 INDEX ass=
ets_b=0D
uilding_idx_the_geom_4326 postgres=0D
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "spat=
ial_r=0D
ef_sys" does not exist=0D
LINE 1: SELECT proj4text FROM spatial_ref_sys WHERE srid =3D 4326 LIMI...=
=0D
                              ^=0D
QUERY:  SELECT proj4text FROM spatial_ref_sys WHERE srid =3D 4326 LIMIT 1=
=0D
    Command was: CREATE INDEX assets_building_idx_the_geom_4326 ON building=
 USIN=0D
G gist (public.st_transform(the_geom, 4326));=0D
WARNING: errors ignored on restore: 1;=0D
=0D
Any suggestions on how to work around this issue?  I recall even when =0D
I have this table loaded before I restore, it still doesn't work.  So I thi=
nk=0D
its still the set schema issue in restore.=0D
=0D
=0D
=0D
-----Original Message-----=0D
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=0D
Sent: Thu 6/18/2009 1:59 PM=0D
To: Obe, Regina=0D
Cc: pgsql-bugs@postgresql.org=0D
Subject: Re: [BUGS] BUG #4860: Indexes gone after restore =0D
 =0D
"Obe, Regina" <robe.dnd@cityofboston.gov> writes:=0D
> 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.=0D
=0D
Well, the only two possibilities for an index not being restored are=0D
(1) it's not listed in the dump file, or=0D
(2) the CREATE INDEX command gets an error during the restore.=0D
=0D
Which is it, and if (2) what's the error message?=0D
=0D
            regards, tom lane=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.=

"Obe, Regina" <robe.dnd@cityofboston.gov> writes:
> I tried with a simpler db and this is what I get

> pg_restore: [archiver (db)] could not execute query: ERROR:  relation "spatial_ref_sys" does not exist
> LINE 1: SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMI...
>                               ^
> QUERY:  SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1
>     Command was: CREATE INDEX assets_building_idx_the_geom_4326 ON building USING gist (public.st_transform(the_geom,
4326));

Hum.  So the immediate problem is that st_transform() is failing to
schema-qualify its reference to spatial_ref_sys.  Think you need to
be filing that one against PostGIS, not us.


There's a bigger issue here too: pg_dump has absolutely no idea that
st_transform() has any such dependency, so it doesn't know it must
restore spatial_ref_sys (let alone put data into it) before creating
this index.  It's just luck that this works at all, independently of
schema considerations.

Not sure what to do about that.  Arguably, st_transform() is broken
to be designed this way: since it is dependent on the contents of a
database table, it is not really IMMUTABLE and shouldn't be used in
index definitions.  I doubt we'll try to enforce that against you,
but I don't immediately see a good way to express the dependency in
a way that would make this safe.  Something to think about when we
do the fabled module feature.

            regards, tom lane

> Hum.  So the immediate problem is that st_transform() is failing to=0D
> schema-qualify its reference to spatial_ref_sys.  Think you need to=0D
> be filing that one against PostGIS, not us.=0D
=0D
=0D
Yah was sort of thinking that. =0D
=0D
Though what about this -- isn't this case a bit more common=0D
=0D
http://postgis.refractions.net/pipermail/postgis-users/2008-September/02139=
3.html=0D
=0D
If you have an index on a function that calls another function where the fu=
nction being called does not have the namespace specifically prefixed.=0D
=0D
In his example he had an index on ST_GeometryType (which calls the older fu=
nction GeometryType both residing in the public schema)=0D
=0D
So here is a more trivial example:=0D
=0D
using your original=0D
=0D
CREATE OR REPLACE FUNCTION foo(integer)=0D
  RETURNS integer AS=0D
'select $1+1'=0D
  LANGUAGE 'sql' IMMUTABLE;=0D
=0D
 CREATE OR REPLACE FUNCTION foo2(integer)=0D
        RETURNS integer As=0D
        $$SELECT foo($1)$$=0D
        LANGUAGE 'sql' IMMUTABLE;=0D
=0D
CREATE SCHEMA s1;=0D
create table s1.t1 (f1 int);=0D
create index i1 on s1.t1 (foo2(f1));=0D
=0D
Try to back that up and then restore it.=0D
=0D
error=0D
pg_restore: [archiver (db)] Error from TOC entry 1769; 0 38023 TABLE DATA t=
1 pos=0D
tgres=0D
pg_restore: [archiver (db)] COPY failed: ERROR:  function foo(integer) does=
 not=0D
exist=0D
LINE 1: SELECT foo($1)=0D
               ^=0D
HINT:  No function matches the given name and argument types. You might nee=
d to=0D
add explicit type casts.=0D
QUERY:  SELECT foo($1)=0D
CONTEXT:  SQL function "foo2" during inlining=0D
pg_restore: [archiver (db)] Error from TOC entry 1768; 1259 38029 INDEX i1 =
postg=0D
res=0D
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "i1" =
alrea=0D
dy exists=0D
    Command was: CREATE INDEX i1 ON t1 USING btree (public.foo2(f1));=0D
WARNING: errors ignored on restore: 32=0D
=0D
Or do people not do this? I tend to a lot.=0D
=0D
=0D
=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.=

"Obe, Regina" <robe.dnd@cityofboston.gov> writes:
> If you have an index on a function that calls another function where the function being called does not have the
namespacespecifically prefixed. 

This has nothing to do with either indexes or pg_dump.  A function that
calls another function, or references a table, or does much of anything
at all with other database objects, is responsible for schema-qualifying
those references if it expects to be callable with random settings of
search_path.

In some cases the most practical way to do this is to attach a "SET
search_path" option to the function rather than change its source code.
But in either case it's ultimately the function author's responsibility.

            regards, tom lane