auf Grund eines DB-Updates steht uns eine Collation Änderung in’s Haus. Soweit nicht dramatisch, abgesehen von der Größe der DB. Die liegt im 3 stelligen TB Bereich.
Grundsätzlich sollte es doch reichen, wenn ich nur diese Indexe neu baue:
SELECT ‘REINEX INDEX’ || indexrelid::regclass || ‘;’ FROM pg_index WHERE not indcollation && ARRAY[0]::oidvector;
Im Postgres Wiki wird für dieses Thema die folgende Abfrage vorgeschlagen:
SELECT DISTINCT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');
Das Problem der ursprünglichen Abfrage ist, daß sie bei mehrspaltigen Indexen die nicht ausgibt, bei denen nicht alle Spalten eine Collation haben.
Hier ist mein Vorschlag:
SELECT DISTINCT
i.indexrelid::regclass AS "index",
i.indrelid::regclass AS "table"
FROM pg_index AS i
CROSS JOIN LATERAL unnest(i.indcollation) AS c(collid)
LEFT JOIN pg_collation AS col ON c.collid = col.oid
WHERE /* hat eine Collation, die sich ändern kann */
coalesce(col.collname, 'C') NOT IN ('C', 'POSIX', 'ucs_basic')
/* für Paranoiker: hat eine WHERE-Bedingung */
OR i.indpred IS NOT NULL
/* für Paranoiker: ist auf einem Ausdruck definiert */
OR i.indexprs IS NOT NULL;
Die beiden Klauseln “für Paranoiker” geben auch alle Indexe aus, die eine WHERE-Bedingung oder einen Ausdruck drinnen haben. Bei denen könnte es theoretisch auch Probleme geben. Wenn z.B. so ein Ausdruck in einem der beiden vorkommt:
length(upper(col))
dann könnte das Ergebnis, obwohl numerisch, sich bei einer Änderung der Collation verändern. In manchen Collations ist upper(‘Fußball’) gleich FUßBALL, in anderen FUSSBALL, und die haben unterschiedliche Länge.
In der Praxis würde ich meine Abfrage laufen lassen und dann die Indexe mit Ausdrücken oder WHERE-Bedingungen unter die Lupe nehmen, ob sie in irgendeiner Weise anfällig sind.
Ja, grundsätzlich reicht es, nur die Indizes mit nicht standardmäßiger Collation (not indcollation) neu zu bauen. Bei sehr großen TB-Datenbanken kann es aber trotzdem lange dauern, und Prüfen auf abhängige Views oder Constraints schadet nicht.