[postgis-tickets] r15245 - add raster constraint max extent exceeds array size limit

[Regina Obe]

Author: robe
Date: 2016-11-24 17:05:14 -0800 (Thu, 24 Nov 2016)
New Revision: 15245

Modified:
   branches/2.3/NEWS
   branches/2.3/raster/rt_pg/rtpostgis.sql.in
Log:
add raster constraint max extent exceeds array size limit
references #3501 for 2.3 (PostGIS 2.3.1)

Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS	2016-11-23 07:34:29 UTC (rev 15244)
+++ branches/2.3/NEWS	2016-11-25 01:05:14 UTC (rev 15245)
@@ -4,6 +4,8 @@
   * Bug Fixes
   - #1973, st_concavehull() returns sometimes empty geometry collection
            Fix from gde
+  - #3501, add raster constraint max extent exceeds array size limit
+        for large tables
   - #3643, PostGIS not building on latest OSX XCode
   - #3644, Deadlock on interrupt
   - #3652, Crash on Collection(MultiCurve())

Modified: branches/2.3/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.3/raster/rt_pg/rtpostgis.sql.in	2016-11-23 07:34:29 UTC (rev 15244)
+++ branches/2.3/raster/rt_pg/rtpostgis.sql.in	2016-11-25 01:05:14 UTC (rev 15245)
@@ -7138,7 +7138,7 @@
 		fqtn text;
 		cn name;
 		sql text;
-		attr text;
+		attr text; srid integer;
 	BEGIN
 		fqtn := '';
 		IF length($1) > 0 THEN
@@ -7146,23 +7146,37 @@
 		END IF;
 		fqtn := fqtn || quote_ident($2);
 
+		sql := 'SELECT MAX(@extschema at .ST_SRID('
+            || quote_ident($3)
+      || ') ) FROM '
+            || fqtn
+            || ' HAVING COUNT(DISTINCT @extschema at .ST_SRID('
+            || quote_ident($3)
+      || ') ) = 1';
+    EXECUTE sql INTO srid;
+    
 		cn := 'enforce_max_extent_' || $3;
 
-		sql := 'SELECT @extschema at .st_ashexewkb( @extschema at .st_envelope( @extschema at .st_union( @extschema at .st_envelope('
-			|| quote_ident($3)
-			|| ')))) 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);
+		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;
 	END;
 	$$ LANGUAGE 'plpgsql' VOLATILE STRICT
-	COST 100;
+	COST 9000;
 
 CREATE OR REPLACE FUNCTION _drop_raster_constraint_extent(rastschema name, rasttable name, rastcolumn name)
 	RETURNS boolean AS