Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Hier dreht es sich um den PostGre Server

Moderator: Moderatoren

Antworten
Benutzeravatar
dtmackenzie
Rekursionen-Architekt
Rekursionen-Architekt
Beiträge: 265
Registriert: Do, 22. Nov 2007 9:02
Wohnort: Leipzig
Hat sich bedankt: 66 Mal
Danksagung erhalten: 22 Mal
Kontaktdaten:

Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von dtmackenzie »

Tja, folgendes Problem erscheint mir leider praktisch unlösbar, aber vielleicht hat jemand eine Idee.

Ich möchte Foreign Keys im Postgres Schema definieren um die Relationen zwischen den Tabellen explizit zu machen auf der Datenbank-Ebene. Zum Beispiel, ich habe eine Tabelle Artikel mit Feld SN (Sachnummer), was eigentlich eindeutig sein sollte, und möchte dies von verschiedenen anderen Tabellen (mit einem Foreign Key) referenzieren. Das setzt einen eindeutigen Schlüssel voraus, also ein UNIQUE Constraint auf der Artikel-Tabelle.

Soweit so gut, aber gelöschte Datensätze machen einen Strich durch die Rechnung. Es ist ja möglich, einen Artikel zu löschen und neu anzulegen. Gut, dachte ich, dann beziehe ich auch __deleted ein, so wie

Code: Alles auswählen

ALTER TABLE artikel ADD CONSTRAINT unique_artikel_sn
UNIQUE (__deleted, sn);
Leider geht das auch nicht, weil ein Artikel mehrmals gelöscht und angelegt werden kann, also die gleiche SN kann mehrmals vorkommen mit __deleted = TRUE. Also stehe ich nun in einer Sackgasse.

Bei jedem Löschen ein PACK zu machen wäre theoretisch denkbar, aber in der Praxis???

Hat jemand eine bessere Idee?
Gibt es vielleicht irgendwo eine Einstellung damit DELETE den Datensatz sofort und endgültig entfernt, anstatt ihn nur als gelöscht zu markieren?
RECALL brauche ich nicht.
Viele Grüße,
David
Benutzeravatar
Tom
Der Entwickler von "Deep Thought"
Der Entwickler von "Deep Thought"
Beiträge: 9345
Registriert: Do, 22. Sep 2005 23:11
Wohnort: Berlin
Hat sich bedankt: 100 Mal
Danksagung erhalten: 359 Mal
Kontaktdaten:

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von Tom »

Gibt es vielleicht irgendwo eine Einstellung damit DELETE den Datensatz sofort und endgültig entfernt, anstatt ihn nur als gelöscht zu markieren?
Das müsste Alaska in der DBE realisieren, aber es ginge ja nicht nur darum, das SQL-DELETE auszulösen, das könntest Du ja auch selbst. <grübel>
Herzlich,
Tom
Benutzeravatar
dtmackenzie
Rekursionen-Architekt
Rekursionen-Architekt
Beiträge: 265
Registriert: Do, 22. Nov 2007 9:02
Wohnort: Leipzig
Hat sich bedankt: 66 Mal
Danksagung erhalten: 22 Mal
Kontaktdaten:

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von dtmackenzie »

Hallo Tom,

ja, da hast Du Recht. Es würden z.B Lücken entstehen in der RECNO() bzw. __record Reihenfolge...

Historisch gesehen ist es klar, warum es die Vorgehensweise mit "markieren als gelöscht" seit 40 Jahren oder mehr gibt - mit einer DBF-Datei geht es viel schneller als alles wie beim PACK zusammenzuschieben, und zudem konnte RECALL als Feature verkauft werden. Aber auch mit PGDBE wäre es für Alaska vermutlich etwas schwierig, eine Option zur sofortigen Löschen anzubieten. Die "RECNO-Lücken" gingen auch nicht einfach so zu "packen", da andere Clients die RECNOs legitim gemerkt haben könnten. Ob man aber vielleicht mit den Lücken leben könnte, zumindest mit SET DELETED ON?
Viele Grüße,
David
Benutzeravatar
dtmackenzie
Rekursionen-Architekt
Rekursionen-Architekt
Beiträge: 265
Registriert: Do, 22. Nov 2007 9:02
Wohnort: Leipzig
Hat sich bedankt: 66 Mal
Danksagung erhalten: 22 Mal
Kontaktdaten:

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von dtmackenzie »

Ich habe nun eine ausführliche Antwort zu diesem Thema von Alaska bekommen, vielen Dank an Steffen F. Pirsig!
Die Emails (meine Anfrage und die Antwort) füge ich unten hinzu, weiterhin gibt es einen neuen und sehr interessanten Artikel in ILX dazu:
https://ilx.alaska-software.com/index.p ... nd-sql.82/

Obwohl ich gehofft habe, dass Foreign Keys irgendwie einsetzbar sein könnten, finde ich es durchaus verständlich, dass dies nicht möglich ist - da es nun klar ist, dass ein "hartes Löschen" nicht praktikabel ist, liegt es in der Natur der Sache, da man bei der Definition des Foreign Keys die gelöschte Datensätze nicht ausklammern kann, weder direkt in der DDL (z.B. ALTER TABLE) noch durch Views (Foreign Keys kann man nur für "richtige" Tabellen definieren), noch durch Bezug auf einem Unique Index (s. unten). Falls ich mich irre, wäre ich sehr erfreut wenn jemand mir zeigen könnte, dass ich falsch liege... :D

Trotzdem sind eine Menge nützliche Tipps und viel Hintergrundwissen dabei. Zum Beispiel, es war mir vorher nicht eingefallen, dass man zumindest ein Unique Index mit "WHERE NOT __deleted" benutzen kann um ungewollte doppelte Schlüssel gleich bei der Entstehung abzufangen: Meiner Meinung nach, besser eine Exception als eine schleichende Datenkorruption! Das werde ich demnächst ausprobieren...

Ich möchte mich also nochmal ganz herzlich bedanken für die Antwort und den Artikel, und natürlich für die darin investierte Zeit!

Hier meine Email und die Antwort:
Hallo Support,

es wäre schon nett, wenn es möglich wäre, Foreign Keys mit PGDBE-Tabellen zu benutzen. Wir (SINUS Messtechnik GmbH) wollen perspektivisch zusätzlich zum ISAM-Zugriff über PGDBE auch reine SQL-Anwendungen mit unserer Datenbank benutzen. Dafür wären Foreign Keys wichtig aus verschiedenen Gründen: Datenintegrität, Relationen, Beschreibung, Performance... Sie gehören einfach dazu. Leider sind die Schlüsselfelder durch das in der dBase-Familie typische Verhalten "Datensätze beim Löschen nur markieren" nicht eindeutig, selbst wenn __deleted zum Schlüssel hinzugefügt wird, weil es mehrere gelöschte Datensätze mit dem gleichen Schlüssel geben kann. Ich hatte im Xbase ++ Forum eine kurze Diskussion mit Tom Liehr über diese Sache und die mögliche Konsequenzen:
https://www.xbaseforum.de/viewtopic.php ... 52ca5a1261

Also zum Punkt: Wäre es für Alaska prinzipiell möglich, eine Einstellung zu schaffen damit DELETE den Datensatz sofort und endgültig aus der Postgres-Datenbank entfernt, anstatt ihn nur als gelöscht zu markieren? Die mögliche Konsequenzen kann ich aber ohne Insiderwissen nicht vollständig einschätzen.

Viele Grüße,
David Mackenzie
Sehr geehrter Herrn Mackenzie,

nachdem wir mal die Vorstellungen der anderen Xbase++ User im Forum betrachtet haben, haben wir uns entschlossen einen Artikel zu schreiben um den Sachverhalt "Löschen von Datensätzen in einer ISAM emulierenden SQL Tabelle" zu beleuchten.

Kurz und einfach gesagt ist es so, wenn Sie in einer ISAM emulierenden Tabelle Sätze löschen verletzten Sie die ISAM Semantic und infolge wird Ihr Xbase++ Code an mehreren Stellen gebrochen sein. Nur wenn der Xbase++ Entwickler auf eine nicht unwesentliche Menge an Garantien des ISAM Modelles verzichtet wäre es möglich Datensätze wirklich zu löschen.

Hier der Link mit zwei Aspekten wie so etwas anders herum gelöst werden kann:
https://ilx.alaska-software.com/index.p ... nd-sql.82/

Der Kernpunkt hier ist, das Sie die ISAM Emulation als ISAM Emulation bestehen lassen müssen, aber im Gegenzug auf der SQL Zugriffsseite einiges anders machen sollten damit eine Interoperabilität gewährleistet ist. Zum Beispiel ist ein DELETE ein Update mit __deleted = TRUE.

Abschließend noch der Hinweis, wenn Sie mittels referenzieller Integrität oder anderen Contraints in der Datenbank arbeiten, erzwingen Sie die Regeln auch auf der Xbase++ code seite. Sollte hier ein Contraint verletzt werden gibt es wie immer bei SQL eine Exception in der Xbase++ Anwendung.

Gerne dürfen Sie diese Ausführungen, sowie den Link ins ILX Portal im Forum veröffentlichen.

Dies mit besten Grüßen und ein schönes Wochenende
Steffen F. Pirsig
Viele Grüße,
David
Benutzeravatar
Lewi
1000 working lines a day
1000 working lines a day
Beiträge: 830
Registriert: Di, 07. Feb 2006 14:10
Wohnort: Hamburg
Danksagung erhalten: 2 Mal

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von Lewi »

Hallo David,
Kannst Du ein Beispiel zweier Tabellen posten, die über "foreign key" verbunden sind? Ich habe die Vermutung, dass er Einsatz von "foreign keys" nicht stimmig ist.

Richtig ist, das "foreign key" zunächst unique sein müssen. Ein "foreign key" kann nicht manuel vergeben werden! Die Info`s über die letzte Nr. darüber befinden sich in den Systemtabellen der DB.

Bei Löschvorgängen wird ein "foreign key" gelöscht, hat aber zur Folge, dass eine nächste Nr. für einen "foreign key" vom DB-System selbst hochgezählt und automatisch beim Anlegen neuer DS erzeugt wird.

Viele Grüße
Olaf
Benutzeravatar
dtmackenzie
Rekursionen-Architekt
Rekursionen-Architekt
Beiträge: 265
Registriert: Do, 22. Nov 2007 9:02
Wohnort: Leipzig
Hat sich bedankt: 66 Mal
Danksagung erhalten: 22 Mal
Kontaktdaten:

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von dtmackenzie »

Hallo Olaf,

Danke für Deine Antwort aber ich befürchte, dass Du hier etwas verwechselt hast.
"Foreign Key" ist ein rein SQL Begriff, es gibt Beispiele zu Hauf in der Postgres Dokumentation.
Hat nichts mit ISAM oder Xbase++ zu tun, und auch nicht mit "Autoincrement": Ein Foreign Key kann doch manuell vergeben werden - ich glaube, Du denkst an die IDENTITY Option für Primary Keys.
Meistens referenziert ein Foreign Key ein Primary Key, aber nicht immer - essentiell ist, dass die referenzierte Spalten in der Fremdtabelle unique sein müssen.
Das ist aber genau das Problem mit gelöschten Spalten in einer ISAM Tabelle - selbst wenn man darauf achtet, dass es einen unique Schlüssel bei nicht gelöschten Datensätzen gibt, kann es beim mehrmaligen löschen und neu anlegen dazu kommen, dass es mehrere völlig identische gelöschte Datensätze gibt. Da die gelöschte Datensätze aus SQL Sicht völlig normale Datensätze sind (mit __deleted = TRUE), gibt es keinen unique Schlüssel über die gesamte Tabelle. Das Beste was man tun kann ist, wie von Alaska vorgeschlagen, einen Index mit WHERE NOT __deleted anzulegen, aber auf dieser Basis kann man keinen Foreign Key definieren.
Viele Grüße,
David
Benutzeravatar
nightcrawler
1000 working lines a day
1000 working lines a day
Beiträge: 650
Registriert: Di, 24. Apr 2012 16:33
Wohnort: 72184 Weitingen
Hat sich bedankt: 3 Mal
Danksagung erhalten: 96 Mal
Kontaktdaten:

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von nightcrawler »

Hallo David,
wenn man sich an den Grundsatz hält, dass ein Primärschlüssel niemals funktional sein darf (also immer künstlich erzeugt und nicht Kundennummer o.ä.), dann sollte dies keine Probleme machen. Nimm eine GUID oder ähnliches, dann ist die Eindeutigkeit mit sehr hoher Wahrscheinlichkeit gegeben.
--
Joachim
Joachim Dürr Softwareengineering
https://www.jd-engineering.de
Benutzeravatar
dtmackenzie
Rekursionen-Architekt
Rekursionen-Architekt
Beiträge: 265
Registriert: Do, 22. Nov 2007 9:02
Wohnort: Leipzig
Hat sich bedankt: 66 Mal
Danksagung erhalten: 22 Mal
Kontaktdaten:

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von dtmackenzie »

Hallo Joachim,

Danke, das könnte/sollte man auf alle Fälle bei einem neuen Datenbankschema berücksichtigen.
In ISAM Tabellen haben wir eigentlich erzwungenermaßen schon sowas Ähnliches mit __record - aber nur solange kein PACK gemacht wird!
Also besser nicht darauf verlassen, sowas wie GUIDs oder IDENTITY für Primary Keys wäre doch besser.
Vielleicht hat Olaf sowas Ähnliches gemeint?

Ja, ich gebe Dir Recht, dieser datentechnische Gedanke ist grundsätzlich nicht verkehrt, aber leider manchmal impraktikabel für eine existierende auf Postgres migrierte Anwendung bzw. Datenbank: In meinem Fall ein firmeninternes ERP-System mit ca. 50 Tabellen, das über einem Zeitraum von 30 Jahren (ursprünglich in Clipper von einem anderen Programmierer) gewachsen ist. Meiner Meinung nach grenzt es an ein Wunder, dass so ein System sich überhaupt so migrieren lässt, alle Achtung Alaska!
Viele Grüße,
David
Benutzeravatar
Lewi
1000 working lines a day
1000 working lines a day
Beiträge: 830
Registriert: Di, 07. Feb 2006 14:10
Wohnort: Hamburg
Danksagung erhalten: 2 Mal

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von Lewi »

Hallo David,
ein "foreign key" kann in in einer nicht-ISAM SQL-Datenbank gar nicht manuell vergeben werden. Das dies bei einer ISAM-Verwaltung (funktional) möglich ist, ist mir neu. Kein Wunder, wenn dann Probleme beim Löschen und Neu-Anlagen auftauchen .... ;-)
Benutzeravatar
dtmackenzie
Rekursionen-Architekt
Rekursionen-Architekt
Beiträge: 265
Registriert: Do, 22. Nov 2007 9:02
Wohnort: Leipzig
Hat sich bedankt: 66 Mal
Danksagung erhalten: 22 Mal
Kontaktdaten:

Re: Wie kann ich in Postgres FOREIGN KEY mit PGDBE benutzen? Gelöschte Datensätze sind problematisch...

Beitrag von dtmackenzie »

Hallo Olaf,

wir reden hier leider völlig aneinander vorbei.
Natürlich darf ein Foreign Key manuell vergeben werden, ich habe wirklich keine Ahnung, was Du damit meinst.
Es muss ja nur übereinstimmen mit genau einem Datensatz in der Fremdtabelle, das ist das Wesen des Foreign Keys.
Meinst Du vielleicht "frei vergeben"?
Dann natürlich nicht.
Bei ISAM selbst fehlt das Konzept komplett, man kann dort nur über Indexes (genaue oder partielle Übereinstimmung) suchen.
Viele Grüße,
David
Antworten