Loading PostGIS raster with QGIS 3.12.3 crashes

[robe2]

Describe the bug

QGIS crashes when loading PostGIS raster layer. This same exercise works fine in QGIS 3.10.6
FWIW I'm running PostgreSQL 13 beta1 so might be related.

I'm going to do some more tests to make sure my newer PG is not the culprit and it's not a GDAL bug

How to Reproduce

I loaded data from here - http://biogeo.ucdavis.edu/data/worldclim/v2.1/base/wc2.1_10m_prec.zip
Using command

raster2pgsql -s 4326 -I -C -M prec/*.tif -F -t 256x256 ch12.prec | psql

QGIS and OS versions

Get below error
Crash ID: 8db542d910c169105d910e7e9ec58abf67aed639

Stack Trace

memcpy_repmovs memcpy.asm:114
GDALCopyWords64 :
MEMRasterBand::IRasterIO :
GDALRasterBand::RasterIO :
GDALRasterIO :
QgsPostgresRasterProvider::readBlock qgspostgresrasterprovider.cpp:455
QgsRasterDataProvider::block qgsrasterdataprovider.cpp:201
QgsSingleBandGrayRenderer::block qgssinglebandgrayrenderer.cpp:91
QgsBrightnessContrastFilter::block qgsbrightnesscontrastfilter.cpp:118
QgsHueSaturationFilter::block qgshuesaturationfilter.cpp:123
QgsRasterResampleFilter::block qgsrasterresamplefilter.cpp:162
QgsRasterProjector::block qgsrasterprojector.cpp:885
QgsRasterIterator::readNextRasterPartInternal qgsrasteriterator.cpp:130
QgsRasterDrawer::draw qgsrasterdrawer.cpp:60
QgsRasterLayerRenderer::render qgsrasterlayerrenderer.cpp:272
QgsMapRendererParallelJob::renderLayerStatic qgsmaprendererparalleljob.cpp:353
QtConcurrent::MapKernel::iterator,QtConcurrent::FunctionWrapper1 >::runIteration qtconcurrentmapkernel.h:69
QtConcurrent::MapKernel::iterator,QgsImageOperation::ProcessBlockUsingPixelOperation >::runIterations qtconcurrentmapkernel.h:78
QtConcurrent::IterateKernel::iterator,void>::forThreadFunction qtconcurrentiteratekernel.h:256
QtConcurrent::ThreadEngineBase::run :
QThreadPoolPrivate::reset :
QThread::start :
BaseThreadInitThunk :
RtlUserThreadStart :

QGIS Info
QGIS Version: 3.12.3-Bucure?ti
QGIS code revision: 8234261
Compiled against Qt: 5.11.2
Running against Qt: 5.11.2
Compiled against GDAL: 3.0.4
Running against GDAL: 3.0.4

Additional context

[robe2]

FWIW per @jgrocha suggestion, I tried this command using the QGIS shell.

gdalinfo "PG:host=localhost port=5432 dbname='postgis_in_action' user='postgres' password='whatever' schema='ch12' table=prec"

And that seems to work fine gives output like below - subdataset repeated for each row

Driver: PostGISRaster/PostGIS Raster driver
Files: none associated
Size is 0, 0
Coordinate System is:
GEOGCRS["WGS 84",
DATUM["World Geodetic System 1984",
ELLIPSOID["WGS 84",6378137,298.257223563,
LENGTHUNIT["metre",1]]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
CS[ellipsoidal,2],
AXIS["geodetic latitude (Lat)",north,
ORDER[1],
ANGLEUNIT["degree",0.0174532925199433]],
AXIS["geodetic longitude (Lon)",east,
ORDER[2],
ANGLEUNIT["degree",0.0174532925199433]],
ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
Subdatasets:
SUBDATASET_1_NAME=PG:host=localhost port=5432 dbname=postgis_in_action user=postgres password=whatever schema='ch12' table='prec' column='rast' where='"rid" = 1'
SUBDATASET_1_DESC=PostGIS Raster at ch12.prec (rast), with "rid" = 1
SUBDATASET_2_NAME=PG:host=localhost port=5432 dbname=postgis_in_action user=postgres password=whatever schema='ch12' table='prec' column='rast' where='"rid" = 2'

[robe2]

I also tried dragging the layer from DBManager, but just highlighting the raster layer triggers this error:

An error has occurred while executing Python code: - this one looks like it might be an issue with PG13 though as it seems to get stuck at the trying to get the version number.

ValueError: invalid literal for int() with base 10: '13beta1'
Traceback (most recent call last):
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_manager.py", line 100, in itemChanged
self.refreshTabs()
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_manager.py", line 143, in refreshTabs
self.info.showInfo(item)
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\info_viewer.py", line 74, in showInfo
self._showTableInfo(item)
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\info_viewer.py", line 133, in _showTableInfo
html += table.info().toHtml()
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_plugins\info_model.py", line 361, in toHtml
ret = self.getTableInfo()
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\info_model.py", line 231, in getTableInfo
ret = TableInfo.getTableInfo(self)
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_plugins\info_model.py", line 296, in getTableInfo
general_info = self.generalInfo()
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\info_model.py", line 121, in generalInfo
if len([fld for fld in self.table.fields() if fld.primaryKey]) File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_plugins\plugin.py", line 811, in fields
fields = self.database().connector.getTableFields((self.schemaName(), self.name))
File "C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins\db_manager\db_plugins\postgis\connector.py", line 604, in getTableFields
version_number = int(self.getInfo()[0].split(' ')[1].split('.')[0])
ValueError: invalid literal for int() with base 10: '13beta1'

Python version: 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)]
QGIS version: 3.12.3-București București, 8234261

Python Path:
C:/PROGRA1/QGIS31.12/apps/qgis/./python
C:/Users/Administrator/AppData/Roaming/QGIS/QGIS3\profiles\default/python
C:/Users/Administrator/AppData/Roaming/QGIS/QGIS3\profiles\default/python/plugins
C:/PROGRA1/QGIS31.12/apps/qgis/./python/plugins
C:\PROGRA1\QGIS31.12\apps\Python37
C:\PROGRA1\QGIS31.12\apps\Python37\Scripts
C:\Program Files\QGIS 3.12\bin\python37.zip
C:\PROGRA1\QGIS31.12\apps\Python37\DLLs
C:\PROGRA1\QGIS31.12\apps\Python37\lib
C:\Program Files\QGIS 3.12\bin
C:\PROGRA1\QGIS31.12\apps\Python37\lib\site-packages
C:\PROGRA1\QGIS31.12\apps\Python37\lib\site-packages\win32
C:\PROGRA1\QGIS31.12\apps\Python37\lib\site-packages\win32\lib
C:\PROGRA1\QGIS31.12\apps\Python37\lib\site-packages\Pythonwin
C:/Users/Administrator/AppData/Roaming/QGIS/QGIS3\profiles\default/python

I'll try reloading my dataset on PG12 and see if it works

[robe2]

Okay tried reloading my data in PG12 (I'm using PostGIS 3.1.0dev (but doubt that matters)) and still get crashing when loading this layer in all 3 modes

via - Browser - expanding db and selecting the layer
via menu -> Raster -> PostgreSQL and selecting the raster layer

and via DbManager -> selecting the layer and dragging. Only difference is that it errors after I drag the layer (not before as in when I had the database PG13beta1 )

Crash ID: 0362377622e9d19f0d371e7048603091f30fefae

Stack Trace

memcpy_repmovs memcpy.asm:114
GDALCopyWords64 :
MEMRasterBand::IRasterIO :
GDALRasterBand::RasterIO :
GDALRasterIO :
QgsPostgresRasterProvider::readBlock qgspostgresrasterprovider.cpp:455
QgsRasterDataProvider::block qgsrasterdataprovider.cpp:201
QgsSingleBandGrayRenderer::block qgssinglebandgrayrenderer.cpp:91
QgsBrightnessContrastFilter::block qgsbrightnesscontrastfilter.cpp:118
QgsHueSaturationFilter::block qgshuesaturationfilter.cpp:123
QgsRasterResampleFilter::block qgsrasterresamplefilter.cpp:162
QgsRasterProjector::block qgsrasterprojector.cpp:885
QgsRasterIterator::readNextRasterPartInternal qgsrasteriterator.cpp:130
QgsRasterDrawer::draw qgsrasterdrawer.cpp:60
QgsRasterLayerRenderer::render qgsrasterlayerrenderer.cpp:272
QgsMapRendererCustomPainterJob::doRender qgsmaprenderercustompainterjob.cpp:316
QgsMapRendererCustomPainterJob::staticRender qgsmaprenderercustompainterjob.cpp:264
QtConcurrent::RunFunctionTask::run qtconcurrentrunbase.h:136
QThreadPoolPrivate::reset :
QThread::start :
BaseThreadInitThunk :
RtlUserThreadStart :

QGIS Info
QGIS Version: 3.12.3-Bucure?ti
QGIS code revision: 8234261
Compiled against Qt: 5.11.2
Running against Qt: 5.11.2
Compiled against GDAL: 3.0.4
Running against GDAL: 3.0.4

System Info
CPU Type: x86_64
Kernel Type: winnt
Kernel Version: 10.0.18363

[jgrocha]

Thank you for the report @robe2

I just did a quick test with QGIS Master/GDAL 3.2. I've used both PG11/PG12/PostGIS2.5/PostGIS3 and the result is the same.

There were no crashes, but there are issues with the rendering. Playing with the tile size changes the rendering, but is is always messed.

raster2pgsql -s 4326 -d -I -C -R -M `pwd`/wc2.1_10m_prec_01.tif -F -t 1000x1000 public.world | psql -h localhost -U opengeo -d ortos

Print screen

I'm attaching a print screen, with the original raster loaded from tif. On the right, is the same raster public.world table, read with GDAL and the native provided.

[gioman]

seems a regression as no crash (or problems in rendering) on 3.10.

[robe2]

I tried against my PG 12.3, GDAL 3.0.4, PostGIS 3.0.1 database

POSTGIS="3.0.1 3.0.1" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.4, released 2020/01/08" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" TOPOLOGY RASTER PostgreSQL 12.3, compiled by Visual C++ build 1914, 64-bit

and still get a crash.
I restarted my computer to make sure it wasn't something dangling in the install.

@jgrocha could it be maybe an issue that's been fixed since 3.12.3 was released?

I'm using this installer - https://qgis.org/downloads/QGIS-OSGeo4W-3.12.3-1-Setup-x86_64.exe

Crash ID: 8db542d910c169105d910e7e9ec58abf67aed639

Stack Trace

memcpy_repmovs memcpy.asm:114
GDALCopyWords64 :
MEMRasterBand::IRasterIO :
GDALRasterBand::RasterIO :
GDALRasterIO :
QgsPostgresRasterProvider::readBlock qgspostgresrasterprovider.cpp:455
QgsRasterDataProvider::block qgsrasterdataprovider.cpp:201
QgsSingleBandGrayRenderer::block qgssinglebandgrayrenderer.cpp:91
QgsBrightnessContrastFilter::block qgsbrightnesscontrastfilter.cpp:118
QgsHueSaturationFilter::block qgshuesaturationfilter.cpp:123
QgsRasterResampleFilter::block qgsrasterresamplefilter.cpp:162
QgsRasterProjector::block qgsrasterprojector.cpp:885
QgsRasterIterator::readNextRasterPartInternal qgsrasteriterator.cpp:130
QgsRasterDrawer::draw qgsrasterdrawer.cpp:60
QgsRasterLayerRenderer::render qgsrasterlayerrenderer.cpp:272
QgsMapRendererParallelJob::renderLayerStatic qgsmaprendererparalleljob.cpp:353
QtConcurrent::MapKernel::iterator,QtConcurrent::FunctionWrapper1 >::runIteration qtconcurrentmapkernel.h:69
QtConcurrent::MapKernel::iterator,QgsImageOperation::ProcessBlockUsingPixelOperation >::runIterations qtconcurrentmapkernel.h:78
QtConcurrent::IterateKernel::iterator,void>::forThreadFunction qtconcurrentiteratekernel.h:256
QtConcurrent::ThreadEngineBase::run :
QThreadPoolPrivate::reset :
QThread::start :
BaseThreadInitThunk :
RtlUserThreadStart :

QGIS Info
QGIS Version: 3.12.3-Bucure?ti
QGIS code revision: 8234261
Compiled against Qt: 5.11.2
Running against Qt: 5.11.2
Compiled against GDAL: 3.0.4
Running against GDAL: 3.0.4

System Info
CPU Type: x86_64
Kernel Type: winnt
Kernel Version: 10.0.18363

[robe2]

Just retried in 3.10.6 to confirm it works and looks fine.

This is what I get in QGIS 3.10.6 so definitely a regression

@jgrocha one thing I didn't try which I realize might be why you are not getting the crash (I'll try that next on 3.12.3).

It looks like you only loaded one file. I loaded all 12 files (so there are 12 tiles for each area where as yours would only have one). I'm going to try that next to see if the fact I've got overlapping rasters is causing the discrepancy between our experiences)

[robe2]

@jgrocha okay it does seem to be the extra overlapping tiles.

If I do similar to what you did just loading the first file
raster2pgsql -s 4326 -I -C -M prec/wc2.1_10m_prec_01.tif -F -t 256x256 ch12.prec_01 | psql

QGIS 3.12.3 doesn't crash but gives me a screwed up image, similar to what you mentioned

I would have expected the elev I have (which is just one file) to have at least displayed, but that crashes as well. https://biogeo.ucdavis.edu/data/worldclim/v2.1/base/wc2.1_10m_elev.zip

And again displays and looks fine in QGIS 3.10.6

[gioman]

This fix has been backported only to 3.12, maybe is related

#34846

[elpaso]

I've found the issue (PR is coming) but I'm struggling with the band statistics (max value) here I get 2381

qgis_tests=# SELECT (ST_SummaryStatsAgg( "rast", 1, TRUE, 1 )).* FROM "ch12"."prec" ;                             
  count  |    sum    |       mean       |      stddev      | min | max                                              
---------+-----------+------------------+------------------+-----+------                                            
 9696636 | 444475130 | 45.8380751840123 | 68.8047716777728 |   0 | 2381    

while GDAL gives me a very different value:

$ gdalinfo -stats "PG: schema=ch12 table=prec dbname=qgis_tests mode=2"
Driver: PostGISRaster/PostGIS Raster driver
Files: none associated
Size is 2160, 1080
Coordinate System is:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
Origin = (-180.000000000000000,90.000000000000000)
Pixel Size = (0.166666666700000,-0.166666666700000)
Corner Coordinates:
Upper Left  (-180.0000000,  90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"N)
Lower Left  (-180.0000000, -90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"S)
Upper Right ( 180.0000001,  90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"N)
Lower Right ( 180.0000001, -90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"S)
Center      (   0.0000000,  -0.0000000) (  0d 0' 0.00"E,  0d 0' 0.00"S)
Band 1 Block=2048x1080 Type=Int16, ColorInterp=Gray
  Minimum=0.000, Maximum=793.000, Mean=38.399, StdDev=64.897
  NoData Value=-32768
  Metadata:
    STATISTICS_MAXIMUM=793
    STATISTICS_MEAN=38.398666919124
    STATISTICS_MINIMUM=0
    STATISTICS_STDDEV=64.896946909805

I know GDAL probably uses a sample but still I don't expect such a wide difference.

Any idea?

[elpaso]

Ok, I see what's probably happening here GDAL uses a single tile (randomly?) to extract the statistics, in my case the tile comes from wc2.1_10m_prec_02.tif.

QGIS native implementation does not support multiple tiles covering the same extent unless you set a subset string filter (or a temporal filter).

[rouault]

GDAL uses a single tile (randomly?) to extract the statistics,

Ah, in case of overlapping data, the result will depend on the order into which PostgreSQL returns rows intersecting the spatial result. The last returned tile will likely appear on-top. So yes, result might be somewhat "random"

[jgrocha]

Nice work @elpaso