raster2pgsql debug methods needed, GEOTIFF stalling for hours on addrasterconstraints

[osgeoap]

Loading problems. Reviewing the postgres query in progress it is stalled at the addrasterconstraints SQL query, taking hours on a file that normally loads in 25 seconds. Is there an approach to determine what the problem may be with addrasterconstraints and a specific geotiff?

[robe]

addrasterconstraints you could leave out the -C option so it doesn't add the contraint and just add them individually.

Usually the culprit is the extent constraint because that one has to build a big old extent check across the whole table to apply.

Are you loading my rows than usual or you were just only applying few in the past.

Also what exact command are you using on raster2pgsql?

[osgeoap]

Paul: Yes, I tried the same command without'-C' and the image loads. Same number of rows, one image per table. I reviewed the postgres log and see that the last query issued is addrastercontraints. There is also a table showing the query running for hours, (typically takes <30 seconds) raster2pgsql-d -s 4326 -C -r -I -F -q -e -t 256x256 -M filename tablename | psql ….

NOTE: I load the same image into QGIS and/or ArcGIS Pro and save it as a GeoTif and it loads fine with -C.

ideally there would be flag to dump/log addrastercontraints detailed verbose progress.

[robe]

It's hard to tell what the culprit is at anyrate outputting timing of what addraster contraints is doing I think would be useful.

addrastercontraints should probably also be changed to a stored procedure, but that requires PostgreSQL 11 or above, so will probably need to wait. That way it doesn't have to commit all the constraints in one batch, which is partly why it's slow.