Postgres: Exclusion Constraints gegen Zeitüberlappungen

[MartinSt]

Hallo,

seit Version 8.5 unterstützt Postgres sogeannte Exclusion Constraints, die man z.B. schön nutzen kann um Zeitüberlappungen zu verhindern, z.B. dass ein Hotelzimmer keine zwei überlappenden Buchungen haben darf.

In verschiedenen Artikeln im Web gibt es für solche Überlappungsconstraints zwei Ansätze: einmal über die Erweiterung der Postgres-Standardtypen um den Typ Period und zweitens indem man die Überlappung auf die Überlappung der geometrischen Objekte zurückführt, die Postgres unterstützt.

Den 1. Weg würde ich wegen des zusätzlichen Packages ungern gehen; der 2. Weg funktioniert, bringt allerdings recht komplizierte Constraint-Bedingungen.

Ich würde gerne (wenn möglich) das Ganze über die normalen Datumsvergleiche abhandeln.

Kann mir jemand einen Verweis auf eine entsprechende Quelle geben oder kennt evtl. eine geschickte Lösung?

Danke. Martin

[flashpixx]

Also bei nem Datum würde ich das über nen Trigger handhaben und wenn es überlappt im Trigge ne race condition erzeugen.

Sprich bei on-update oder on-instert prüft du im Trigger ob ein Datensatz innerhalb der Periode vorhanden ist, wenn ja, race condition werfen.

[dr.dimitri]

Die PG Techniken sind mir nicht bekannt, aber die Triggerlösung ist definitiv nicht multiuserfähig, da unterschiedliche Sessions die uncomitteten Einträge der anderen nicht sehen können und es daher früher oder später zu Überbuchungen kommt.

Ich würde hier wie folgt vorgehen:

Angeommen, es gibt eine Tabelle ZIMMER, wird bei jedem Buchungsversuch der entsprechende Eintrag in dieser Tabelle mittels SELECT ... FOR UPDATE gelockt. Jetzt hat man dieses Zimmer im exclusiven Zugriff und kann seine Buchungsdaten speichern.

Eine andere Session würde jetzt erstmal warten, bis der neue Eintrag comittet ist, den Datensatz wieder sperren, evtl. feststellen, dass der vorher noch als frei angezeigte Zeitraum mittlerweile belegt ist und mit einer entsprechenden Meldung abbrechen.

Das ganze wird natürlich optimistisch gelockt, sprich der Lock auf das Zimmer erfolgt erst, wenn über die Anwendung die Daten schon fix und fertig eingegeben sind und der Anwender auf Speichern klickt.

Man könnte es auch noch eleganter machen, und in der Tabelle ZIMMER einen Änderungszähler hinterlegen, der jedesmal hochgezählt wird, wenn eine Buchung erfolgt. Ist der Änderungszähler, der während des erstmaligen Ladens des Zimmers für die Anzeige in der AW gelesen wurde, identisch mit dem Zähler der beim Locken in der Tabelle steht, muss ich keine vorherige Prüfung laufen lassen, ob der Zeitraum noch frei ist und spare mir damit etwas Zeit und CPU.

Und im übrigen: Keine Anwendungslogik in Triggern hinterlegen :mod:

Dim

[flashpixx]

Die PG Techniken sind mir nicht bekannt, aber die Triggerlösung ist definitiv nicht multiuserfähig, da unterschiedliche Sessions die uncomitteten Einträge der anderen nicht sehen können und es daher früher oder später zu Überbuchungen kommt.

Das ist eine Sache der Transaktionssicherung, wenn die komplette Buchung als eine Transaktion geführt wird, ist die Anwendung eines Triggers korrekt, denn es bezieht sich nur auf vollständig gebuchte Daten, wobei der neu einzufügende Datensatz auf die korrekte Konsistenz geprüft wird. Durch das Race wird die aktuelle Transaktion ungültig und somit wird die komplette neue Buchung als fehlerhaft gekennzeichnet.

Und im übrigen: Keine Anwendungslogik in Triggern hinterlegen :mod:

Wo ist der Beleg für Deine Aussage? Nur weil Du das vielleicht nicht konkret einsetzt, muss dies nicht als generelleres Prinzip umgesetzt werden

Zitat aus "Datenbanksysteme" von Alfons Kemper & Andre Eickler, Seite 165 / Kap. 5.7

Der allgem. Konsistenzmechanismus ist der sogenannte Trigger. [..] Ein Trigger ist eine benutzerdefinierte Prozedur, die automatisch bei Erfüllung einer bestimmten Bedingung vom DBMS gestartet wird. Sie kann nicht nur zur Überprüfungs-, sonder auch Berechnungsfunktionen übernehmen. [..] Durch folgenden Trigger soll beispielsweise verhindert werden, dass Professoren einen Rang degradiert werden können

Letzteres wäre hier ein analoger Fall, wobei in der genannten Literatur dieses Beispiel sogar in Oracle angegeben ist.

[dr.dimitri]

Das ist eine Sache der Transaktionssicherung, wenn die komplette Buchung als eine Transaktion geführt wird, ist die Anwendung eines Triggers korrekt, denn es bezieht sich nur auf vollständig gebuchte Daten, wobei der neu einzufügende Datensatz auf die korrekte Konsistenz geprüft wird. Durch das Race wird die aktuelle Transaktion ungültig und somit wird die komplette neue Buchung als fehlerhaft gekennzeichnet.

Sofern Du immer nur eine Transaktion zu einer Zeit ausführst ja - aber Datenbanken wurden nicht gemacht um die Zugriffe auf eine Ressource künstlich zu serialisieren. Sofern wir von einem Multiusersystem sprechen, müssen wir also immer davon ausgehen, dass mehrere Transaktionen gleichzeitig versuchen auf das Zimmer einen sich überschneidenden Zeitrum zu buchen.

Da Transaktionsänderungen für andere Sessions frühestens dann sichtbar werden, wenn die ändernde Transaktion sie comittet hat (Dirty Reads lassen wir mal außen vor, sofern vom DBMS überhaupt unterstützt, denn wer die verwenden muss hat eh schon verloren) können wir in einem Trigger nie sicher sein, ob nicht ein Stand gelesen wird, der gerade verändert wird aber noch nicht comittet ist.

Wo ist der Beleg für Deine Aussage?

Transaktion Tx1 startet zum Zeitpunkt T1 und prüft, ob eine Buchung für den Zeitraum Z vorhanden ist. Da dies nicht der Fall ist, schreibt Tx1 zum Zeitpunkt T1+x die Änderung fest.

Zum Zeitpunkt T2 startet die Transaktion Tx2 innerhalb einer anderen Session, wobei gilt T1 > T2 < T1+x.

Zum Zeitpunkt T2+y für den gilt T2+y < T1+x prüft Tx2 ebenfalls, ob eine Buchung vorhanden ist. Da Tx1 noch nicht comittet hat, sieht Tx2 diesen Eintrag nicht und comittet ebenfalls zu einem Zeitpunkt T2+y+z.

Damit haben wir eine klassische Doppelbuchung.

Zitat aus "Datenbanksysteme" von Alfons Kemper & Andre Eickler, Seite 165 / Kap. 5.7

Ja, dass hatten wir schon mal. Aber wer einen Trigger als allgemeinen Konsistenzmechanismus beschreibt, sollte sich doch nochmal eingehend mit der Thematik befassen. Es gibt in einer DB genau 2 (in Worten zwei) Mechanismen, die verwendet werden können um die technische und fachliche Konsistenz sicherzustellen. Das eine sind Constraints, das andere sind Locks. Es ist nicht möglich mit Triggern einen wie auch immer gearteten Constraint nachzubauen (ok, einen Check Constraint schon) ohne den Zugriff auf die Ressource (== Tabelle) zu serialisieren.

Trigger sollten nur dann benutzt werden, wenn es nicht anders geht. Sie sind langsam, agieren ausserhalb der normalen Programmlogik und wer nicht weiß wie man sie richtig verwendet (dazu zähle ich auch die von Dir zitierten Herren), der läuft Gefahr seine Daten fachlich und ggf. auch technisch inkonsistent werden zu lassen.

Dim

[flashpixx]

Ja, dass hatten wir schon mal. Aber wer einen Trigger als allgemeinen Konsistenzmechanismus beschreibt, sollte sich doch nochmal eingehend mit der Thematik befassen.

Ich denke die Reputation TUM Informatik III: Prof. A. Kemper ist durchaus aussagekräftig.

[dr.dimitri]

Ich denke die Reputation TUM Informatik III: Prof. A. Kemper ist durchaus aussagekräftig.

Reputationen sind mir relativ egal. Was ich aus seinem Lebenslauf herauslese, war er nie, vielleicht mal von einem Praktikum abgesehen, längere Zeit in der Verantwortung für ein echtes, produktives System. Lehramtler in Passau (keine 60km von mir) zu unterrichten, dürfte dieses Gap kaum geschlossen haben.

Ich habe die letzten 10 Jahre Erfahrungen als Azubi, Entwickler sowie seit 2 Jahren als technisch Verantwortlicher für ein System, das ca. 10 Tsd User täglich zuverlässig und performant mit Daten zu versorgen hat. Das schließt Datenlieferungen vom Mainframe ebenso ein wie Rücklieferungen aus der dezentralen Welt.

Im dezentralen Bereich laufen Programme von mir, die die lokale Oracle Datenbanken innerhalb einer Agentur (je nach dem, bei welcher Versicherung Du bist hast Du evtl. auch schon mal die Anwendung gesehen, die auf diese Datenbank zugreift) quasi in Echtzeit miteinander abgleichen und konsistent halten bzw. auch ohne Probleme damit zurecht kommen, wenn ein Agent sein NB aus der Docking Station nimmt und sich später per UMST wieder einwählt.

Die Oracle Replikation war uns zu starr, darum hab ich da was flexibleres geschrieben

Konstrukte, wie Du sie hier vorschlägt würden keine 10 Minuten in der Entwicklungsdatenbank überleben bevor ich mir den Kollegen greife - sofern er überhaupt auf die Idee kommt sowas zu machen.

Die jenigen Autoren, von denen ich mir Wissen erarbeitet habe sind weniger durch eine Aufzählung erworbene Titel als vielmehr durch jahrelange Erfahrung zu den anerkannten Know How Trägern im Datenbankbereich (insb. Oracle) geworden.

Dazu gehören Tom Kyte (vgl. Signatur), Jonathan Lewis, Wolfgang Breitling, Carry Millsap uvm., die Du über diese Links erreichen kannst.

Ob Kemper hier einfach nur Müll schreibt, oder Du ihn falsch auslegst vermag ich nicht 100%ig zu sagen - Dein Zitat spricht jedenfalls im Bereich Trigger und Konsistenz nicht dafür, dass er tiefgreifende Erfahrung damit hat, große Systeme zu designen, die pro Tag 10 Mio und mehr Änderungen verkraften müssen ohne das ein falsch geschriebener Trigger innerhalb von Tagen oder Wochen die Daten in ihre Einzelteile zerlegt.

Dim

[MartinSt]

Danke für die Ideen und Vorschläge.

Wen es interessiert, hier der Link zur Lösung mit geometr. Koordinaten, die mir inzwischen ganz gut gefällt, da man ja solche Zeiträume gut als Intervalle/Strecken auf der Zeitachse verstehen kann.

select * from depesz; Blog Archive Waiting for 8.5 – exclusion constraints