PostgreSQL 'forgot' PostGIS functionality

Question

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.

Answer 1

You need to make sure your set search_path call always includes the schema postgis is installed in.

So instead of doing:

SET search_path=vmplan201506;

Make sure you are doing

SET search_path=vmplan201506,public,tiger;

If you have other tables in public you don't want seen, then move postgis into another schema and make sure that schema is always included.

My favorite is postgis

CREATE SCHEMA postgis;
ALTER EXTENSION postgis SET SCHEMA postgis;
ALTER DATABASE mydb SET search_path=vmplan201506,postgis,tiger;