System details:
- PostgreSQL 9.3.5 64bit on Windows 7 Ultimate;
- PostGIS 2.1.3
Two days ago I re-organised my schema structure - effectively moving a series of tables of the form vm[X]YYYYMM.[tablename]
(X∈[admin, prop,add,reftab, plan]) to vmYYYYMM.[tablename]
. There are no conflicting table names, so the reorganisation went off without a hitch.
This was done primarily to enable me to set schema
when doing intra-period operations - tidying data, adding indexes, setting foreign keys etc - and to enable some more flexible scripting approaches to data import/indexing.
The change - creating the new schemas (schemae? TNIRN) and moving tables en-masse to their new homes - was the only significant change to the database.
Today I tried to perform a standard query of the type that I have performed a thousand times in the old schema-prefixed setup:
select a.lga,
b.group3,
sum(ST_Area(a.g1::geography))/10000 as area_ha
from vmplan201506.plan_zone a
join vmplan201506.plan_codelist b
on a.zone_code = b.zone_code
group by a.lga, b.group3
order by a.lga, b.group3;
I ran that same query just now, within the vm201506 schema, as follows:
set schema 'vm201506';
select a.lga,
b.group3,
sum(ST_Area(a.g1::geography))/10000 as area_ha
from plan_zone a
join plan_codelist b
on a.zone_code = b.zone_code
group by a.lga, b.group3
order by a.lga, b.group3;
Result: "ERROR: type "geography" does not exist"
To test, I dropped the geography cast, and re-ran the query... this time 'ERROR: Function ST_Area() does not exist'. News to me...
If I add back the schema prefixes and set schema 'public'
, everything runs fine (ergo PostGIS exists, and both the geography cast and the area calc happen as expected).
Another oddity: select postgis_full_version()
returns an error (even within schema public
) - ERROR: function postgis_full_version() does not exist... but look at the graphic below - it's clearly installed (and I can run spatial queries so long as I am operating in the public
schema).
Anyone ever seen this sort of thing before? If so, was it remediable? (Note: restarting PostgreSQL didn't help).
I realise I could simply uninstall and reinstall PostGIS through Control Panel-> Programs and Features, but thought I would pose the question in case it's a symptom of something egregious. There's no indication of a pending disk failure.
UPDATE (no way to spend a Sunday afternoon)... So I took this opportunity to
- pg_dump all data (150GB... less than I thought it would be);
- upgrade PostgreSQL to 9.4.4;
- uninstall the old PostGIS;
- install new 9.4.4-compatible PostGIS (2.1.8);
- set up a fresh new database instance;
- postgis-ify it;
- psql \i the data
Everything went off without a hitch.
EXCEPT ... the problem still exists in a very slightly modified form.
In 9.3.5 I got an error - even with set schema 'public'
for select postgis_full_version()
. Now I get the output I expect - namely
POSTGIS="2.1.8 r13780" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER
Seems like progress, yeah? Not so fast.
If I set schema
to anything else, select postgis_full_version()
chucks an error- good old 'function does not exist'.
Talk about maddening.