[postgis-tickets] r17936 - Fix AddRasterConstraints in presence of null geoms in table

[Sandro Santilli]

Author: strk
Date: 2019-10-17 07:10:17 -0700 (Thu, 17 Oct 2019)
New Revision: 17936

Modified:
   branches/2.3/raster/rt_pg/rtpostgis.sql.in
   branches/2.3/raster/test/regress/tickets.sql
   branches/2.3/raster/test/regress/tickets_expected
Log:
Fix AddRasterConstraints in presence of null geoms in table

References #4547 in 2.3 branch

Modified: branches/2.3/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.3/raster/rt_pg/rtpostgis.sql.in	2019-10-17 11:46:34 UTC (rev 17935)
+++ branches/2.3/raster/rt_pg/rtpostgis.sql.in	2019-10-17 14:10:17 UTC (rev 17936)
@@ -7146,24 +7146,28 @@
 		attr text; srid integer;
 	BEGIN
 		fqtn := '';
-		IF length($1) > 0 THEN
-			fqtn := quote_ident($1) || '.';
+		IF length(rastschema) > 0 THEN
+			fqtn := quote_ident(rastschema) || '.';
 		END IF;
-		fqtn := fqtn || quote_ident($2);
+		fqtn := fqtn || quote_ident(rasttable);
 
 		sql := 'SELECT @extschema at .ST_SRID('
-			|| quote_ident($3)
+			|| quote_ident(rastcolumn)
 			|| ') FROM '
 			|| fqtn
 			|| ' WHERE '
-			|| quote_ident($3)
+			|| quote_ident(rastcolumn)
 			|| ' IS NOT NULL LIMIT 1;';
                 EXECUTE sql INTO srid;
 
-		cn := 'enforce_max_extent_' || $3;
+    IF srid IS NULL THEN
+      RETURN false;
+    END IF;
 
+		cn := 'enforce_max_extent_' || rastcolumn;
+
 		sql := 'SELECT @extschema at .st_ashexewkb( @extschema at .st_setsrid( @extschema at .st_extent( @extschema at .st_envelope('
-			|| quote_ident($3)
+			|| quote_ident(rastcolumn)
 			|| ')), ' || srid || ')) FROM '
 			|| fqtn;
 		EXECUTE sql INTO attr;
@@ -7172,7 +7176,7 @@
 		sql := 'ALTER TABLE ' || fqtn
 			|| ' ADD CONSTRAINT ' || quote_ident(cn)
 			|| ' CHECK ( @extschema at .st_envelope('
-			|| quote_ident($3)
+			|| quote_ident(rastcolumn)
 			|| ') @ ''' || attr || '''::@extschema at .geometry) NOT VALID';
 		RETURN  @extschema at ._add_raster_constraint(cn, sql);
 	END;

Modified: branches/2.3/raster/test/regress/tickets.sql
===================================================================
--- branches/2.3/raster/test/regress/tickets.sql	2019-10-17 11:46:34 UTC (rev 17935)
+++ branches/2.3/raster/test/regress/tickets.sql	2019-10-17 14:10:17 UTC (rev 17936)
@@ -125,4 +125,13 @@
 INSERT INTO table_4308(r) values (NULL);
 INSERT INTO table_4308(r) SELECT ST_AddBand(ST_MakeEmptyRaster(10, 10, 1, 1, 2, 2, 0, 0,4326), 1, '8BSI'::text, -129, NULL);;
 SELECT AddRasterConstraints('table_4308', 'r');
-DROP TABLE table_4308;
\ No newline at end of file
+DROP TABLE table_4308;
+
+
+-- #4547
+CREATE TABLE ticket_4547(r raster);
+SELECT '#4547.1', AddRasterConstraints('ticket_4547', 'r');
+INSERT INTO ticket_4547 VALUES (null);
+INSERT INTO ticket_4547 SELECT ST_AddBand(ST_MakeEmptyRaster(10, 10, 1, 1, 2, 2, 0, 0,4326), 1, '8BSI'::text, -129, NULL);
+SELECT '#4547.2', AddRasterConstraints('ticket_4547', 'r');
+DROP TABLE ticket_4547;

Modified: branches/2.3/raster/test/regress/tickets_expected
===================================================================
--- branches/2.3/raster/test/regress/tickets_expected	2019-10-17 11:46:34 UTC (rev 17935)
+++ branches/2.3/raster/test/regress/tickets_expected	2019-10-17 14:10:17 UTC (rev 17936)
@@ -26,3 +26,42 @@
 NOTICE:  Adding out-of-database constraint
 NOTICE:  Adding maximum extent constraint
 t
+NOTICE:  Adding SRID constraint
+NOTICE:  Adding scale-X constraint
+NOTICE:  Adding scale-Y constraint
+NOTICE:  Adding blocksize-X constraint
+NOTICE:  Unable to add constraint: enforce_width_r
+NOTICE:  SQL used for failed constraint: ALTER TABLE public.ticket_4547 ADD CONSTRAINT enforce_width_r CHECK (st_width(r) IN ())
+NOTICE:  Returned error message: syntax error at or near ")" (42601)
+WARNING:  Unable to add constraint: 'blocksize_x'.  Skipping
+NOTICE:  Adding blocksize-Y constraint
+NOTICE:  Unable to add constraint: enforce_height_r
+NOTICE:  SQL used for failed constraint: ALTER TABLE public.ticket_4547 ADD CONSTRAINT enforce_height_r CHECK (st_height(r) IN ())
+NOTICE:  Returned error message: syntax error at or near ")" (42601)
+WARNING:  Unable to add constraint: 'blocksize_y'.  Skipping
+NOTICE:  Adding alignment constraint
+NOTICE:  Adding number of bands constraint
+NOTICE:  Adding pixel type constraint
+NOTICE:  Unable to get the pixel types of a sample raster (max < 1 or null)
+WARNING:  Unable to add constraint: 'pixel_types'.  Skipping
+NOTICE:  Adding nodata value constraint
+NOTICE:  Unable to get the nodata values of a sample raster (max < 1 or null)
+WARNING:  Unable to add constraint: 'nodata_values'.  Skipping
+NOTICE:  Adding out-of-database constraint
+NOTICE:  Unable to get the out-of-database bands of a sample raster (max < 1 or null)
+WARNING:  Unable to add constraint: 'out_db'.  Skipping
+NOTICE:  Adding maximum extent constraint
+WARNING:  Unable to add constraint: 'extent'.  Skipping
+#4547.1|t
+NOTICE:  Adding SRID constraint
+NOTICE:  Adding scale-X constraint
+NOTICE:  Adding scale-Y constraint
+NOTICE:  Adding blocksize-X constraint
+NOTICE:  Adding blocksize-Y constraint
+NOTICE:  Adding alignment constraint
+NOTICE:  Adding number of bands constraint
+NOTICE:  Adding pixel type constraint
+NOTICE:  Adding nodata value constraint
+NOTICE:  Adding out-of-database constraint
+NOTICE:  Adding maximum extent constraint
+#4547.2|t