Unser täglich MySQL-Rant gib uns heute…

Mein heutiger Quell der Freude: das Buch “High Performance MySQL”.

Im Einleitungskapitel werden nacheinander die beiden wesentlichen Storage-Engines (InnoDB und MyISAM) vorgestellt, wobei die Eigenschaften von MyISAM erfrischend … nunja, ehrlich beschrieben werden.

MyISAM features
As one of the oldest storage engines included in MySQL, MyISAM has many features that have been developed over years of use to fill niche needs:

Soweit, so gut. Mal sehen, welche “niche needs” erfüllt wurden:

Locking and concurrency
[…] Writers obtain exclusive (write) locks. However, you can insert new rows into the table while select queries are running against it.

Ok… – an dieser Stelle sei erinnert, dass MyISAM keine Transaktionen beherrscht. Aber es geht noch beeindruckender:

Repair
MySQL supports manual and automatic checking and repairing of MyISAM tables […]. After repairing a table, you’ll likely find that some data ist simply gone. Repairing is slow, too.

“Und langsam ist es auch noch!” – na dagegen ist das “Einige deiner Daten sind hochwahrscheinlich verlustig gegangen” ja fast nebensächlich.

Zur Einleitung zurückkehrend haben wir also gelernt, dass MyISAM jahrelang konsequent weiterentwickelt wurde, um unter anderem endlich die lang vermisste Eigenschaft sich sporadisch selbst entleerender Tabellen anzubieten?

Unser täglich MySQL-Rant gib uns heute…

Ich war ja vor einiger Zeit auf einer MySQL-Schulung (siehe auch die zugehörigen Essensbilder) und zwar mit dem Ziel oder zumindest der Hoffnung, von einem “ernsthaften” MySQL-Administrator Lösungen oder zumindest Workarounds für die unzähligen, bisher bei uns aufgetretenen Unzulänglichkeiten dieses Pseudo-DBMS zu erhalten.

Leider wurden meine ob des Themas zugegebenermaßen niedrigen Erwartungen an die Schulung noch deutlich unterboten, aber wenigstens konnte ich einen wertvollen Einblick erhalten, wie Leute ticken, die mit MySQL-Consulting ihr Geld verdienen: Zu so ziemlich allen Problemen, die mein ebenfalls anwesender Kollege und ich nannten (z.B. die Unmöglichkeit, MyISAM-Dumps im laufenden Betrieb zu ziehen), lautete die hochprofessionelle Antwort, dass das halt so sei und man daran auch nichts ändern könne.

Mein absolutes Highlight war allerdings seine Aussage (inhaltlich wiedergegeben):

“Wozu braucht man denn Fremdschlüssel? Für die Konsistenz der Daten reicht es doch, wenn die zugreifenden Applikationen nur konsistente Daten an die Datenbank liefern. Eine Prüfung der referentiellen Integrität in der Datenbank verbraucht doch nur unnötig Zeit!”

Ich lass das einfach mal unkommentiert so stehen, erinnere aber nochmal daran, dass der Herr sein Geld mit Datenbankconsulting (nagut, MySQL-Consulting) verdient…

Anyway, heute wurde mir dieses Zitat noch einmal in Erinnerung gerufen, als ich vor dem Problem stand, dass sich ein frisch gezogener MySQL-Dump nicht in eine jungfräuliche MySQL-Datenbank einspielen ließ (ziemlich genau das hier beschriebene Problem mit der Universalfehlermeldung “ERROR 1005 (HY000)”).

Das grundsätzliche Problem besteht darin, dass gleich am Anfang des Dumps die Anlage der Tabellen und der Fremdschlüssel synchron erfolgt (jeweils alles in einem CREATE-TABLE-Statement). Dadurch existieren jedoch zu etlichen, frisch definierten Schlüsseln die referenzierten Tabellen (noch) nicht, so dass infolgedessen das gesamte CREATE TABLE fehlschlägt. (Kurz bei Postgres vorbeigeschaut: Bei pgdump werden im Dumpfile erst die Tabellen ohne Schlüssel angelegt, dann die Tabellen gefüllt und erst zum Ende die Fremdschlüsselbeziehungen definiert und dabei natürlich auch geprüft).

Nun gibt es aber auch für dieses Problem eine MySQL-Lösung, deren schlichte Existenz einen DBA zum Schreien bringen sollte: Man kann in MySQL die gesamte Prüfung der referentiellen Integrität abschalten!!!!!111elf.

Und zwar so richtig abschalten. Richtig im Sinne von: auch wenn die referentielle Integrität wieder aktiviert wird, führt dies nicht zu einer Prüfung, ob die Datenbank überhaupt noch in einem konsistenten Zustand ist!

Kleines Beispiel zum Gruseln:

Wir legen zwei Tabellen an, wobei die zweite die erste via Fremdschlüssel referenziert:

mysql> create table foo (a int not null, PRIMARY KEY(a) );
Query OK, 0 rows affected (0.02 sec)
mysql> create table bar (b int, FOREIGN KEY (b) REFERENCES foo (a));
Query OK, 0 rows affected (0.01 sec)


Ein Einfügen von Werten in foo ist generell unproblematisch, in bar sind hingegen nur Werte erlaubt, die schon in foo stehen:

mysql> insert into foo values (1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into bar values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into bar values (0);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytest`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`b`) REFERENCES `foo` (`a`))


Und nun kommt’s:

mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into bar values (0);
Query OK, 1 row affected (0.00 sec)
mysql> set foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)


Und ja, die Daten sind wirklich drin:

mysql> select * from bar;
+------+
| b |
+------+
| 0 |
| 1 |
+------+
2 rows in set (0.00 sec)


Wer noch mehr schlechte Laune braucht, kann sich mal das MySQL-Handbuch zum Thema “Fremdschlüssel-Beschränkungen” zu Gemüte führen. Darin sind solche Perlen enthalten wie:

Abweichung von SQL-Standards: Ein FOREIGN KEY-Constraint, der einen Nicht-UNIQUE-Schlüssel referenziert, ist nicht Standard-SQL, sondern eine InnoDB-Erweiterung dieses Standards.

oder

Hinweis: Trigger werden von kaskadierenden Fremdschlüsselaktionen derzeit nicht aktiviert.

So, Feierabend, mir reicht’s für heute…

Das folgende wird ein etwas weniger humoristischer Beitrag über ein Thema, das sich inzwischen zu einem meiner Hauptprobleme als Datenbankadministrator entwickelt hat: BLOBs in Datenbanken. Ich werde versuchen, das Ganze etwas ausführlicher zu beschreiben, damit ich später BLOB-geneigte Datenbanknutzer für einen Einstieg in die Problematik auf diese Seite verweisen kann.

Fangen wir also ganz harmlos an: was bedeutet BLOB? Das Wort ist überraschenderweise kein echtes Akronym, sondern wurde laut Wikipedia von einem Mitarbeiter bei DEC erfunden, um einen “Haufen unstrukturierter Daten” (freie Übersetzung von mir) zu bezeichnen:

Blobs were originally just amorphous chunks of data invented by Jim Starkey at DEC, who describes them as “the thing that ate Cincinnati, Cleveland, or whatever”.

Erst später tauchten die Backronyme “Basic Large Object” bzw. “Binary Large Object” auf.

Doch was sind BLOBs nun genau? Grob gesagt stellen jegliche Daten, die im jeweiligen Kontext unstrukturiert erscheinen, BLOBs dar. Damit sind sowohl tatsächlich unstrukturierte Daten gemeint als auch, was der weitaus häufigere Fall ist, strukturierte Daten, deren innere Struktur im Kontext jedoch nicht erfasst und somit auch nicht genutzt werden kann.

Am Beispiel etwas konkreter: eine MP3-Datei hat zwar durchaus einen wohldefinierten, strukturierten Aufbau (ein Dateiheader gefolgt von Musikdaten, die in exakt spezifizierten Frames vorliegen, letztere widerum mit jeweils einem eigenen Header versehen). Dennoch stellt eine MP3-Datei im Kontext von Datenbanken einen BLOB dar, da die Datenbank mit dieser inneren Struktur überhaupt nichts anfangen kann. Sie kann lediglich den gesamten “Datenhaufen” 1:1 in ein BLOB-Feld kopieren. Dasselbe gilt für alle in Dateien gekapselten Daten (Graphiken, Videos, Office-Dokumente, usw. aber auch reine Textdateien), wenn keine Anwendung vor die Datenbank geschaltet wird, die die Daten “auseinandernimmt” und eventuell auch noch weiter aufbereitet.

Eine zentrale Eigenschaft, durch die sich BLOBs von den restlichen Daten in einer Datenbank unterscheiden, liegt darin, dass die einzigen Datenbankoperationen im jeweils vollständigen Lesen oder Schreiben des gesamten BLOB-Datenfeldes bestehen. Andere Operationen, die eigentlich den Ausschlag für den Einsatz von Datenbanken darstellen (sortieren, suchen, filtern, usw.) sind nicht möglich. Auch in komplexeren Datenbankabfragen können BLOB-Felder maximal in der Form von “… und dann hängen wir in der Ergebnistabelle noch den BLOB dran” auftreten.

Nachdem nun also feststeht, was BLOBs sind, wird sich der geneigte Leser und insbesondere der unbedarfte Datenbanknutzer fragen, was denn nun das Problem sei. Es gibt doch in jeder besseren Datenbank (und auch bei MySQL) entsprechende Datentypen, also rein damit und gut ist…

Fassen wir also zunächst zusammen, warum Datenbanknutzer (verständlicherweise) von BLOBs so begeistert sind:

• die Daten sind im Datenbankschema sauber eingebunden und (hoffentlich durch echte Schlüsselbeziehungen – bei MySQL/MyISAM nicht möglich) referenziert – die Daten können weder verwaisen, noch können die Referenzen auf die Daten auf einmal ins Leere zeigen
• in einem Datenbank-Dump sind sämtliche Daten enthalten: die strukturierten Daten und die BLOBs – dies erleichtert sowohl die Portierung als auch das komplette Zurücksetzen in einen früheren Zustand (Stichwort Backups) ungemein
• je nach Datenbanksystem ist es möglich, die BLOB-Daten durch programmierbare Operationen auf dem Datenbankserver (Trigger, Rules, …) zu bearbeiten
• die Rechteverwaltung (welcher Nutzer darf was?) ist deutlich leichter zu implementieren, wenn die BLOBs mit in der Datenbank liegen

Die Probleme, mit denen der oben beschriebene Komfort (und es geht hier ausschließlich um Komfort) erkauft werden, sieht der Nutzer nicht – ganz im Gegensatz zum Datenbankadministrator.

Grob kann man festhalten, dass BLOBs in Datenbanken immer ineffizient sind und zwar sowohl was die Zugriffszeit als auch den letztendlich verbrauchten Speicher angeht. Begründet liegt dies darin, dass bei allen Datenbanksystemen (und in Teilen auch bei MySQL) Datenkonsistenz die oberste Priorität besitzt.

So kann ein Datenbankserver z.B. nicht wie ein Fileserver dem Nutzer den Dateiinhalt einfach an den Kopf werfen (also lediglich: Datei lesen und den Inhalt an den Nutzer weiterleiten). Nein, ein Datenbankserver wird bei einem Zugriff auf einen BLOB (mindestens) folgende Operationen durchführen:

• prüfen, ob der Datenbanknutzer die Berechtigung zum Zugriff auf die Datei hat (über mehrere Hierarchieebenen hinweg – Datenbankrechte sind in der Regel umfangreicher als Filesystemrechte aufgebaut)
• prüfen, ob der BLOB gerade anderweitig gelesen/geschrieben wird oder sonstwie temporär gesperrt ist (dies dient der Sicherung der Datenkonsistenz – es soll verhindert werden, dass sich die Daten während des Zugriffs ändern könnten); falls ja, die Anfrage in einer Warteschlange eintragen
• sobald ein Zugriff möglich ist, diesen in den entsprechenden Systemtabellen eintragen, damit die folgenden Zugriffe bei Bedarf hingehalten werden können
• (nur zur Erinnerung: bis hierher wurde noch kein Byte des BLOBs gelesen!)
• die Datei aus dem datenbankinternen Format in das ursprüngliche Format umwandeln (eventuell sind je nach Datenbankconnector noch weitere Umwandlungen nötig)
• die Datei ausliefern
• die eingetragenen Sperren wieder entfernen

… und je nach Datenbanksystem wird der gesamte Zugriff auch noch parallel in einer separaten Textdatei protokolliert. Spätestens an dieser Stelle sollte einleuchtend sein, warum es aus Effizienzgründen eine blöde Idee ist, einen Datenbankserver als Fileserver zu missbrauchen – es wird aber noch schlimmer…

Stichwort Backups. Jeder, der sich schon einmal ernsthaft mit Backups beschäftigt hat, kennt die goldene Methode, um die drei gegeneinander konkurrierenden Größen Backuplaufzeit, Rückspielzeit und Backupgröße miteinander in Einklang zu bringen: inkrementelle Backups.

Als Datenbankadministrator muss man lernen, mit einer unbequemen Wahrheit zu leben: es gibt keine inkrementellen Backups! Zur Sicherung der Datenkonsistenz (Erinnerung: oberste Priorität) besteht jedes Backup zwangsläufig aus einem vollständigen Datenbankdump.

(Kleine Fußnote der Vollständigkeit halber: man kann bei echten Datenbanksystemen durch eine explizite, separate Sicherung der Write-Logs eine Art inkrementelles Backup simulieren, was auch durchaus nicht selten praktiziert wird, aber im Falle der BLOBs die Probleme auch nur verlagert – davon abgesehen, dass eine Backup-Strategie immer so trivial und unkompliziert wie möglich sein sollte.)

Dazu ein Beispiel aus unserer Praxis zur Verdeutlichung. Bei unseren PostgreSQL-Datenbankservern erfolgt einmal täglich ein Datenbankdump aller Datenbanken, wobei die Datenbanken jeweils erst einmal einzeln und dann noch einmal die gesamte Datenbankinstanz in einem eigenen Dump abgezogen werden.

Diese Datenbankdumps heben wir derzeit für 100 Tage auf (darüber hinaus heben wir noch die monatsersten Dumps ein Jahr lang auf und die jahresersten Dumps bisher ohne Begrenzung, des Weiteren machen wir noch ein zweites Backup pro Tag, das wir kürzer aufheben, aber für das folgende Rechenbeispiel beschränken wir uns auf die 100 Tage). Daraus folgt, dass jedes Byte in unserer Datenbank auf dem Backupserver das 200fache als Speicherplatz benötigt.

Dazu ein paar Rechenbeispiele: eine 100kB-Graphikdatei benötigt auf dem Backupserver langfristig nicht weniger als 19MB (in Wirklichkeit sogar noch etwas mehr, da die BLOBs im Datenbankdumpformat auch nochmal aufgebläht werden). Eine 578kB-Worddatei (Beispieldatei eines BLOB-geneigten Datenbanknutzers) belegt 112MB. Was mit Daten im MB-Bereich passiert, kann sich der geneigte Leser selbst ausrechnen.

Und nun haben wir Anfragen im zweistelligen GB-Bereich (und einen Ausreißer im dreistelligen GB-Bereich) erhalten…

Von unseren PostgreSQL-Servern konnten wir BLOBs bisher weitestgehend fernhalten, bei unseren MySQL-Servern ist das Kind schon in den Brunnen gefallen (was unter anderem dazu geführt hat, dass wir die obige Backup-Strategie zum Leidwesen der mehrheitlich BLOB-losen Nutzer dort nicht fahren können). Dadurch wurden wir aber auch schon mit einem weiteren, echten Problem konfrontiert: das Abziehen von riesigen, BLOB-lastigen Datenbanken kostet Zeit – viel Zeit – zuviel Zeit.

Denn zusätzlich besteht bei MySQL (auf Basis von MyISAM) das Problem, dass man Datenbanken nicht im laufenden Betrieb abziehen kann. Die Datenbank muss also für die Zeit des Datenbankdumps gesperrt werden. Was dies bedeutet, wenn die Dauer eines Dumps im Stundenbereich liegt, kann sich der geneigte Leser selbst ausmalen. Erfreulicherweise wurden wir bisher von dem Erlebnis von Kollegen verschont, bei denen BLOB-bedingt Backupzeiten von mehr als 24h auftraten – was bei täglichen Backups ziemlich unpraktisch ist und wo dann auch “echte” Datenbanksysteme nicht mehr weiterhelfen könnten.

Bleibt also die Frage, welche sinnvollen Optionen nun für einen Datenbanknutzer bestehen, der neben strukturierten Daten auch BLOBs sinnvoll verwalten möchte. Nun, es gibt eine Lösung, die sogar älter ist als BLOBs selbst (und zwar schlichtweg deshalb, da sie aus einer Zeit kommt, als Datenbanken noch keine BLOBs aufnehmen konnten): Referenzen.

Gemeint sind damit Referenzen, die man in der Datenbank (in Textfeldern) hinterlässt und die auf BLOB-Daten verweisen. Dabei kann es sich beispielsweise um vollqualifizierte Verzeichnis- und Dateinamen auf einem Fileserver oder URLs, die sonstwie auf den eigentlichen Speicherort des BLOBs verweisen, handeln. Der einzige, wirklich kritische Nachteil dieser Lösung besteht darin, dass die Sicherstellung der Konsistenz zwischen Datenbank- und Fileserverinhalt der zugreifenden Applikation aufgebürdet wird. Auch beim Backup muss man nun aufpassen, dass man Datenbank und Fileserver möglichst synchron sichert. Ansonsten löst dieser Ansatz aber schlagartig alle oben genannten Probleme.

Zu guter letzt noch, Ehre wem Ehre gebührt: dieser Artikel hat mir beim Schreiben sehr geholfen. In dem Artikel, der die gesamte Problematik vor allem aus der Sicht per PHP angebundener Webdatenbanken betrachtet, werden auch noch ein paar weitere Gründe gegen BLOBs genannt, die in diesem speziellen Kontext zusätzlich auftreten.

Auch diesen Artikel zu dem Thema fand ich sehr lesenswert. Der Autor ist ein DB-Admin, der für sehr große Datenmengen zuständig ist. Neben interessanten Erfahrungsberichten aus seiner Praxis und den oben genannten Gründen greift er unter anderem auch noch den Kostenaspekt auf (den er sehr zugespitzt formuliert: “If you’re storing your BLOBs, especially large media files in database, aren’t you choosing the most expensive file system available?”).

Kurzzusammenfassung all des Gesagten (siehe auch Subject): Du sollst nicht BLOBben!

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 ...

David Wheeler wird das folgende herrliche Zitat zugeschrieben (wobei gerne nur der erste Teil zitiert wird):

Any problem in computer science can be solved with another layer of indirection. But that usually will create another problem.

Was hat nun MySQL damit zu tun? Nun, die nehmen den ersten Teil wörtlich. Gegeben sei folgende Tabelle

mysql> create table foo (a int, b int);
mysql> insert into foo values (1,2);
mysql> insert into foo values (3,4);


Nun möchte ich folgendes versuchen:

mysql> update foo set a = (select a from foo where b = 2) where b = 4;
ERROR 1093 (HY000): You can't specify target table 'foo' for update in FROM clause


Damit kommen wir zu David Wheeler:

mysql> update foo set a = (select a from (select * from foo where b = 2) as x) where b = 4;
Query OK, 1 row affected (0.00 sec)


Kein weiterer Kommentar…