3.18 Export to Postgresql (Available Connections) does not adopt parameters correctly - causes error

[billjwilliamson]

The dialog in
PROCESSING TOOLBOX | GDAL | VECTOR MISC | EXPORT TO POSTGRESQL (AVAILABLE CONNECTIONS)
is no longer properly parsing input parameters for the target database. Some quoting issue.

Error code

FAILURE:
Unable to open datasource `dbname='postgis_db'' with the following drivers.
-> `PCIDSK'
-> `netCDF'
-> `PDS4'

Current workaround;
The companion tool EXPORT TO PG (NEW CONNECTION) works fine

On normal package upgrade a regression behaviour has crept into the dialog.

Deleting the existing database connection and recreating it as new, did not fix.

Sample of the dialog console below;

In error

ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL "PG:"""" dbname='postgis_db' host=localhost port=XXXX user='XXXX' password='XXXX' sslmode=disable active_schema=staging """"" -lco DIM=2 "/home/XXXX/Documents/Data/subset_20210225.gpkg" subset_20210225 -overwrite -nlt MULTIPOLYGON -lco GEOMETRY_NAME=geom -lco FID=id -nln staging.subset_20210225 -a_srs EPSG:7855 -nlt PROMOTE_TO_MULTI

Working (using new connection dialog)

ogr2ogr -progress --config PG_USE_COPY YES -f PostgreSQL "PG:host=localhost port=XXXX dbname=postgis_db password=XXXX active_schema=staging user=XXXX" -lco DIM=2 "/home/XXXX/Documents/Data/subset_20210225.gpkg" subset_20210225 -overwrite -nlt MULTIPOLYGON -lco GEOMETRY_NAME=geom -lco FID=id -nln staging.subset -a_srs EPSG:7855 -nlt PROMOTE_TO_MULTI

Difference appears to be the """ around the PG string.

QGIS and OS versions

QGIS version	3.18.0-Zürich	QGIS code revision	bdef9fb
Compiled against Qt	5.12.8	Running against Qt	5.12.8
Compiled against GDAL/OGR	3.0.4	Running against GDAL/OGR	3.0.4
Compiled against GEOS	3.8.0-CAPI-1.13.1	Running against GEOS	3.8.0-CAPI-1.13.1
Compiled against SQLite	3.31.1	Running against SQLite	3.31.1
Compiled against PDAL	2.0.1	Running against PDAL	2.0.1 (git-version: Release)
PostgreSQL Client Version	12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)	SpatiaLite Version	4.3.0a
QWT Version	6.1.4	QScintilla2 Version	2.11.2
Compiled against PROJ	6.3.1	Running against PROJ	Rel. 6.3.1, February 10th, 2020
OS Version	Ubuntu 20.04.2 LTS
Active python plugins	quick_map_services; nominatim_locator_filter; GroupStats; db_manager; processing; MetaSearch

[billjwilliamson]

Perhaps related, on loading a layer and starting the "export to pgsql...." dialog I just got this

AttributeError: 'NoneType' object has no attribute 'lower' 
Traceback (most recent call last):
  File "/usr/share/qgis/python/plugins/processing/algs/gdal/GdalAlgorithmDialog.py", line 137, in parametersHaveChanged
    commands = self.algorithm().getConsoleCommands(parameters, context, feedback, executing=False)
  File "/usr/share/qgis/python/plugins/processing/algs/gdal/ogr2ogrtopostgislist.py", line 285, in getConsoleCommands
    table = layername.lower()
AttributeError: 'NoneType' object has no attribute 'lower'


Python version: 3.8.5 (default, Jan 27 2021, 15:41:15) [GCC 9.3.0] 
QGIS version: 3.18.0-Zürich Zürich, bdef9fb328 

XXXX

[Inrap]

Is it really the same issue or you consider those two issues as a global PostgreSQL issue ?

[gioman]

@billjwilliamson @Inrap probabli fixed by #41847

[agiudiceandrea]

@gioman it seems to me this issue also occurs in 3.16.4, but the fix was not backported to the 3.16 branch.

[SrNetoChan]

@gioman @nyalldawson This still needs to be backported to the LTR branch, which seems to suffer from this issue.

[agiudiceandrea]

@SrNetoChan the merging of the fix for 3.16 branch (#42408) was postponed and it will be available in 3.16.7.

[SrNetoChan]

@SrNetoChan the merging of the fix for 3.16 branch (#42408) was postponed and it will be available in 3.16.7.

Ah, great. Thanks!

[Inrap]

Following #42408, I have modified ogr2ogrtopostgislist.py in Qgis 3.16.6 (from OSGeo4W), but It doesn't change anything : I have the same error Unable to open datasource dbname='activite' with the following drivers.
Is it a normal behaviour ?

[agiudiceandrea]

@Inrap did you force the reload of the "Processing" plugin, or restart QGIS, after modifying the file?

[Inrap]

@agiudiceandrea I quit and reload Qgis each time. (Is there a difference between QGis installed by OSGEo4W and the executable on QGis.org ? I am not sure but it seems that my colleagues who use QGis from QGis.org don't encounter the issue after replacing ogr2ogrtopostgislist.py)

[agiudiceandrea]

@Inrap there is no difference between the OSGeo4W Network Installer and the QGIS Standalone EXE Installer: the latter is only an all-in-one installer which contains the same packages installed by the former.

Could you provide the "GDAL/OGR console call" generated by the tool and the full log of the tool (redacting the sensitive parts) and your fixed ogr2ogrtopostgislist.py (possibly publishing it on gist/github or elsewhere)?

[Inrap]

@agiudiceandrea I nuked my OSGEO4W directory and reinstalled the whole stuff from scratch. Then I have replaced the ogr2ogrtopostgislist.py (in both gdal directory of qgis and qgis-ltr). It works again...I guess I made a mistake somewhere before...Thanks for your attention.