a solution which seems to work: quite simply to use the variables
PRELUDE_STATEMENTS, CLOSING_STATEMENTS.
The gdal doc mentions pg_bouncer, pgpool looks like it..
For QGis, add the following lines to the .env file:
PRELUDE_STATEMENTS = "BEGIN;"
CLOSING_STATEMENTS = "COMMIT;"
and to the .vars file:
PRELUDE_STATEMENTS
CLOSING_STATEMENTS
From Pyhon, :
gdal.SetConfigOption("PRELUDE_STATEMENTS", "BEGIN;")
gdal.SetConfigOption("CLOSING_STATEMENTS", "COMMIT;")
do you confirm this?
-----Message d'origine-----
De : gdal-dev [mailto:[email protected]] De la part de CULOS
Envoyé : mardi 11 août 2020 11:39
À : '[email protected]'
Objet : [gdal-dev] A performance issue when connecting to pg through PgPool.
Hi,
is this the correct list for this issue ? It is about a problem of performence
which concerns the couple GDAL / PgPool.
I am posting here because our other tools (talend, php, psycopg ...) which
connect to the pool do not have this problem.
A trio of servers (pool, master, slave) have been working well so far, but...
under QGis, the following script launches a simple ogr.Open.
import osgeo.ogr as ogr
uri = "host=pool port=5432 dbname=dbname user=user password=password"
QgsMessageLog.logMessage("CONNECT", 'perfs', level=Qgis.Info)
conn = ogr.Open("PG:{}".format(uri))
QgsMessageLog.logMessage("OK", 'perfs', level=Qgis.Info)
And the result : a connection that lasts 8s.
2020-08-11T09:53:28 INFO CONNECT
(.. 8s ..)
2020-08-11T09:53:36 INFO OK
We find this delay of 8s when opening layers (first opened) in QGis. 9/10 times.
When you connect directly to the 'master' or to the 'slave', or when the pool
is connected to only one node, the connection is immediate.
The ogrinfo command has the same behavior (ogrinfo -ro PG:"host=pool port=5432
dbname=my_db schemas=a_schema tables=a_table").
The pg servers logs are very different, depending on the case.
What is happening ? do you have an idea ?
Thanks,
Xavier.
_______________________________________________
gdal-dev mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
