[raster] Use of exclusion constraint on multiple tables fails due to name of implicit index

[mloskot]

(This ticket follows the postgis-devel thread on ​Testing out-db and regular blocking.)

I'm running a simple test:

• single GeoTIFF, 512x256 pixels, RGB
• loaded as single image, no cutting into tiles

I noticed, these two commands set different regular_blocking constraint:

raster2pgsql     -C -r  -> TRUE
raster2pgsql -R -C -r  -> FALSE

I attached screenshot with all the values in raster_columns view presented.

The PostgreSQL server runs as postgres user and has access to the world.tif file

postgres:~$ ps -ef|grep '9.1/bin/postgres'
postgres  1012     1  0 10:09 ?        00:00:00
/usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main
-c config_file=/etc/postgresql/9.1/main/postgresql.conf

postgres:~$ ls -l  /home/mloskot/data/geotiff/world.tif
-rw-r--r-- 1 mloskot mloskot 411822 Apr 14  2008
/home/mloskot/data/geotiff/world.tif

postgres:~$ gdalinfo  /home/mloskot/data/geotiff/world.tif
Driver: GTiff/GeoTIFF
Files: /home/mloskot/data/geotiff/world.tif
       /home/mloskot/data/geotiff/world.tif.aux.xml
Size is 512, 256
...

Here is Bash script that generates test tables:

#!/bin/bash
# Test loading single image/tile as in-db and out-db
# using three raster2pgsql modes: default flags, -C and -C -r
#
# Build: PostGIS Raster (trunk@r11115), GEOS (trunk), GDAL (trunk)
#
U=postgres
O=mloskot
D=rasters2
# world.tif, 512x256, RGB, GeoTIFF
R=/home/mloskot/data/geotiff/world.tif
dropdb -U ${U} ${D}
createdb -U ${U} -O ${O} ${D}
[[ $? -ne 0 ]] && exit 1
psql -U ${U} -d ${D} -c 'CREATE EXTENSION postgis'
[[ $? -ne 0 ]] && exit 1
echo "-R ----------------------------------------------------------------------------"
raster2pgsql -R ${R} out_R > out_R.sql
[[ $? -ne 0 ]] && exit 1
echo "-R -C -------------------------------------------------------------------------"
raster2pgsql -R -C ${R} out_R_C > out_R_C.sql
[[ $? -ne 0 ]] && exit 1
echo "-R -C -r ----------------------------------------------------------------------"
raster2pgsql -R -C -r ${R} out_R_C_r > out_R_C_r.sql
[[ $? -ne 0 ]] && exit 1
echo "-------------------------------------------------------------------------------"
raster2pgsql ${R} in > in.sql
[[ $? -ne 0 ]] && exit 1
echo "-C ----------------------------------------------------------------------------"
raster2pgsql -C ${R} in_C > int_C.sql
[[ $? -ne 0 ]] && exit 1
echo "-C -r -------------------------------------------------------------------------"
raster2pgsql -C -r ${R} int_C_r > int_C_r.sql
[[ $? -ne 0 ]] && exit 1
echo
echo "DONE: $?"

for f in *.sql
do
    psql -d ${D} -f ${f}
    [[ $? -ne 0 ]] && exit 1
done

Bborie's posted ​first diagnosis:

there's an error creating the implicit exclusion index for the enforce_spatially_unique_rast constraint

[mloskot]

raster_columns view for the test tables

[mloskot]

Patch proposes fix to make the contraint name unique

[dustymugs]

Fixed in r11117.

A simplified test case.

CREATE TABLE foo AS
	SELECT
		id
	FROM generate_series(1, 5) t(id);
CREATE TABLE bar AS
	SELECT
		id
	FROM generate_series(1, 5) t(id);
ALTER TABLE foo
	ADD CONSTRAINT enforce_unique_id EXCLUDE (id WITH =);
ALTER TABLE bar
	ADD CONSTRAINT enforce_unique_id EXCLUDE (id WITH =);

[mloskot]

Thanks Bborie! Screen with fixed constraints presented