Veränderte collation

Hallo,

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;

Oder übersehe ich da (mal wieder) was?

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');
1 „Gefällt mir“

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.

1 „Gefällt mir“

Vielen Dank für den Schubs in die richtige Richtung

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.

Hier ist ein ausführlicherer Artikel zum Thema.

1 „Gefällt mir“

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.