Bug #93223
closed
Wrong syntax for ALTER INDEX on PostgreSQL
Added by Jörn Wagner over 3 years ago.
Updated 1 day ago.
Category:
Database API (Doctrine DBAL)
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.
Related issues
1 (1 open — 0 closed)
- TYPO3 Version changed from 11 to 10
- Description updated (diff)
- Related to Epic #90719: PostgreSQL related issues added
- 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.
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)];
}
/**
- 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.
- Status changed from Accepted to Under Review
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Also available in: Atom
PDF