[postgis-tickets] r15383 - @ operator not schema qualified and ~ not schema qualified in some places

[Regina Obe]

Author: robe
Date: 2017-05-02 20:50:38 -0700 (Tue, 02 May 2017)
New Revision: 15383

Modified:
   branches/2.3/NEWS
   branches/2.3/postgis/postgis.sql.in
   branches/2.3/raster/rt_pg/rtpostgis.sql.in
Log:
@ operator not schema qualified and ~ not schema qualified in some places
apply patch with adjustments. Patch provided by Shane StClair (Axiom Data Science)
Closes #3750 for PostGIS 2.3.3

Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS	2017-05-03 03:12:37 UTC (rev 15382)
+++ branches/2.3/NEWS	2017-05-03 03:50:38 UTC (rev 15383)
@@ -1,5 +1,5 @@
 PostGIS 2.3.3dev
-YYYY/MM/DD
+2017/MM/DD
 
   * Bug Fixes
 
@@ -10,6 +10,9 @@
            raster data instead of setting srid
   - #3744, ST_Subdivide loses subparts of inverted geometries
            (Darafei Praliaskouski Komzpa)
+  - #3750, @ and ~ operator not always schema qualified in geometry 
+           and raster functions. Causes restore issues.
+           (Shane StClair of Axiom Data Science)
            
 
 PostGIS 2.3.2

Modified: branches/2.3/postgis/postgis.sql.in
===================================================================
--- branches/2.3/postgis/postgis.sql.in	2017-05-03 03:12:37 UTC (rev 15382)
+++ branches/2.3/postgis/postgis.sql.in	2017-05-03 03:50:38 UTC (rev 15383)
@@ -4061,7 +4061,7 @@
 -- Availability: 1.2.2
 CREATE OR REPLACE FUNCTION ST_CoveredBy(geom1 geometry, geom2 geometry)
 	RETURNS boolean
-	AS 'SELECT $1 @ $2 AND @extschema at ._ST_CoveredBy($1,$2)'
+	AS 'SELECT $1 OPERATOR(@extschema at .@) $2 AND @extschema at ._ST_CoveredBy($1,$2)'
 	LANGUAGE 'sql' IMMUTABLE _PARALLEL;
 
 -- Availability: 1.2.2

Modified: branches/2.3/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.3/raster/rt_pg/rtpostgis.sql.in	2017-05-03 03:12:37 UTC (rev 15382)
+++ branches/2.3/raster/rt_pg/rtpostgis.sql.in	2017-05-03 03:50:38 UTC (rev 15383)
@@ -5718,7 +5718,7 @@
 
 CREATE OR REPLACE FUNCTION raster_contained(raster, raster)
     RETURNS bool
-    AS 'select $1::geometry @ $2::geometry'
+    AS 'select $1::geometry OPERATOR(@extschema at .@) $2::geometry'
     LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
 
 CREATE OR REPLACE FUNCTION raster_contain(raster, raster)
@@ -5742,7 +5742,7 @@
 -- Availability: 2.0.5
 CREATE OR REPLACE FUNCTION raster_contained_by_geometry(raster, geometry)
     RETURNS bool
-    AS 'select $1::geometry @ $2'
+    AS 'select $1::geometry OPERATOR(@extschema at .@) $2'
     LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
 
 -- Availability: 2.0.0
@@ -5756,13 +5756,13 @@
 -- Availability: 2.0.0
 CREATE OR REPLACE FUNCTION geometry_raster_contain(geometry, raster)
     RETURNS bool
-    AS 'select $1 ~ $2::geometry'
+    AS 'select $1 OPERATOR(@extschema at .~) $2::geometry'
     LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
 
 -- Availability: 2.0.5
 CREATE OR REPLACE FUNCTION geometry_contained_by_raster(geometry, raster)
     RETURNS bool
-    AS 'select $1 @ $2::geometry'
+    AS 'select $1 OPERATOR(@extschema at .@) $2::geometry'
     LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL;
 
 -- Availability: 2.0.0
@@ -7146,34 +7146,28 @@
 		END IF;
 		fqtn := fqtn || quote_ident($2);
 
-		sql := 'SELECT MAX(@extschema at .ST_SRID('
+		sql := 'SELECT @extschema at .ST_SRID('
             || quote_ident($3)
-      || ') ) FROM '
+      || ') FROM '
             || fqtn
-            || ' HAVING COUNT(DISTINCT @extschema at .ST_SRID('
-            || quote_ident($3)
-      || ') ) = 1';
+            || ' LIMIT 1;';
     EXECUTE sql INTO srid;
     
 		cn := 'enforce_max_extent_' || $3;
 
-		IF srid > -1 THEN
-      sql := 'SELECT @extschema at .st_ashexewkb( @extschema at .st_setsrid( @extschema at .st_extent( @extschema at .st_envelope('
-        || quote_ident($3)
-        || ')), ' || srid || ')) FROM '
-        || fqtn;
-      EXECUTE sql INTO attr;
-  
-      sql := 'ALTER TABLE ' || fqtn
-        || ' ADD CONSTRAINT ' || quote_ident(cn)
-        || ' CHECK ( @extschema at .st_envelope('
-        || quote_ident($3)
-        || ') @ ''' || attr || '''::geometry) ';
-      RETURN  @extschema at ._add_raster_constraint(cn, sql);
-    ELSE 
-      -- no valid srid therefore doesn't make sense to set extent
-      RETURN false;
-    END IF;
+		sql := 'SELECT @extschema at .st_ashexewkb( @extschema at .st_setsrid( @extschema at .st_extent( @extschema at .st_envelope('
+			|| quote_ident($3)
+			|| ')), ' || srid || ')) FROM '
+			|| fqtn;
+		EXECUTE sql INTO attr;
+
+		-- NOTE: I put NOT VALID to prevent the costly step of validating the constraint
+		sql := 'ALTER TABLE ' || fqtn
+			|| ' ADD CONSTRAINT ' || quote_ident(cn)
+			|| ' CHECK ( @extschema at .st_envelope('
+			|| quote_ident($3)
+			|| ') @ ''' || attr || '''::geometry) NOT VALID';
+		RETURN  @extschema at ._add_raster_constraint(cn, sql);
 	END;
 	$$ LANGUAGE 'plpgsql' VOLATILE STRICT
 	COST 9000;