Change column type with dependencies

12.07.2016, peter.korduan@gdi-service.de
Neulich stellten wir fest, dass im PostNAS Datenbankschema der Datentyp für ein Attribut in einer Tabelle geändert werden muss. Das ist erstmal nicht so problematisch. Die Spalte anlass in der Tabelle delete im Schema alkis lässt sich mit folgendem Befehl z.B. vom Typ character varying in ein Array character varying[] umwandeln.

ALTER TABLE alkis.delete ALTER COLUMN anlass TYPE character varying[] USING array[anlass]';

Nun hat die Tabelle alkis.delete aber ein trigger, der für das jeweils zu löschende Objekt in der dazugehörigen Tabelle des alkis-Schemas in der Spalte anlass einen Eintrag ändert. All diese Spalten müssen also auch geändert werden. Das lässt sich über eine Schleife machen. Dazu werden in einer Abfrage alle Tabellen abgefragt, die im Schema alkis vorkommen und eine Spalte anlass haben.
So geschehen auch in der Migration vom Norbit-Schema, siehe https://github.com/norBIT/alkisimport/commit/ff1f7b47be8d2a75f4517b1bc8c...

Das ist aber noch nicht alles. Wenn man Views hat, kann man nicht einfach die Typen all dieser Tabellen ändern, weil einige Views von diesen Tabellen abhängen. Man muss also vorher all diese abhängigen Views löschen und anschließend wieder herstellen. Welche Schritte sind also im einzelnen auszuführen?

  1. Sichten dumpen
    Im Foldenden findet sich ein Dump-Befehl, der die zu dumpenden Views über eine Query abfragt. Die Query (Der Text in Anführungszeichen hinter -c) liefert dank dem Parameter -t (hinter den Anführungszeichen) eine Zeichenkette aller Views aus dem Schema alkis mit jeweils einem vorangestelltem -t . Diese -t Optionen teilen dem Dump-Befehl mit welche views gedumpt werden sollen. "> alkis_views.sql" Leitet die Sicherung der Dumps in die Datei alkis_views.sql. Genau diese SQL-Datei muss am Ende wieder ausgeführt werden. Der Nutzername kvwmap, der Schemaname alkis und der Datenbankname kvwmapsp muss ggf. durch die eigenen Namen ersetzt werden.

    pg_dump -U kvwmap -Fp --schema=alkis -s $(psql -U kvwmap -c "SELECT string_agg( '-t alkis.' || quote_ident(relname), ' ' ) FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = 'v' AND nspname = 'alkis' AND NOT (nspname ~ '^pg_' OR nspname = 'information_schema')" -t kvwmapsp) kvwmapsp

  2. Sichten löschen
    Jetzt wo die Sichten gesichert sind, können diese mit folgendem Befehl gelöscht werden:
    DO
    $$
    DECLARE
    c record;
    BEGIN
    FOR c IN
    SELECT
    quote_ident(nspname) || '.' || quote_ident(relname) AS view_name
    FROM
    pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE
    relkind = 'v' AND nspname = 'alkis' AND NOT (nspname ~ '^pg_' or nspname = 'information_schema')
    LOOP
    EXECUTE 'DROP VIEW IF EXISTS ' || c.view_name || ' CASCADE';
    END LOOP;
    END
    $$
  3. Spalten ändern
    Da jetzt die Abhängigkeiten durch die Views weg sind dürfte die Änderung an den Spalten anlass in den Tabellen wie folgt funktionieren:
    DO
    $$
    DECLARE
    t record;
    BEGIN
    FOR t IN
    SELECT
    table_name
    FROM
    information_schema.columns a
    WHERE
    a.table_schema='alkis'
    AND a.column_name='anlass'
    AND a.data_type='character varying'
    LOOP
    EXECUTE 'ALTER TABLE alkis.' || t.table_name::text || ' ALTER COLUMN anlass TYPE character varying[] USING string_to_array(anlass, '' '')';
    END LOOP;
    END
    $$
  4. Views wieder herstellen
    Jetzt wo die Spalten geändert wurden können die views mit folgendem Befehl wieder hergestellt werden.

    psql -U kvwmap -f /var/www/data/alkis/alkis_views.sql kvwmapsp

    Auch hier gilt wieder Nutzername und Datenbankname ggf. durch eigenen ersetzen.

Das wars. Im Prinzip lässt sich die Vorgehensweise immer anwenden wenn in einem Schema in Tabellen Spalten geändert werden müssen, von denen Views abhängen. Ein Hinweis noch: Die Problematik, dass man die Spalte anlass in allen möglichen Tabellen ändern muss ist dem geschuldet, dass in dem Schema noch jede Tabelle dieses Attribut hat obwohl alle Tabellen diese Eigenschaft eigentlich vom Objekt AA_Objekt erben. Würde man also die ganzen Tabellen vom alkis Schema über Vererbung anlegen bräuchte man die Spalte anlass nur in einer Tabelle ändern. Der Tabelel AA_Objekt.