Opened 14 months ago

Last modified 10 months ago

#4402 new defect

raster2pgsql debug methods needed, GEOTIFF stalling for hours on addrasterconstraints

Reported by: osgeoap Owned by: Bborie Park
Priority: medium Milestone: PostGIS Fund Me
Component: raster Version: 2.4.x
Keywords: addrasterconstraint, raster2pgsql Cc:


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?

Change History (4)

comment:1 Changed 14 months ago by robe

Component: postgisraster
Owner: changed from pramsey to Bborie Park

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?

comment:2 Changed 14 months ago by 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.

comment:3 Changed 11 months ago by robe

Milestone: PostGIS 2.4.8PostGIS 3.0.0

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.

comment:4 Changed 10 months ago by robe

Milestone: PostGIS 3.0.0PostGIS Fund Me
Note: See TracTickets for help on using tickets.