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

[Regina Obe]

Author: robe
Date: 2016-11-26 10:54:17 -0800 (Sat, 26 Nov 2016)
New Revision: 15254

Modified:
   branches/2.2/NEWS
   branches/2.2/raster/rt_pg/rtpostgis.sql.in
Log:
add raster constraint max extent exceeds array size limit
closes #3501 for 2.2.4 

Modified: branches/2.2/NEWS
===================================================================
--- branches/2.2/NEWS	2016-11-26 16:39:38 UTC (rev 15253)
+++ branches/2.2/NEWS	2016-11-26 18:54:17 UTC (rev 15254)
@@ -6,6 +6,8 @@
  - #1973, st_concavehull() returns sometimes empty geometry collection
           Fix from gde
  - #3656, Fix upgrade of aggregates from 2.2 or lower version
+ - #3501, add raster constraint max extent exceeds array size limit
+        for large tables
  - #3659, Crash caused by raster GUC define after CREATE EXTENSION
      using wrong memory context. (manaeem)
 

Modified: branches/2.2/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.2/raster/rt_pg/rtpostgis.sql.in	2016-11-26 16:39:38 UTC (rev 15253)
+++ branches/2.2/raster/rt_pg/rtpostgis.sql.in	2016-11-26 18:54:17 UTC (rev 15254)
@@ -7138,28 +7138,42 @@
 		fqtn text;
 		cn name;
 		sql text;
-		attr text;
+		attr text; srid integer;
 	BEGIN
 		fqtn := '';
 		IF length($1) > 0 THEN
 			fqtn := quote_ident($1) || '.';
 		END IF;
 		fqtn := fqtn || quote_ident($2);
+		
+		sql := 'SELECT MAX(ST_SRID('
+				|| quote_ident($3)
+		|| ') ) FROM '
+				|| fqtn
+				|| ' HAVING COUNT(DISTINCT ST_SRID('
+				|| quote_ident($3)
+		|| ') ) = 1';
+		EXECUTE sql INTO srid;
 
 		cn := 'enforce_max_extent_' || $3;
 
-		sql := 'SELECT st_ashexewkb(st_envelope(st_union(st_envelope('
-			|| quote_ident($3)
-			|| ')))) FROM '
-			|| fqtn;
-		EXECUTE sql INTO attr;
-
-		sql := 'ALTER TABLE ' || fqtn
-			|| ' ADD CONSTRAINT ' || quote_ident(cn)
-			|| ' CHECK (st_envelope('
-			|| quote_ident($3)
-			|| ') @ ''' || attr || '''::geometry)';
-		RETURN _add_raster_constraint(cn, sql);
+		IF srid > -1 THEN
+			sql := 'SELECT st_ashexewkb( st_setsrid( st_extent( st_envelope('
+				|| quote_ident($3)
+				|| ')), ' || srid || ')) FROM '
+				|| fqtn;
+			EXECUTE sql INTO attr;
+		
+			sql := 'ALTER TABLE ' || fqtn
+				|| ' ADD CONSTRAINT ' || quote_ident(cn)
+				|| ' CHECK ( st_envelope('
+				|| quote_ident($3)
+				|| ') @ ''' || attr || '''::geometry) ';
+			RETURN  _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;