Bug #93223
closed
Wrong syntax for ALTER INDEX on PostgreSQL
Added by Jörn Wagner almost 4 years ago. Updated 5 months ago.
100%
Description
Database compare generates statements like ALTER INDEX public_index_phash_freeindexuid2_idx RENAME TO "freeIndexUid_65098221". This results in an error:
/* ERROR: relation "public_index_phash_freeindexuid2_idx" does not exist */The first argument needs to be quoted and case-sensitive like ALTER INDEX "public_index_phash_freeIndexUid2_idx" RENAME TO "freeIndexUid_65098221".
My PostgreSQL version is 12.5.
Updated by Jörn Wagner almost 4 years ago
Further research suggests that this is an issue of doctrine/dbal: https://github.com/doctrine/dbal/blob/2.12.x/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php#L713
Updated by Stephan Großberndt almost 4 years ago
- Related to Epic #90719: PostgreSQL related issues added
Updated by Stefan Bürk about 2 years ago
- Status changed from New to Accepted
- Assignee set to Stefan Bürk
Failed to reproduce it yet for a "changed" index. However, I get similar error when a field should be renamed (to zzz_) with postgres, and can reproduce this in v10/v11 and also main/v12. Thus, interessting that this happens with doctrine/dbal 2.x and 3.x (for renaming the field).
I would guess, that it boiles down to the workaround for postgres which is in the compareing stuff which has been added as fascadeds, and eventually the same for index.
So I will take this issue as granted and will look into it again try to fixing it.
Updated by Andreas Kießling 6 months ago
I actually had that error in a fresh 12.4 setup (the missing quotes, not the case sensitivity), but the error came from doctrine itself. If i remember correctly, it could not deal with index names that start numerically.
Patching doctrine with composer-patches fixed it for me, maybe this can be of help
Index: src/Platforms/PostgreSQLPlatform.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/Platforms/PostgreSQLPlatform.php b/src/Platforms/PostgreSQLPlatform.php
--- a/src/Platforms/PostgreSQLPlatform.php
+++ b/src/Platforms/PostgreSQLPlatform.php (date 1715367310975)
@@ -746,7 +746,7 @@
$oldIndexName = $schema . '.' . $oldIndexName;
}
- return ['ALTER INDEX ' . $oldIndexName . ' RENAME TO ' . $index->getQuotedName($this)];
+ return ['ALTER INDEX "' . $oldIndexName . '" RENAME TO ' . $index->getQuotedName($this)];
}
/**
Updated by Stefan Bürk 6 months ago
- TYPO3 Version changed from 10 to 11
The issue still exists in current main and is a not so trival
issue on the TYPO3 (our) side in the whole database analyzer
stack. I know why I want to rewrite it completly for TYPO3 v13.
Somewhere in the whole stack arrays are created, splitted and
recreated - and somewhere in this chain the array index for
renamed column and index are
a) made lowercase
b) saved unquoted and later on not properly quoted, thus havin the issue in the PostgresSQLPlatform class code that it is not quoted.
I did some debugging the last 3 hours - it's not so obvious and a single line to fix. And the "multi" parsing and splitting does not
help either on this case (the stack is a mess structural wise - and a brainfuck).
This needs quite some time to fix this properly, also as backportable solution. Sadly, I have not the time the next days to tackle
that issue without disruption. But I have it on my list (again with higher priority) for the next weeks.
Updated by Gerrit Code Review 5 months ago
- Status changed from Accepted to Under Review
Patch set 1 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/84576
Updated by Gerrit Code Review 5 months ago
Patch set 2 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/84576
Updated by Gerrit Code Review 5 months ago
Patch set 3 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/84576
Updated by Gerrit Code Review 5 months ago
Patch set 4 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/84576
Updated by Gerrit Code Review 5 months ago
Patch set 5 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/84576
Updated by Gerrit Code Review 5 months ago
Patch set 1 for branch 12.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/84698
Updated by Stefan Bürk 5 months ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 6e0111edbcbf12d1b704be70b13a1746d8021a42.
Updated by
Updated by Stefan Bürk 4 months ago
- Related to Bug #104449: Drop fields are not executed in Database Analyzer added