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

[qgib]

Author Name: Pedro Venâncio (Pedro Venâncio)
Original Redmine Issue: 21436
Affected QGIS version: 3.6.0
Redmine category:db_manager

---

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

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

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

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: Pedro Venâncio (Pedro Venâncio)

---

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.

---

• status_id was changed from Feedback to Open

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• operating_system was changed from Windows 10 to
• subject was 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

[alexbruy]

But should they be renamed? As far as I can see, pgadmin does not rename dependent objects when renaming a table. Also if we choose to go with renaming, we will need to rename not only indexes but also all other objects like sequences, views, triggers, etc which maybe error prone in automatic mode.

[gioman]

But should they be renamed? As far as I can see, pgadmin does not rename dependent objects when renaming a table. Also if we choose to go with renaming, we will need to rename not only indexes but also all other objects like sequences, views, triggers, etc which maybe error prone in automatic mode.

@PedroVenancio what about this comment from @alexbruy ?

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[github-actions]

While we hate to see this happen, this Issue has been automatically closed because it has not had any activity in the last 42 days despite being marked as feedback. If this issue should be reconsidered, please follow the guidelines in the previous comment and reopen this issue.
Or, if you have any further questions, there are also further support channels that can help you.