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,
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@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.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=#
-----------------------------------------
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.
"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 requiring you 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 attention to the screen notices.
I tried with a simpler db and this is what I get
QUERY: SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1
Command was: CREATE INDEX assets_building_idx_the_geom_4326 ON building USIN
G gist (public.st_transform(the_geom, 4326));
WARNING: errors ignored on restore: 1
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2612; 1259 27845 INDEX assets_b
uilding_idx_the_geom_4326 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: relation "spatial_r
ef_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 USIN
G gist (public.st_transform(the_geom, 4326));
WARNING: errors ignored on restore: 1;
Any suggestions on how to work around this issue? I recall even when
I have this table loaded before I restore, it still doesn't work. So I think
its still the set schema issue in restore.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thu 6/18/2009 1:59 PM
To: Obe, Regina
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4860: Indexes gone after restore
"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 requiring you 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
-----------------------------------------
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.
"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
schema-qualify its reference to spatial_ref_sys. Think you need to
be filing that one against PostGIS, not us.
Yah was sort of thinking that.
Though what about this -- isn't this case a bit more common
http://postgis.refractions.net/pipermail/postgis-users/2008-September/021393.html
If you have an index on a function that calls another function where the function being called does not have the namespace specifically prefixed.
In his example he had an index on ST_GeometryType (which calls the older function GeometryType both residing in the public schema)
So here is a more trivial example:
using your original
CREATE OR REPLACE FUNCTION foo(integer)
RETURNS integer AS
'select $1+1'
LANGUAGE 'sql' IMMUTABLE;
CREATE OR REPLACE FUNCTION foo2(integer)
RETURNS integer As
$$SELECT foo($1)$$
LANGUAGE 'sql' IMMUTABLE;
CREATE SCHEMA s1;
create table s1.t1 (f1 int);
create index i1 on s1.t1 (foo2(f1));
Try to back that up and then restore it.
error
pg_restore: [archiver (db)] Error from TOC entry 1769; 0 38023 TABLE DATA t1 pos
tgres
pg_restore: [archiver (db)] COPY failed: ERROR: function foo(integer) does not
exist
LINE 1: SELECT foo($1)
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
QUERY: SELECT foo($1)
CONTEXT: SQL function "foo2" during inlining
pg_restore: [archiver (db)] Error from TOC entry 1768; 1259 38029 INDEX i1 postg
res
pg_restore: [archiver (db)] could not execute query: ERROR: relation "i1" alrea
dy exists
Command was: CREATE INDEX i1 ON t1 USING btree (public.foo2(f1));
WARNING: errors ignored on restore: 32
Or do people not do this? I tend to a lot.
-----------------------------------------
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.
"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 namespace specifically 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