Re: BUG #4860: Indexes gone after restore

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