Opened 6 years ago

Closed 6 years ago

#2215 closed defect (fixed)

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

Reported by: mloskot Owned by: Bborie Park
Priority: medium Milestone: PostGIS 2.1.0
Component: raster Version: trunk
Keywords: constraints, history Cc:

Description

(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

Attachments (3)

postgis_raster_in_out_single_tile_constraints.png (22.5 KB) - added by mloskot 6 years ago.
raster_columns view for the test tables
fix_2215_make_spatially_unique_constraint_name_unique.patch (479 bytes) - added by mloskot 6 years ago.
Patch proposes fix to make the contraint name unique
postgis_raster_in_out_single_tile_constraints_fixed.png (20.6 KB) - added by mloskot 6 years ago.
Thanks Bborie! Screen with fixed constraints presented

Download all attachments as: .zip

Change History (4)

Changed 6 years ago by mloskot

raster_columns view for the test tables

Changed 6 years ago by mloskot

Patch proposes fix to make the contraint name unique

comment:1 Changed 6 years ago by Bborie Park

Keywords: history added
Milestone: PostGIS 2.1.0
Resolution: fixed
Status: newclosed
Summary: [raster] Different constraints set for in-db and out-db[raster] Use of exclusion constraint on multiple tables fails due to name of implicit index

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 =);

Changed 6 years ago by mloskot

Thanks Bborie! Screen with fixed constraints presented

Note: See TracTickets for help on using tickets.