Обсуждение: 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