Temporale Tabellen

Hallo


ich benoetige in einer Datenbank fuer eine Teilmenge der Tabellen folgende Funktionalitaet:

  • es sollen aeltere Staende eines Eintrags mit Gueltigkeitszeitpunkt gespeichert werden und geloeschte sollen als invalid gekennzeichnet, aber nicht geloescht werden
  • es soll jederzeit moeglich sein, zu einem aelteren Stand unter Angabe des gewuenschten Zeitpunkts zurueckzukehren
  • es soll moeglich sein, von Zeit zu Zeit “aufzuraeumen”, d.h. Eintraege aelter als n Stunden zu loeschen
  • es gibt bereits definierte FrontEnds (Viewer) fuer die Datenbank, die nicht angefasst werden sollen


    Was ich mir ueberlegt habe:

zunaechst einmal will ich den entsprechenden Tabellen zwei weitere Spalten hinzufuegen, eine, die den Gueltigkeitsbeginn (“t”) einer Zeile enthaelt, eine, die ein “valid”-Flag als boolean enthaelt, der primary key wuerde sich dann zusammensetzen aus dem alten primary key (“id”) und dem “t”.

dann muessten die Funktionen zur Bearbeitung von Eintraegen ueberschrieben werden:
ein INSERT muesste wie ein normales INSERT funktionieren, aber zusaetzlich automatisch den aktuellen Zeitpunkt in “t” eintragen und “valid” auf true setzen
ein UPDATE muesste die entsprechende Zeile/Zeilen unveraendert lassen und stattdessen fuer jede Zeile eine Kopie anlegen, die den nun aktuellen Zeitpunkt und den neuen Zustand der Zeile enthaelt
ein DELETE sollte eine neue Zeile anlegen als Kopie der bislang gueltigen, aber mit dem valid-Flag auf false

ein SELECT weiterhin sollte fuer jede “id” nur den Inhalt der neuesten Zeile liefern und Zeilen, bei denen valid false ist, nicht zurueckliefern

ein VIEW sollte immer die aktuellen Zeilen anzeigen, dh. wenn eine Zeile durch ein UPDATE sich aendert, sollte nicht mehr die alte nun nicht mehr gueltige Zeile im VIEW stehen, sondern die neue (bzw. nach DELETE sollte sie aus dem VIEW verschwinden)

dann sollte es eben eine Funktion “jump_to_time(t)” geben, die für alle Einträge eine neue Zeile mit der aktuellen Zeit und dem Inhalt, der zum Zeitpunkt t gültig war, anlegt (bzw. sofern dieser Eintrag dort noch nicht existierte, eine Zeile mit aktueller Zeit und valid false anlegt)

dann müsste es eine Funktion “kill_before(t)” geben, die bei allen Einträge jeweils den Gültigkeitszeitpunkt der Zeile, die zum Zeitpunkt t gültig war, auf t setzt und alle Zeilen dieses Eintrages, die älter sind, löscht


Nun meine Fragen:

  • ist diese Problemstellung neu oder gibt es bereits Funktionensammlungen zu diesem Thema für postgresql?
  • ist mein Konzept in Ordnung oder müsste man das Thema anders angehen?
  • habe ich etwas übersehen, was ebenfalls für diesen Ansatz implementiert werden müsste? Wie sieht es zB. in Bezug auf Trigger oder foreign-key Handling aus? Würde ein FOREIGN KEY CASCADE ON UPDATE / DELETE automatisch funktionieren?


    Viele Fragen, ich hoffe Ihr könnt mir helfen
    Markus

Ja, es gibt tablelog von ads. Google kennt den Link.


Okay, ich auch: Log Table Changes in PostgreSQL with tablelog - ads’ corner


Andreas

docbot kannte den Link g

sehr gut!

Da hier offenbar sogar der Autor persönlich schreibt (“ads”), gleich noch zwei Fragen in Bezug auf die beiden Punkte, in denen das noch nicht ganz tut, was ich mir vorstelle, vielleicht gibt es dazu ja geeignete Lösungen?

  • ich möchte nicht in eine restore-Table restoren, sondern in die Ursprungstabelle. Die Idee, dafür erstmal eine temp-Tabelle anzulegen, dann die Ursprungstabelle zu droppen und die temp-Tabelle in den Namen der Ursprungstabelle zu ändern, funktioniert nicht, weil es Foreign-Key Beziehungen gibt (mit ON DELETE CASCADE), die dann kaputtgingen. Gibt es irgendeinen Weg, in die Ursprungstabelle zu restoren?

  • im Moment eines Restore soll nicht eine einzelne Tabelle restort werden, sondern alle temporal-enabled-Tabellen. Da sollte es doch möglich sein, ein Skript laufen zu lassen, welches das Restore-Kommando auf allen _log-Tabellen laufen lässt? Hat da schon jemand was geeignetes?

Also, du musst nicht unbedingt die vorhandenen Restore-Funktionen nutzen. Mit einem geeigneten Select kriegst du die passenden Informationen auch so aus der Log-Tabelle und kannst das als Grundlage für einen Insert nutzen.

Zu deinem zweiten Wunsch: uh, nein, kenne ich nichts passendes. Aber eine Funktion, die den Rest in einer Schleife aufruft, ist eigentlich schnell geschrieben, oder?

die zweite Sache ist wohl in der Tat schnell selbst geschrieben in einer Schleife

bei der ersten Aufgabe bin ich mir jedoch nicht ganz sicher, ob ich da richtig denke, mag mal jemand über diesen Pseudo-Code schauen? Ist der vollständig, oder könnte es irgendwo Probleme geben?

, die Tabelle, in der die Daten stehen, in die hinein auch das Restore laufen muss
, die von tablelog verwaltete Datei

a) erstmal müsste ich raussuchen, welche ids restored werden können:
SELECT DISTINCT FROM ;

b) für jedes Ergebnis muss ich nun zunächst den zum gesuchten Zeitpunkt t gültigen Wert raussuchen aus der :

SELECT * FROM WHERE trigger_changed < t AND id==<id, die ich in diesem Durchlauf betrachte> SORT BY trigger_id DESC LIMIT 1;

c) dann die Unterscheidung:
wenn null Einträge gefunden werden, existierte diese Zeile zu dem Zeitpunkt noch nicht, also muss ich ein DELETE IF EXISTS in der für diese id ausführen
wenn ein Eintrag gefunden wird und trigger_tuple=old ist, dann wurde diese Zeile zu dem Zeitpunkt gerade vorher gelöscht, also ebenso ein DELETE IF EXISTS
wenn ein Eintrag gefunden wird und trigger_tuple=new ist, dann muss entweder ein UPDATE oder ein INSERT in der Datentabelle ausgeführt werden, je nachdem, ob ein Eintrag mit dieser id noch existiert oder zwischenzeitlich gelöscht wurde…

Geht das so?




noch eine andere Frage: angenommen, ich möchte alle Einträge einer Datentabelle in die log-tabelle mit dem aktuellen Datum setzen, wie könnte man das machen?

Hallo Otternase,

ich habe das Problem intertemporaler Datensätze auch schon seit Monaten auf dem Schirm, bin mit den bestehenden Data Warehouse Konzepten (Slowly Changing Dimensions) auch noch nicht glücklich geworden und bastle daher an einer eigenen Lösung. Bist Du inzwischen fündig geworden oder hast Du etwas eigenes entworfen? Ich fände es schön, hier zu diesem sehr komplexen Thema in einen Erfahrungsaustausch treten zu können.