raster2pgsql error: violates check constraint "enforce_out_db_rast"

Question

When I run the command:

raster2pgsql -s 4269 -a -R -F -t auto path_to_my_file.tif public.agdd_2016 | psql -U postgres -d climate -h server_ip -p 5432 --pass

I get the following error:

Processing 1/1: /geo-data/gridded_models/agdd/agdd_20161021_base_thirtytwo_f.tif
INFO: Using computed tile size: 93x72
BEGIN
ERROR:  new row for relation "agdd_2016" violates check constraint "enforce_out_db_rast"
DETAIL:  Failing row contains (3123162, 0100000100DCC660BBAF7A963FDCC660BBAF7A96BFBD8A315555415FC0000000..., agdd_20161021_base_thirtytwo_f.tif, null, null, null).
ERROR:  current transaction is aborted, commands ignored until end of transaction block

How do I fix this? I have tried running this query on my database:

SET postgis.enable_outdb_rasters TO True;

I have also tried adding the following to /etc/postgresql/9.4/main/postgresql.conf and then rebooting postgres

postgis.enable_outdb_rasters = True

- Update -

I have verified that the following query returns true, but the error in question still persists:

SHOW postgis.enable_outdb_rasters;

I also noticed that if I drop the enforce_out_db_rast constraint, the raster2psql command fails on the next constraint that exists on the table so I'm now not so sure that this problem is specific to enforce_out_db_rast.

This error only started happened after upgrading from PostgreSQL from 9.4.5 to 9.4.10. Before that I was not getting any constraint violations.

select version(); (before and after upgrade)

PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

SELECT PostGIS_Version(); (before and after upgrade)

2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

I'm not sure which version of gdal (raster2pgql) I have but maybe that's where the issue is? I also currently have two server snapshots one pre upgrade and functional and the other post upgrade which exhibits the issues.

Answer 1

I noticed that you are doing an append operation instead of creating a new table. Might be your existing table constraints don't match what you are loading.

Try this:

raster2pgsql -s 4269 -C -R -F -t auto path_to_my_file.tif public.agdd_2016_test | psql -U postgres -d climate -h server_ip -p 5432 --pass

The public.agdd_2016_test should have new set of constraints (because of the -C option) based on your added data. Compare these constraints to the one you currently have in public.agdd_2016.

Easy way to compare is just to query:

SELECT * FROM raster_columns 
  WHERE r_table_name IN('agdd_2016', 'agdd_2016_test');

I should add between 2.1 and 2.3, the big thing I did for raster was schema qualifying all the function calls in the constraint functions so that raster database restore would work properly. I can't think of a reason why this would cause this issue though.

There were some other changes with the scaling constraint functions, but again that would be with the scaling constraints not out db.