Bug report #21436
DBManager: spatial indexes are not reamed when a table is renamed
| 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 about 5 years 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 about 5 years 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 about 5 years 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