PostgreSQL Duplicate table error for alter index queries

Created on 14 July 2020, over 4 years ago
Updated 28 February 2024, 10 months ago

Problem/Motivation

With a PostgreSQL backend, when you rename a table all the indexes also get renamed. When the renamed index has the same name as the old table name, the table renaming will fail.

Proposed resolution

Fix the bug and add testing.

Remaining tasks

None

User interface changes

None

API changes

An API-addition for the method Drupal\Core\Database\Schema::tableExists(). A second parameter is added named: $add_prefix, which defaults to TRUE. When the new parameter is not set, the method works the same as before the second parameter was added. When the second parameter is set to FALSE, the existance of the table is checked without adding its table prefix.

Data model changes

None

Release notes snippet

An API-addition for the method Drupal\Core\Database\Schema::tableExists(). A second parameter is added named: $add_prefix, which defaults to TRUE. When the new parameter is not set, the method works the same as before the second parameter was added. When the second parameter is set to FALSE, the existance of the table is checked without adding its table prefix.

Original bug report

I was upgrading from Drupal 8.5.11 to 8.8.5 and I have got this error: [error] Exception thrown while performing a schema update. SQLSTATE[42P07]: Duplicate table: 7 ERROR: relation "old_81ee22taxonomy_term_field_data____name" already exists: ALTER INDEX "idx_30852_taxonomy_term_field__name" RENAME TO old_81ee22taxonomy_term_field_data____name; Array.

I have read many threads related to this error but none patch worked in my case. In the end, I got this fixed with the attached patch.

More Information:

  1. I have checked for any suspicious term inside the table "taxonomy_term_field_data". All looked fine. No term with status set to '0'. All terms are having the valid name and mapped to correct and existing vocabularies.
  2. Checked for existing tables with pattern "old_%", found none.

More on the solution/patch:
On digging more into the issue i have found that in the function renameTable() of my Schema.php of PGSQL Driver (/core/lib/Drupal/Core/Database/Driver/pgsql/Install). The index_name was being passed empty and this caused the duplicate index issue. Extra if check makes sure "$index_name" is not empty or null.

🐛 Bug report
Status

Fixed

Version

11.0 🔥

Component
PostgreSQL driver 

Last updated 9 days ago

No maintainer
Created by

🇮🇳India saxenaakansha30 New Delhi, India

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Merge Requests

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Production build 0.71.5 2024