Bug report #22035

"Export to PostgreSQL" python error if rather schema has an uppercase letter in its name

Added by Giovanni Manghi 3 months ago. Updated 3 months ago.

Status:Closed
Priority:Normal
Assignee:Alessandro Pasotti
Category:Processing/QGIS
Affected QGIS version:3.6.2 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:Yes Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:29849

Description

QGIS version: 3.6.2-Noosa
QGIS code revision: 656500e
Qt version: 5.9.5
GDAL version: 2.2.3
GEOS version: 3.6.2-CAPI-1.10.2 4d2925d6
PROJ version: 493
Processing algorithm…
Algorithm 'Export to PostgreSQL' starting…
Input parameters: { 'CREATEINDEX' : True, 'DATABASE' : 'locale', 'DROP_STRING_LENGTH' : False, 'ENCODING' : 'UTF-8', 'FORCE_SINGLEPART' : False, 'GEOMETRY_COLUMN' : 'geom', 'INPUT' : '/media/giovanni/DATA/training/data/portugal_3763/areas_protegidas.shp', 'LOWERCASE_NAMES' : True, 'OVERWRITE' : True, 'PRIMARY_KEY' : None, 'SCHEMA' : 'Teste1', 'TABLENAME' : '' }

Traceback (most recent call last):
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 826, in _exec_sql
cursor.execute(sql)
psycopg2.ProgrammingError: relation "teste1.areas_protegidas" does not exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 838, in _exec_sql_and_commit
self._exec_sql(c, sql)
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 829, in _exec_sql
e.cursor.query.decode(e.cursor.connection.encoding))
_core.QgsProcessingException: relation "teste1.areas_protegidas" does not exist
QUERY: CREATE INDEX sidx_areas_protegidas_geom ON Teste1.areas_protegidas USING GIST

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/share/qgis/python/plugins/processing/algs/qgis/ImportIntoPostGIS.py", line 204, in processAlgorithm
db.create_spatial_index(table, schema, geomColumn)
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 727, in create_spatial_index
self._exec_sql_and_commit(sql)
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 840, in _exec_sql_and_commit
except DbError:
NameError: name 'DbError' is not defined

Associated revisions

Revision ad24b876
Added by Alessandro Pasotti 3 months ago

Add missing error class and quote identifiers

Fixes #22035 - case sensitive schema

History

#1 Updated by Alessandro Pasotti 3 months ago

  • Status changed from Open to Feedback
  • Assignee set to Alessandro Pasotti

Does it change anything if LOWERCASE_NAMES=False?

#2 Updated by Giovanni Manghi 3 months ago

  • Status changed from Feedback to Open

Alessandro Pasotti wrote:

Does it change anything if LOWERCASE_NAMES=False?

seems is gives the same error:

QGIS version: 3.7.0-Master
QGIS code revision: 943c7d5
Qt version: 5.9.5
GDAL version: 2.2.3
GEOS version: 3.6.2-CAPI-1.10.2 4d2925d6
PROJ version: 493
Processing algorithm…
Algorithm 'Export to PostgreSQL' starting…
Input parameters: { 'CREATEINDEX' : True, 'DATABASE' : 'locale', 'DROP_STRING_LENGTH' : False, 'ENCODING' : 'UTF-8', 'FORCE_SINGLEPART' : False, 'GEOMETRY_COLUMN' : 'geom', 'INPUT' : '/media/giovanni/DATA/training/data/portugal_3763/localidades.shp', 'LOWERCASE_NAMES' : False, 'OVERWRITE' : True, 'PRIMARY_KEY' : None, 'SCHEMA' : 'Teste1', 'TABLENAME' : '' }

Traceback (most recent call last):
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 826, in _exec_sql
cursor.execute(sql)
psycopg2.ProgrammingError: schema "teste1" does not exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 838, in _exec_sql_and_commit
self._exec_sql(c, sql)
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 829, in _exec_sql
e.cursor.query.decode(e.cursor.connection.encoding))
_core.QgsProcessingException: schema "teste1" does not exist
QUERY: CREATE INDEX sidx_localidades_geom ON Teste1.localidades USING GIST

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/usr/share/qgis/python/plugins/processing/algs/qgis/ImportIntoPostGIS.py", line 204, in processAlgorithm
db.create_spatial_index(table, schema, geomColumn)
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 727, in create_spatial_index
self._exec_sql_and_commit(sql)
File "/usr/share/qgis/python/plugins/processing/tools/postgis.py", line 840, in _exec_sql_and_commit
except DbError:
NameError: name 'DbError' is not defined

Execution failed after 1.04 seconds

Loading resulting layers
Algorithm 'Export to PostgreSQL' finished

#3 Updated by Alessandro Pasotti 3 months ago

  • Status changed from Open to In Progress
  • Resolution set to fixed/implemented
  • Pull Request or Patch supplied changed from No to Yes

PR https://github.com/qgis/QGIS/pull/10063

That's just the tip of the iceberg, the code was written as if capital letters didn't exist.

#4 Updated by Alessandro Pasotti 3 months ago

  • % Done changed from 0 to 100
  • Status changed from In Progress to Closed

Also available in: Atom PDF