AUTO_INCREMENT mit PostgreSQL #

In jedem besseren Datenbanksystem gibt es eine Methode, um Schlüsselfelder automatisch zu erzeugen. Das bedeutet, daß ich mehrere Datensätze in die Datenbank einfügen kann und jeder dieser Sätze einen eindeutigen (zumeist numerischen) ID-Wert zugeordnet bekommt. Auf diese Art kann ich später jeden einzelnen Datensatz genau identifizieren. Man kann die Erzeugung eines solchen eindeutigen Schlüssels der Applikation überlassen oder dies in der Datenbank machen. Letzteres ist in vielen Fällen komfortabler und weniger fehleranfällig.

Der SQL-Standard benutzt für dieses Problem sogenannte Sequences. Dennoch haben sich verschiedene Methoden und Syntaxen durchgesetzt, mit dem Problem umzugehen. HSQLDB benutzt IDENTITY, MySQL hat AUTO_INCREMENT und PostgreSQL nimmt SERIAL.

welche Lösungen gibt es in verschiedenen Datenbanken #

Leider ist die Semantik dieser verschiedenen Lösungen überall leicht unterschiedlich:

• IDENTITY in HSQLDB (Doku) erlaubt es nicht, Werte selber anzugeben. Die ID wird IMMER erzeugt. IDENTITY impliziert immer UNIQUE.
• SERIAL in PostgreSQL (Doku) erlaubt es, eigene Werte anzugeben. Allerdings wird die zugrundeliegende Sequence davon nicht verändert, so daß man also in die Situation kommen kann, wo die SERIAL-Sequenz einen Wert liefert, den man vorher bereits manuell gesetzt hat. Konsequenterweise ist dann auch UNIQUE nicht automatisch impliziert (kann aber natürlich angefügt werden).
• AUTO_INCREMENT in MySQL (Doku) lässt die Angabe von eigenen Werten zu. Ein automatisch generierter Wert ist aber garantiert immer höher als der höchste bisherige Wert. Diese Semantik erscheint mir als die logischste. -- ThomasBayen

Darüber hinaus gibt es aber noch eine ganze Menge weiterer möglicher Herangehensweisen an das Problem. Insbesondere wer an grosse Datenbanken mit sehr vielen Benutzern denkt, die womöglich über einen Cluster von mehreren Servern verteilt sind und/oder eine optimale Performance erzielen will, merkt irgendwann, daß die Erzeugung von guten IDs nicht immer trivial ist. Einige sehr interessante Strategien muss man daher in der Applikation implementieren. Dies ist z.B. in JavaHibernate gemacht worden. Eine gute Übersicht gibt es in der Hibernate Dokumentation hierzu.

Wo liegt das Problem? (Was ist denn nun am besten) #

Für meine (ThomasBayen) Bibliothek (LugFramework) suche ich einen ID-Generator, der folgende Eigenschaften hat:

• Mehrbenutzerfähig
• unbedingtes Verhindern von doppelten Werten
• direktes Einfügen von eigenen Datensätzen in die Datenbank (also ohne meine Bibliothek) sollte keine Inkonsistenzen erzeugen (können)
• Es muss möglich sein, IDs bereits vorzugeben - ansonsten ist die Replikation einer Datenbank (bzw. auch das Kopieren von Teilen davon) mit vorhandenen Fremdschlüsseln nicht möglich.
• Betrieb auf mehreren Datenbanksystemen möglich (ich unterstütze zur Zeit HSQL, MySQL und PostgreSQL)
• Konfiguration in JavaHibernate möglich
• (wer weitere interessante Punkte hat, darf die hier gerne anfügen...)

Wenn man direktes Einfügen erlauben will, muss die gesamte ID-Logik zwangsläufig in der Datenbank stecken. Das bedeutet, man muss entweder mit den o.a. Eigenschaften oder mit Triggern arbeiten. Leider ist bei der Verwendung von Triggern die Plattformunabhängigkeit nicht mehr so ganz einfach herzustellen.

Meine Überlegungen ergeben, daß ich eigentlich genau das möchte, was MySQL mit AUTO_INCREMENT macht. Dummerweise benutzt mein Projekt im Moment PostgreSQL, so daß ich das dort implementieren möchte.

Umsetzung für PostgreSQL #

Nach eingehendem Studium der PostgreSQL Dokumentation ist meine Vorstellung, daß man am einfachsten SERIAL Spalten benutzt und diese dann mittels eines Triggers anpasst. Ich müsste also immer dann, wenn ein ID-Wert bereits vorgegeben wird, nachsehen, ob ich die zugrundeliegende Sequence ggf. auf einen höheren Wert setzen muss, damit weitere, automatisch erzeugte IDs immer größer sind als die manuell eingefügte. Wird kein ID-Wert vorgegeben (was der Normalfall sein sollte), wird der Trigger gar nicht ausgeführt, dann kommt die normale SERIAL-Logik zum Zuge. Auf diese Art wird im Normalfall der Zugriff nicht groß ausgebremst.

Eine Alternative könnte sein, daß man auf die Sequence ganz verzichtet und beim Einfügen immer ein MAX() ausführt. Laut der MySQL-Dokumentation wird das dort so gemacht. Ich denke jedoch, daß das von der Performance her nicht optimal ist. (Im übrigen denke ich, daß da auch die Doku nicht die Wahrheit sagt und intern bestimmt eine Art Sequence-Zähler benutzt wird.)

Einen Trigger setzt man mit dem CREATE TRIGGER-Befehl. Dieser ruft dann eine vorher definierte Funktion auf, die man einer von mehreren integrierten Sprachen, am einfachsten in PL/pgSQL, implementiert. In der Doku steht auch ein Beispiel für einen Trigger in PL/pgSQL.

Diesen Trigger habe ich nun folgendermassen implementiert:

-- DROP LANGUAGE IF EXISTS plpgsql CASCADE;
-- CREATE LANGUAGE plpgsql;

CREATE TABLE test(
   id serial unique,
   name char(20)
);

CREATE FUNCTION test_id_trigger() RETURNS TRIGGER AS $$
  BEGIN
    IF NEW.id IS NOT NULL THEN
      IF NOT (SELECT is_called FROM "test_id_seq") THEN
        -- bei ganz neuer Sequence ggf. zuerst initialisieren
        PERFORM nextval('test_id_seq');
      END IF;
      IF NEW.id > (SELECT last_value FROM "test_id_seq") THEN
        PERFORM setval('test_id_seq',NEW.id,true);
      END IF;
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_id_trigger BEFORE INSERT OR UPDATE ON test
  FOR EACH ROW EXECUTE PROCEDURE test_id_trigger();

Anmerkungen zu diesem Code:

• Obwohl in der Dokumentation steht, daß der CREATE LANGUAGE Befehl in der Standardinstallation für PL/pgSQL nicht nötig ist, war es das doch. Wahrscheinlich waren die Debian Maintainer da mal wieder vorsichtiger als die Upstream-Autoren.
• Den Namen der Sequence ("test_id_seq") habe ich mit pgAdmin3 aus der Datenbank geholt. Er wird automatisch durch SERIAL erzeugt und man muss ihn ansonsten raten.
• Ab PostgreSQL 9.x gibt es für den "CREATE TRIGGER" Befehl eine "WHEN Condition". Damit kann man den Fall, daß die übergebene ID NULL ist schon abfangen, bevor die Funktion überhaupt aufgerufen wird. Obige Lösung per IF-Befehl innerhalb der Funktion habe ich mit der Version 8.4 gemacht.
• Ob man den Trigger wirklich auch für UPDATE-Befehle braucht oder nicht, hängt von der Anwendung ab. Mir ist kein sinnvoller Grund bekannt, die ID eines bestehenden Datensatzes zu ändern, also könnte man das "OR UPDATE" auch weglassen.

-- ThomasBayen

Umsetzung für HSQLDB #

Auch HSQLDB erlaubt die Verwendung von Triggern, allerdings habe ich das bisher (noch) nicht umgesetzt.

Übertragung in Hibernate #

Grundlegendes zu eigenen ID-Generatoren in Hibernate #

Wünschenswert wäre, wenn man in Hibernate eine eigene Generator-Klasse hätte, so daß man den hier vorgestellten ID-Generator genauso auswählen kann, wie die anderen, in Hibernate vorgegebenen, Varianten. Hierzu habe ich zuerstm einmal Informationen gesammelt. In der Doku zu Annotationen steht grundsätzlich, wie man in Hibernate den ID-Generator auswählt. Dazu gibt es in Kapitel "2.2.3 Mapping Identifier Properties" die Beschreibung der Generatoren, die der JPA-Standard unterstützt. Darüber hinaus kann man in Hibernate aber auch noch weitergehende Generatoren benutzen, die in "2.4.2.1 Generators" beschrieben werden. Dort ist erklärt, daß man in GenericGenerator entweder eine der Hibernate "generator strategies" angeben kann oder einen FQCN einer Generatorklasse, die IdentifierGenerator implementiert. Solche Generatorklassen können auch die Enhanced Identifier Generators sein, die erst in Zukunft in Hibernate zum Einsatz kommen sollen.

Hier fängt es nun an, interessant zu werden: Man kann sich die vorhandenen Implementationen dieses Interfaces ansehen und eine eigene, neue Implementation erzeugen, die direkt macht, was wir wollen. Diese ist dann in einer ganz normalen Hibernate-Konfiguration über Ihren FQCN zu benutzen wie die Standard-Generatoren auch. Die Frage an dieser Stelle ist, welche der vorhandenen Generator-Klassen man am besten als Basis benutzt.

Überlegungen und Fehlüberlegungen #

Meine obigen Überlegungen bei der Auswahl der Implementation meiner Lösung gingen ja erst mal davon aus, daß die Lösung komplett in der Datenbank stecken soll. Damit fallen einige der Implementationen wie Hi/Lo oder UUID aus. Letztlich habe ich dann (in PostgreSQL) eine Sequence benutzt. Also würde sich anbieten, den SequenceGenerator als Basis zu nehmen. Der hat allerdings das Problem, daß er auf Plattformen, die keine Sequences kennen, gar nicht geht. :-( Eine Lösung könnte sein, eine ganz eigene Klasse zu schreiben, die dann automatisch zwischen verschiedenen Lösungen auswählt. So was ähnliches gibt es bereits: Die Klasse SequenceStyleGenerator ist zwar (zur Zeit) noch nicht offiziell eine der Haupt-Generatorstrategien, aber sie ist in Hibernate bereits enthalten: Sie benutzt eine Sequence und falls es eine solche nicht gibt, eine besondere Tabelle mit einem Eintrag. Experimente ergaben jedoch, daß diese Klasse zwar eine Sequence benutzt, dies aber "von Hand": Es wird also kein "SERIAL" benutzt, sondern mit einem SELECT der nächste Sequence-Wert geholt, bevor ein neues Objekt geschrieben wird. Genau das wollte ich ja nun nicht (Alles soll so in der Datenbank gemacht werden, daß es unmöglich wird, etwas falsch zu machen...).

Ein anderer Weg ist die "identity" Strategie. Ein Blick auf den Quellcode zeigte mir, daß diese Strategie unter MySQL "AUTO_INCREMENT" benutzt und unter PostgreSQL "SERIAL". Damit ist das ja schon fast perfekt. Da muss ich nur noch meinen Trigger einbauen. Dazu muss ich die Klasse IdentityGenerator ableiten und das Interface PersistentIdentifierGenerator implementieren lassen. Dort kann ich dann Code angeben, der den zusätzlichen Trigger installiert. Nun warf der Blick in den Quellcode allerdings ein anderes Problem auf: Beim Erzeugen der DDL (also der Tabellendefinition für CREATE) wird auf einmal nicht mehr SERIAL verwendet. Und das kommt so: Die Erzeugung der DDL geschieht in Configuration.generateSchemaCreationScript(), wo dann Table.sqlCreateString() aufgerufen wird. Das wiederum prüft dann anhand eines Objektes "idValue" in SimpleValue.isIdentityColumn(), ob es sich um einen IdentityGenerator handelt. Davon abhängig wird dann in Table.sqlCreateString() der richtige SQL-Code erzeugt (also abhängig von der Datenbank z.B. "SERIAL" oder "AUTO_INCREMENT"). Die besagte Abfrage prüft nun aber die Frage, ob es sich um einen IdentityGenerator handelt, indem die Klasse auf Gleichheit geprüft wird. Das bedeutet, daß nur genau diese eine Klasse den richtigen SQL-Code hervorrufen kann.

Im Moment ist mir nicht ganz klar, wo ich da jetzt am besten manipulierend eingreifen kann, weswegen ich dieses Problem hier erstmal so stehen lasse.

Für den Moment scheint die beste Lösung zu sein, einen IDENTITY-Generator zu benutzen und dann im Nachhinein den Trigger "von Hand" (bzw. programmatisch per direktem SQL-Befehl) zu installieren. Vielleicht werde ich das Problem mal den Hibernate-Entwicklern schildern. Mit winzigen Anpassungen in Hibernate könnte man das eleganter lösen.

-- ThomasBayen

---