Interlis Import unsupported startup parameter: options

[cymed]

Describe the bug
When trying to import the any xtf, I received an psycopg2.OperationalError: connection to server at "%ip%", port %port% failed: FATAL: unsupported startup para

To Reproduce
Exact steps to reproduce the behavior:

1. try to import the an xtf using the UI button
2. See error

Expected behavior
XTF import

Screenshots / data
Error:

psycopg2.OperationalError: connection to server at "%ip%", port %port% failed: FATAL: unsupported startup parameter: options
Traceback (most recent call last):
File "C:\Users/%usr%/AppData/Roaming/QGIS/QGIS3\profiles\tww_dev/python/plugins\teksi_wastewater\teksi_wastewater_plugin.py", line 495, in actionImportClicked
self.interlisImporterExporter.action_import()
File "C:\Users/%usr%/AppData/Roaming/QGIS/QGIS3\profiles\tww_dev/python/plugins\teksi_wastewater\interlis\gui\interlis_importer_exporter.py", line 106, in action_import
raise exception
File "C:\Users/%usr%/AppData/Roaming/QGIS/QGIS3\profiles\tww_dev/python/plugins\teksi_wastewater\interlis\gui\interlis_importer_exporter.py", line 90, in action_import
self._action_import(xtf_file_input)
File "C:\Users/%usr%/AppData/Roaming/QGIS/QGIS3\profiles\tww_dev/python/plugins\teksi_wastewater\interlis\gui\interlis_importer_exporter.py", line 180, in _action_import
self._clear_ili_schema(recreate_schema=True)
File "C:\Users/%usr%/AppData/Roaming/QGIS/QGIS3\profiles\tww_dev/python/plugins\teksi_wastewater\interlis\gui\interlis_importer_exporter.py", line 478, in clear_ili_schema
connection = psycopg2.connect(
File "C:\Users%usr%\AppData\Roaming\Python\Python39\site-packages\psycopg2_init.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "%ip%", port %port% failed: FATAL: unsupported startup parameter: options

Desktop (please complete the following information):

• TEKSI plugin version: dev
• TEKSI datamodel version: dev
• TEKSI project file version: 2024_01_15
• QGIS Version - see the About menu: 3.28.11
• Postgres: 12.17
• Postgis: POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.4" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)"
• OS: Windows

Additional context

• psycopg2 Version: 2.9.7 (dt dec pq3 ext lo64)

[sjib]

@cymed Wie machst du deine Datenbankverbindung? pg_conf?

[domi4484]

Ip address and port (and in general postgres connection settings) are taken from the layer vw_tww_wastewater_structure. Do you have the project open?

[cymed]

@cymed Wie machst du deine Datenbankverbindung? pg_conf?

DB-connection via PGSYSCONFDIR, windows system variable overridden in qgis options. Project is open and working (inserting through wizard and directly adding entries to vw_tww_wastewater_structure).

[urskaufmann]

Ich habe eine ähnliche Fehlermeldung, wenn ich im pgsysconfdir nicht user und passwort (postgres-passwort) eintrage, weil für das erstellen des Austauschschema das postgres-Passwort notwendig ist. Die Fehlermeldung hilft leider nicht direkt, dem auf die Spur zu kommen.

[cymed]

Ich habe eine ähnliche Fehlermeldung, wenn ich im pgsysconfdir nicht user und passwort (postgres-passwort) eintrage, weil für das erstellen des Austauschschema das postgres-Passwort notwendig ist. Die Fehlermeldung hilft leider nicht direkt, dem auf die Spur zu kommen.

User und Passwort sind im pgsysconfdir vorhanden

[cymed]

Found out the following things:

• dump files do not include grant structure
• grant structure is missing tww_app (see fix in add tww_app to 12_1_roles.sql #83)
• TWW interlis import does use the qgis override pgsysconfdir
• error remains

[cymed]

Error is thrown at

wastewater/plugin/teksi_wastewater/interlis/gui/interlis_importer_exporter.py

Lines 479 to 481 in d0135f5

	connection = psycopg2.connect(
	get_pgconf_as_psycopg2_dsn(), options="-c statement_timeout=1000"
	)

[cymed]

It might be a problem of connecting via pgbouncer, see https://stackoverflow.com/questions/69133440/django-error-on-connecting-to-postgres-unsupported-startup-parameter-options.
I could check whether setting ignore_startup_parameters to options works, but I have no writing rights on pgbouncer.ini

[sjib]

• check for solution for options else remove options="-c statement_timeout=1000" @domi4484

[domi4484]

Fix uploaded in the plugin zip in discussion top post

[cymed]

Fixed by removing session timeout