Meine Vorliebe für Postgres (insbesondere im Gegensatz zu einem anderen bekannten Open-Source-Datenbanksystem ;-)) ist ja eigentlich hinlänglich bekannt. Seit vorgestern mag ich Postgres noch etwas mehr…

Im Gegensatz zum SQL-Standard erlaubt Postgres in Triggern lediglich den Aufruf einer Funktion (statt beliebigem SQL-Code, was aber nicht weiter problematisch ist, da man diesen bei Bedarf halt nur in einer Funktion kapseln muss). Erfreulicherweise kann man aber dieselbe Funktion in beliebig vielen verschiedenen Triggern rufen und genau das ist die Grundlage für meine (trommelwirbel) generische Log-Funktion.

Generisch ist diese Funktion dahingehend, dass man diese Funktion unverändert für (fast) beliebige Tabellen in beliebigen Datenbanken verwenden kann. Die Funktionsweise ist von der Idee her ziemlich einfach (und die konkrete Umsetzung hinreichend eklig, dass sie IMHO einen Blogeintrag lohnt):

Zuerst benötigen wir eine Log-Tabelle, in der später bei jeder Modifikation der überwachten Tabellen (bei INSERT, UPDATE oder DELETE) folgende Informationen gespeichert werden: wer hat wann in welcher Tabelle (+Schemaname) in welcher Zeile (Angabe des Primärschlüsselwertes) in welcher Spalte welchen alten Wert auf welchen neuen Wert gesetzt? Das unten zu sehende CREATE TABLE (Zeile 1 bis 13) erstellt uns eine solche Tabelle.

Das Befüllen dieser Tabelle übernimmt nun die anschließend zu sehende generische Log-Funktion. Zunächst wird (Zeile 24 bis 32) die Primärschlüsselspalte der modifizierten Tabelle ermittelt. Danach wird über alle Spalten der modifizierten Tabelle iteriert (Zeile 34 bis 126) und dabei werden je nach konkreter Operation (INSERT, DELETE, UPDATE) verschiedene SELECT-Statements zusammengebaut (und unmittelbar danach per EXECUTE gerufen), die die alten bzw. neuen Werte der geänderten Spalten ermitteln. Jeweils anschließend werden alle ermittelten Werte als neue Zeile in die Logtabelle eingetragen.

Da die Funktion den konkreten Aufbau der überwachten Tabelle dynamisch ermittelt, kann sie in INSERT-, DELETE- und UPDATE-Triggern von (fast) beliebigen Tabellen hinzugefügt werden, was exemplarisch in Zeile 133-135 zu sehen ist.

Bevor ich nun endlich den angepriesenen Code zeige, sind noch ein paar Hinweise notwendig. Zunächst, Ehre wem Ehre gebürt, war diese Seite der Ausgangspunkt meiner Konstruktion. Die dort zu sehende Funktion war jedoch noch stark verbesserungsbedürftig (mehrfach sinnfreie Variablensubstitutionen, jeweils exakt einmal durchlaufende Schleifenkonstruktionen und lediglich Unterstützung von UPDATE-Triggern). Ich habe die dortige Funktion erstmal optisch saniert, dann semantisch-äquivalent reduziert, dann verstanden (ja, in dieser Reihenfolge) und danach um die zwei fehlenden Trigger-Operationen erweitert.

Frank hat mich freundlicherweise auf eine Einschränkung hingewiesen: die Funktion funktioniert nur in Tabellen, deren Primärschlüssel aus exakt einer Spalte besteht. Zumindest bei den von mir entworfenen Datenbanken ist dies immer der Fall, aber Vertreter einer anderen reinen Lehre 🙂 müssen an dieser Stelle aufpassen.

Des Weiteren muss natürlich berücksichtigt werden, dass die Funktion durch ihren Ich-muss-jetzt-erstmal-selbst-das-Schema-verstehen-Ansatz Zeit benötigt, die bei jeder getriggerten Operation anfällt. Bei den von mir betreuten Datenbanken ist auch dies kein Problem, ich würde aber zum Beispiel einem Telekommunikationsanbieter davon abraten, diese Funktion für eine zentrale Session-Datenbank verwenden zu wollen 🙂

Falls jemand die (nun wirklich unmittelbar) folgende Funktion wirklich einsetzt, würde ich mich über Rückmeldungen freuen…

Update (2013-11-06):

Zeile 37 musste um "AND NOT attisdropped" erweitert werden. Sobald man nämlich eine Spalte in einer Tabelle löscht, ist sie in der Systemtabelle immer noch unter dem künstlichen Namen "........pg.dropped.xx........" sichtbar. Dies führt jedoch dazu, dass die Logfunktion auch den alten Wert der derartig benannten Spalte ermitteln möchte, was natürlich fehlschlägt. Das genannte AND-NOT-Anhängsel filtert die gelöschten Spaltennamen aus.

  1 CREATE TABLE log (
  2    log_id BIGSERIAL NOT NULL PRIMARY KEY,
  3    log_action VARCHAR NOT NULL,
  4    log_timestamp TIMESTAMP NOT NULL DEFAULT(NOW()),
  5    log_user VARCHAR NOT NULL DEFAULT(current_user),
  6    log_schema VARCHAR NOT NULL,
  7    log_table VARCHAR NOT NULL,
  8    log_primary_key VARCHAR NOT NULL,
  9    log_column VARCHAR NOT NULL,
 10    log_old_value VARCHAR NULL,
 11    log_new_value VARCHAR NULL,
 12    log_query VARCHAR NULL
 13 );
 14 
 15 CREATE OR REPLACE FUNCTION log_function() RETURNS TRIGGER AS $$
 16 
 17    DECLARE mycolumn RECORD;
 18    DECLARE column_name VARCHAR;
 19    DECLARE old_value VARCHAR;
 20    DECLARE new_value VARCHAR;
 21    DECLARE pkey_column_name VARCHAR;
 22    DECLARE pkey_value VARCHAR;
 23 
 24    BEGIN
 25       SELECT INTO pkey_column_name kcu.column_name
 26       FROM
 27          information_schema.table_constraints AS tc
 28          JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
 29          JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 30       WHERE
 31          constraint_type = 'PRIMARY KEY' AND ccu.table_name = tg_table_name
 32       ;
 33 
 34       FOR mycolumn IN
 35          SELECT attname AS column_name
 36          FROM pg_attribute
 37          WHERE attrelid = ( SELECT oid FROM pg_class WHERE relname = tg_table_name ) AND attnum > 0 AND NOT attisdropped
 38       LOOP
 39          IF (TG_OP = 'UPDATE') THEN
 40             EXECUTE 'SELECT text((' || QUOTE_LITERAL(NEW) || '::"' || tg_table_schema || '"."' || tg_table_name || '")."' || pkey_column_name || '")' INTO pkey_value;
 41             EXECUTE 'SELECT text((' || QUOTE_LITERAL(NEW) || '::"' || tg_table_schema || '"."' || tg_table_name || '")."' || mycolumn.column_name || '") ' INTO new_value;
 42             EXECUTE 'SELECT text("' || mycolumn.column_name || '") 
 43                FROM "' || tg_table_schema || '"."' || tg_table_name
 44                || '" WHERE "' || pkey_column_name || '"= ('
 45                || QUOTE_LITERAL(NEW) || '::"' || tg_table_schema || '"."'
 46                || tg_table_name || '")."' || pkey_column_name || '";'
 47                INTO old_value;
 48 
 49             IF old_value != new_value THEN
 50                INSERT INTO log (
 51                   log_action,
 52                   log_table,
 53                   log_schema,
 54                   log_primary_key,
 55                   log_column,
 56                   log_old_value,
 57                   log_new_value,
 58                   log_query
 59                ) VALUES (
 60                   tg_op,
 61                   tg_table_name,
 62                   tg_table_schema,
 63                   pkey_value,
 64                   mycolumn.column_name,
 65                   old_value,
 66                   new_value,
 67                   current_query()
 68                );
 69             END IF;
 70 
 71          ELSIF (TG_OP = 'DELETE') THEN
 72             EXECUTE 'SELECT text((' || QUOTE_LITERAL(OLD) || '::"' || tg_table_schema || '"."' || tg_table_name || '")."' || pkey_column_name || '")' INTO pkey_value;
 73             EXECUTE 'SELECT text("' || mycolumn.column_name || '") 
 74                FROM "' || tg_table_schema || '"."' || tg_table_name
 75                || '" WHERE "' || pkey_column_name || '"= ('
 76                || QUOTE_LITERAL(OLD) || '::"' || tg_table_schema || '"."'
 77                || tg_table_name || '")."' || pkey_column_name || '";'
 78                INTO old_value;
 79 
 80             IF old_value IS NOT NULL THEN
 81                INSERT INTO log (
 82                   log_action,
 83                   log_table,
 84                   log_schema,
 85                   log_primary_key,
 86                   log_column,
 87                   log_old_value,
 88                   log_query
 89                ) VALUES (
 90                   tg_op,
 91                   tg_table_name,
 92                   tg_table_schema,
 93                   pkey_value,
 94                   mycolumn.column_name,
 95                   old_value,
 96                   current_query()
 97                );
 98             END IF;
 99 
100          ELSIF (TG_OP = 'INSERT') THEN
101 
102             EXECUTE 'SELECT text((' || QUOTE_LITERAL(NEW) || '::"' || tg_table_schema || '"."' || tg_table_name || '")."' || mycolumn.column_name || '") ' into new_value;
103             EXECUTE 'SELECT text((' || QUOTE_LITERAL(NEW) || '::"' || tg_table_schema || '"."' || tg_table_name || '")."' || pkey_column_name || '")' into pkey_value;
104 
105             IF new_value IS NOT NULL THEN
106                INSERT INTO log   (
107                   log_action,
108                   log_table,
109                   log_schema,
110                   log_primary_key,
111                   log_column,
112                   log_new_value,
113                   log_query
114                ) VALUES (
115                   tg_op,
116                   tg_table_name,
117                   tg_table_schema,
118                   pkey_value,
119                   mycolumn.column_name,
120                   new_value,
121                   current_query()
122                );
123             END IF;
124 
125       END IF;
126    END LOOP;
127    IF (TG_OP = 'DELETE') THEN RETURN OLD; END IF;
128    RETURN NEW;
129 END
130 $$
131 LANGUAGE plpgsql;
132 
133 CREATE TRIGGER log_service BEFORE UPDATE OR INSERT OR DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE log_function();
134 CREATE TRIGGER log_service BEFORE UPDATE OR INSERT OR DELETE ON table2 FOR EACH ROW EXECUTE PROCEDURE log_function();
135 CREATE TRIGGER log_service BEFORE UPDATE OR INSERT OR DELETE ON table3 FOR EACH ROW EXECUTE PROCEDURE log_function();
136 ...