Bug report #20170
"no result buffer" warning, application restart required after PostGIS connection is lost and reconnected
|Affected QGIS version:||3.4.2||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||27991|
Running QGIS 3.2.0-Bonn on MacOS Mojave, though this has been a consistent problem since QGIS 2.x.
I run queries against a PostGIS database that is only accessible via VPN or SSH tunnel. For example, I set up a tunnel like this:
ssh -N -L 55432:db-hostname:5432 server
and then have a QGIS PostGIS connection named "DBTunnel" to localhost, port 55432.Every time I follow these steps:
- Create a layer with a PostGIS query to DBTunnel
- Disconnect the ssh tunnel
- Perform any action that would cause a DB query, like toggling that layer (I believe, but am not positive, that this step is required to reproduce)
- Reconnect the ssh tunnel (say, after opening my laptop in a new location)
- Perform a new query, and successfully see the result rows in the DB Manager window
- Attempt to add those results as a new Layer
- The layer is not created
- I see the following message in the PostGIS tab of the Log Messages panel:
2018-10-19T10:30:49 WARNING Unable to execute the query. The error message from the database was: no result buffer. SQL: SELECT * FROM (SELECT row_number() over () AS _uid_,* FROM (SELECT id, geometry from table) AS _subq_1_ ) AS "subQuery_0" LIMIT 1
After this happens, the only way I know of to load new PostGIS layers is to restart the application, which as you can imagine is quite frustrating.
QGIS version info:
QGIS version 3.2.0-Bonn
QGIS code branch Release 3.2
Compiled against Qt 5.9.3
Running against Qt 5.9.3
Compiled against GDAL/OGR 2.2.4
Running against GDAL/OGR 2.2.4
Compiled against GEOS 3.6.2-CAPI-1.10.2
Running against GEOS 3.6.2-CAPI-1.10.2 4d2925d6
PostgreSQL Client Version 9.6.6
SpatiaLite Version 4.4.0-RC0
QWT Version 6.1.3
QScintilla2 Version 2.10.3
PROJ.4 Version 493
Fix Postgresql connection reset not being called
PQreset was never called if the query was made using mConnectionRO
from the PostgresProvider, resulting in an always failing state.
#5 Updated by Michael Douchin about 1 year ago
We install QGIS Server a lot in our client infrastructure, and this issue often appear in this context too. Some times, there is a proxy or reverse proxy, cache software, or some other tool which cut the connection after some time (for example 30min) between servers, and then QGIS Server instances cannot use the PostgreSQL layers anymore.
Example message :
[15:16:20] Connection error: SELECT * FROM "myschema"."mytable" LIMIT 1 returned 1 [la connexion au serveur a été coupée de façon inattendue Le serveur s'est peut-être arrêté anormalement avant ou durant le traitement de la requête. [15:16:20] Unable to access the "myschema"."mytable" relation. The error message from the database was: no result buffer.
When the IT managers knows their tool well, they can configure it and disable this behaviour (or increase the timeout), but very often, this kind of behaviour is even unknown or cannot be configured in the proxy/cache manager/...
This is a very annoying QGIS bug, since it needs a manual restart of all QGIS Server workers to get the WMS/WFS working again. This is not ok in production environment.