- 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).
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
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.
set schema to anything else,
select postgis_full_version() chucks an error- good old 'function does not exist'.
Talk about maddening.