From ed91dcf01243b57349826a1d54f59785f48a5bb5 Mon Sep 17 00:00:00 2001 From: Sandro Santilli Date: Thu, 17 Oct 2019 14:15:31 +0000 Subject: [PATCH] Fix AddRasterConstraints in presence of null geoms in table References #4547 in 2.4 branch git-svn-id: http://svn.osgeo.org/postgis/branches/2.4@17937 b70326c6-7e19-0410-871a-916f4a2858ee --- raster/rt_pg/rtpostgis.sql.in | 20 ++++++++------ raster/test/regress/tickets.sql | 11 +++++++- raster/test/regress/tickets_expected | 39 ++++++++++++++++++++++++++++ 3 files changed, 61 insertions(+), 9 deletions(-) diff --git a/raster/rt_pg/rtpostgis.sql.in b/raster/rt_pg/rtpostgis.sql.in index b2037647c..da62cabd9 100644 --- a/raster/rt_pg/rtpostgis.sql.in +++ b/raster/rt_pg/rtpostgis.sql.in @@ -7197,24 +7197,28 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_extent(rastschema name, rastta 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@.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@.st_ashexewkb( @extschema@.st_setsrid( @extschema@.st_extent( @extschema@.st_envelope(' - || quote_ident($3) + || quote_ident(rastcolumn) || ')), ' || srid || ')) FROM ' || fqtn; EXECUTE sql INTO attr; @@ -7223,7 +7227,7 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_extent(rastschema name, rastta sql := 'ALTER TABLE ' || fqtn || ' ADD CONSTRAINT ' || quote_ident(cn) || ' CHECK ( @extschema@.st_envelope(' - || quote_ident($3) + || quote_ident(rastcolumn) || ') @ ''' || attr || '''::@extschema@.geometry) NOT VALID'; RETURN @extschema@._add_raster_constraint(cn, sql); END; diff --git a/raster/test/regress/tickets.sql b/raster/test/regress/tickets.sql index 5501ebb89..0d9423104 100644 --- a/raster/test/regress/tickets.sql +++ b/raster/test/regress/tickets.sql @@ -125,4 +125,13 @@ CREATE TABLE table_4308 (r raster); 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; diff --git a/raster/test/regress/tickets_expected b/raster/test/regress/tickets_expected index 31ff4d4f6..fee0e70d4 100644 --- a/raster/test/regress/tickets_expected +++ b/raster/test/regress/tickets_expected @@ -26,3 +26,42 @@ NOTICE: Adding nodata value constraint 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 -- 2.40.0