Bug report #21436

DBManager: spatial indexes are not reamed when a table is renamed

Added by Pedro Venâncio over 1 year ago. Updated over 1 year ago.

Status:Open
Priority:Normal
Assignee:-
Category:DB Manager
Affected QGIS version:3.6.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:29253

Description

DBManager throws an error when importing a new layer to PostGIS, and the layer name is the same of an old layer already in PostGIS (name meanwhile changed). As the old layer name had a spatial index with the same name structure (sidx_ + layer_name + _the_geom) of the new layer name, DBManager throws an error, instead of show an warning window or so.

An error has occurred while executing Python code: 

db_manager.db_plugins.plugin.DbError: relation "sidx_coordenadas_ema_the_geom" already exists 
Traceback (most recent call last):
  File "C:/OSGEO4~1/apps/qgis-rel-dev/./python/plugins\db_manager\db_plugins\connector.py", line 86, in _execute
    cursor.execute(sql)
psycopg2.ProgrammingError: relation "sidx_coordenadas_ema_the_geom" already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/OSGEO4~1/apps/qgis-rel-dev/./python/plugins\db_manager\dlg_import_vector.py", line 373, in accept
    self.db.connector.createSpatialIndex((schema, table), geom)
  File "C:/OSGEO4~1/apps/qgis-rel-dev/./python/plugins\db_manager\db_plugins\postgis\connector.py", line 999, in createSpatialIndex
    self._execute_and_commit(sql)
  File "C:/OSGEO4~1/apps/qgis-rel-dev/./python/plugins\db_manager\db_plugins\connector.py", line 100, in _execute_and_commit
    self._execute(None, sql)
  File "C:/OSGEO4~1/apps/qgis-rel-dev/./python/plugins\db_manager\db_plugins\connector.py", line 94, in _execute
    raise DbError(e, sql)
db_manager.db_plugins.plugin.DbError: relation "sidx_coordenadas_ema_the_geom" already exists

Python version: 3.7.0 (v3.7.0:1bf9cc5093, Jun 27 2018, 04:59:51) [MSC v.1914 64 bit (AMD64)] 
QGIS version: 3.6.0-Noosa Noosa, 6b6a1ba5bc

History

#1 Updated by Giovanni Manghi over 1 year ago

  • Status changed from Open to Feedback

Does it happens in a previous release? only after renaming a table or also after deleting one?

#2 Updated by Pedro Venâncio over 1 year ago

  • Status changed from Feedback to Open

Ok, I've been digging more in this issue and:

  • It only happens in QGIS 3 (3.4.5, 3.6.0 and master) using the 'Create spatial index' option in 'Import vector layer' window.
  • It does not happen when importing the layer without 'Create spatial index' option, and creating the spatial index after, in DB Manager Info tab (No spatial index defined (create it)). This way, DB Manager shows the Database error window with 'relation "sidx_coordenadas_ema_the_geom" already exists' message.
  • In QGIS 2.18.28, using the 'Create spatial index' option in 'Import vector layer' window, DB Manager imports the layer, does not create the spatial index, does not throws the Python error, and does not show the 'Import was successful' window message. 'Import vector layer' window keeps open indefinitely, although the layer be successful imported, without spatial index.

This only happens after renaming a table, without renaming the correspondent spatial index. Dropping the table, the spatial index goes with it.

#3 Updated by Giovanni Manghi over 1 year ago

  • Operating System deleted (Windows 10)
  • Subject changed from DBManager Error when creating a spatial index with a name already existent in PostGIS to DBManager: spatial indexes are not reamed when a table is renamed

Also available in: Atom PDF