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


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.

  • Which version of postgis are you running? and did you run: ALTER EXTENSION postgis UPDATE; after upgrading? – LR1234567 Oct 27 '16 at 4:23
  • @LR1234567 I've edit the question to better explain my upgrade path. When you commented I hadn't yet upgraded the postgis extension, but since then I have and have ran the ALTER EXTENSION command. Still with the same symptoms showing. – apricity Oct 27 '16 at 19:48

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.