search

qgis / QGIS

QGIS is a free, open source, cross platform (lin/win/mac) geographical information system (GIS)
https://qgis.org
GNU General Public License v2.0
10.52k stars 2.99k forks source link

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

Closed billjwilliamson closed 3 years ago

billjwilliamson commented 3 years ago

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 bdef9fb328
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 commented 3 years ago

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 commented 3 years ago

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

gioman commented 3 years ago

@billjwilliamson @Inrap probabli fixed by https://github.com/qgis/QGIS/pull/41847

agiudiceandrea commented 3 years ago

@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 commented 3 years ago

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

agiudiceandrea commented 3 years ago

@SrNetoChan the merging of the fix for 3.16 branch (https://github.com/qgis/QGIS/pull/42408) was postponed and it will be available in 3.16.7.

SrNetoChan commented 3 years ago

@SrNetoChan the merging of the fix for 3.16 branch (https://github.com/qgis/QGIS/pull/42408) was postponed and it will be available in 3.16.7.

Ah, great. Thanks!

Inrap commented 3 years ago

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 commented 3 years ago

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

Inrap commented 3 years ago

@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 commented 3 years ago

@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 commented 3 years ago

@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.