ST_Distance(geography, geography) fails when schema name contains hyphen character

[kunszabo67]

ST_Distance(geography, geography) fails with a SQL syntax error when the PostGIS extension is installed in a schema whose name must be quoted because it contains a hyphen charater.

Reproduction:

Install the extension in a schema that has a quoted name that includes a hyphen character:

SET search_path TO public;
CREATE SCHEMA "schema-with-hyphen";
CREATE EXTENSION postgis WITH SCHEMA "schema-with-hyphen";
SET search_path TO public, "schema-with-hyphen";

The problem occurs in the ST_Distance function with geography parameters:

SELECT 'POINT (1 1)'::geography, 'POINT (1 2)'::geography;
-- returns: POINT (1 1), POINT (1 2)

SELECT ST_DISTANCE('POINT (1 1)'::geography, 'POINT (1 2)'::geography);
-- SQL Error [42601]: ERROR: syntax error at or near "-"

The variant with geometry parameters works as expected:

SELECT 'POINT (1 1)'::geometry, 'POINT (1 2)'::geometry
-- returns: POINT (1 1), POINT (1 2)

SELECT ST_DISTANCE('POINT (1 1)'::geometry, 'POINT (1 2)'::geometry);
-- returns 1

The problem seems to be present in versions 2.5.0 and later.

[kunszabo67]

PostgreSQL logs show that the failing statement is: SELECT proj4text, auth_name, auth_srid, srtext FROM quoted-schema.spatial_ref_sys WHERE srid = 4326 LIMIT 1

The qualifying schema name is not quoted properly.

[Algunenano]

Something like ​https://github.com/postgis/postgis/pull/553 should fix it

With that patch:

schematest=# create database schema2;
CREATE DATABASE
schematest=# \c schema2
You are now connected to database "schema2" as user "postgres".
schema2=#  SET search_path TO public;
SET
schema2=#  CREATE SCHEMA "schema-with-hyphen";
CREATE SCHEMA
schema2=#  CREATE EXTENSION postgis WITH SCHEMA "schema-with-hyphen";
CREATE EXTENSION
schema2=#  SET search_path TO public, "schema-with-hyphen";
SET
schema2=#  SELECT 'POINT (1 1)'::geography, 'POINT (1 2)'::geography;
                     geography                      |                     geography                      
----------------------------------------------------+----------------------------------------------------
 0101000020E6100000000000000000F03F000000000000F03F | 0101000020E6100000000000000000F03F0000000000000040
(1 row)

schema2=#  SELECT ST_DISTANCE('POINT (1 1)'::geography, 'POINT (1 2)'::geography);
   st_distance   
-----------------
 110575.06481434
(1 row)

[Algunenano]

There are still a couple of other places that call get_namespace_name so I think they should be merged (in fact we already have a constant that we should be using constants->install_nsp) and fix the issue everywhere.

[Algunenano]

There are also multiple issues when spatial_ref_sys is not in the search_path:

Select "schema-with-hyphen".ST_AsGeoJSON('SRID=3827; POINT(1 1)'::"schema-with-hyphen".geometry);
ERROR:  relation "spatial_ref_sys" does not exist
LINE 1: SELECT auth_name||':'||auth_srid           FROM spatial_ref_...
                                                        ^
QUERY:  SELECT auth_name||':'||auth_srid                        FROM spatial_ref_sys WHERE srid='3827'

The following C functions are either not adding the schema or adding it without quotes:

• getSRSbySRID
• getSRIDbySRS
• gml_is_srid_planar
• rtpg_getSR
• SetSpatialRefSysSchema → GetProjStringsSPI
• getPostgisConstants (unused)

Also, as Komzpa alerted, this will now break if the schema has quotes in it, so we should find the proper way to fully escape the identifier and then ensure all functions use it correctly.

[Algunenano]

I've fixed it in the core extension by storing the full qualified table name in POSTGIS_CONSTANTS and using it wherever it's needed.

I've checked raster SPI_execute calls and found several issues (_st_summarystats being called directly without schema, and the same for spatial_ref_sys) but the code, specially around mapalgebra is far from clear. Not only that, but the internal types in the SQL code aren't qualified either, so there is quite a bit to do if you want to really run raster in a different schema and support FDW. I'm just going to open a different issue to track for whoever is interested on the feature to handle.

[Raúl Marín <git@…>]

In b48fb3e/git:

Fix access to spatial_ref_sys with a non default schema

References #4661
Closes ​https://github.com/postgis/postgis/pull/553

[Raúl Marín <git@…>]

In efafa58/git:

Fix access to spatial_ref_sys with a non default schema

References #4661

[Raúl Marín <git@…>]

In 526ba3d/git:

Fix access to spatial_ref_sys with a non default schema

References #4661

[Raúl Marín <git@…>]

In 40015c93/git:

Fix access to spatial_ref_sys with a non default schema

Closes #4661