[postgis-users] Issues using PostGIS in other schema

[Regina Obe]

The postgis functions are schema qualified, so simply changing the path will not work.

 

What you need to do after you edit the system tables and move the extension is to do the below (replacing with your version of PostGIS)

 

ALTER EXTENSION postgis UPDATE TO "3.0.0next";

ALTER EXTENSION postgis UPDATE TO "3.0.0";

 

That will force a reupgrade which will rerun install of the functions with the new schema you set.

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Pieter Provoost
Sent: Saturday, January 25, 2020 3:39 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Issues using PostGIS in other schema

 

Hi all,

 

I have issues using PostGIS after moving it to a different schema. In many but not all cases I get a "function does not exist" error with a reference to the public schema which isn't even on my search path.

 

I followed these steps to set up PostGIS:

 

docker run --name test -e POSTGRES_PASSWORD=test -e POSTGRES_USER=test -e POSTGRES_DATABASE=test -d mdillon/postgis
docker exec -it test /bin/bash
psql -U test test
create schema test;
alter schema test owner to test;
update pg_extension set extrelocatable = true where extname = 'postgis';
alter extension postgis set schema test;
set search_path = test;

 

I'm expecting to be able to use PostGIS at this point, but postgis_full_version() for example fails with:

 

ERROR:  function public.postgis_lib_version() does not exist

 

This on the other hand works fine:

 

SELECT ST_SnapToGrid(
    ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 2.3456 1.11111,4.111111 3.2374897 3.1234 1.1111, -1.11111112 2.123 2.3456 1.1111112)'),
    ST_GeomFromEWKT('POINT(1.12 2.22 3.2 4.4444)'),
    0.1, 0.1, 0.1, 0.01);

 

But this (from the PostGIS docs as well) doesn't:

 

SELECT ST_SnapToGrid(
    ST_GeomFromText('LINESTRING(1.1115678 2.123, 4.111111 3.2374897, 4.11112 3.23748667)'),
    0.001);
ERROR:  function public.st_snaptogrid(geometry, integer, integer, double precision, double precision) does not exist
LINE 1: SELECT public.ST_SnapToGrid($1, 0, 0, $2, $2)

 

Any thoughts?

 

Best regards

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200125/9122b2d9/attachment.html>