Opened 5 months ago

Last modified 5 weeks 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:

Description

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 5 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 5 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 2 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 5 weeks ago by robe

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