Bug report #20170

"no result buffer" warning, application restart required after PostGIS connection is lost and reconnected

Added by Will McDonald over 1 year ago. Updated over 1 year ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
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

Description

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:
  1. Create a layer with a PostGIS query to DBTunnel
  2. Disconnect the ssh tunnel
  3. 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)
  4. Reconnect the ssh tunnel (say, after opening my laptop in a new location)
  5. Perform a new query, and successfully see the result rows in the DB Manager window
  6. Attempt to add those results as a new Layer
Then I get the following result:
  1. The layer is not created
  2. 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


Related issues

Duplicated by QGIS Application - Bug report #21104: no result buffer error for PostGIS datasets. Closed 2019-01-25

Associated revisions

Revision f30a15cc
Added by Timothe Perez over 1 year ago

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.
Fixes #20170

Revision 31adb153
Added by Timothe Perez over 1 year ago

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.
Fixes #20170

(cherry picked from commit f30a15ccecfd0e94828a82e33b38260623c2eeec)

History

#1 Updated by Axel Hörteborn over 1 year ago

I can just comfirm that this also occur on Windows and in QGIS 3.4.1

#2 Updated by Giovanni Manghi over 1 year ago

  • Affected QGIS version changed from 3.2.1 to 3.4.1
  • Operating System deleted (MacOS 10.14)

#3 Updated by Axel Hörteborn over 1 year ago

This was not solved in 3.4.2

In my experience Layers that are already loaded works, but it is not possible to change the filters of those layers. It is sometimes(?) possible to add new layers via DB manager, but it is not possible via the browser panel.

#4 Updated by Giovanni Manghi over 1 year ago

  • Affected QGIS version changed from 3.4.1 to 3.4.2

#5 Updated by Michael Douchin over 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 :

[19239][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.
[19239][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.

#6 Updated by Jürgen Fischer over 1 year ago

  • Duplicated by Bug report #21104: no result buffer error for PostGIS datasets. added

#7 Updated by Axel Hörteborn over 1 year ago

Was the issue fixed?

#9 Updated by Timothe Perez over 1 year ago

  • % Done changed from 0 to 100
  • Status changed from Open to Closed

Also available in: Atom PDF