Ausschlussbeschränkung

  

Außerdem darf die Tabelle "Bar" keine Überschneidung enthalten "Timespan"   Werte für den gleichen "FooID" oder "ParentID" . Ich habe einen Trigger erstellt   das nach jedem INSERT , UPDATE oder DELETE ausgelöst wird, das verhindert   überlappende Bereiche.

Ich schlage vor, dass Sie stattdessen eine Ausschlussbeschränkung verwenden, die viel einfacher, sicherer und schneller ist:

Sie müssen das zusätzliche Modul installieren btree_gist zuerst. Siehe Anweisungen und Erklärungen in dieser Antwort:

• Speichern Sie den Wochentag und die Uhrzeit?

Und Sie müssen "ParentID" in die Tabelle "Bar" redundant einfügen, was ein kleiner Preis ist. Tabellendefinitionen könnten wie folgt aussehen:

Ich habe auch den Datentyp für "Bar"."FooID" von int8 in int4 geändert. Er verweist auf "Foo"."FooID" , was ein serial ist, d.h. int4 . Verwenden Sie den passenden Typ int4 (oder nur integer ) aus verschiedenen Gründen, z. B. Performance.

Sie benötigen keinen Trigger mehr (zumindest nicht für diese Aufgabe), und Sie erstellen den Index "Bar_FooID_Timerange_idx" nicht mehr, da er implizit durch die Ausschlussbedingung erstellt wird .

Ein btree-Index auf ("ParentID", "FooID") wird jedoch höchstwahrscheinlich nützlich sein:

Verwandte:

• Verhindern angrenzender / überlappender Einträge mit EXCLUDE in PostgreSQL

Ich habe UNIQUE ("ParentID", "FooID") gewählt und nicht aus einem anderen Grund, da es in beiden Tabellen einen anderen Index mit dem führenden "FooID" gibt:

• Ist ein zusammengesetzter Index auch für Abfragen im ersten Feld geeignet?

Beiseite: Ich verwende niemals doppelt zitierte CaMeL-Case-Bezeichner < in Postgres. Ich mache es nur hier, um Ihrem Layout zu entsprechen.

Vermeiden Sie die redundante Spalte

Wenn Sie "Bar"."ParentID" nicht redundant hinzufügen können oder wollen, gibt es einen anderen Rogue Weg - unter der Bedingung, dass "Foo"."ParentID" nie aktualisiert ist. Stellen Sie das sicher, zum Beispiel mit einem Trigger.

Sie können eine Funktion IMMUTABLE fälschen:

Ich habe das Schema für den Tabellennamen qualifiziert, um sicherzustellen, dass public vorausgesetzt wird. Passen Sie sich Ihrem Schema an.

Mehr:

• CONSTRAINT um Werte aus einer entfernten Tabelle zu überprüfen (über Join usw.)
• Unterstützt PostgreSQL "akzentunempfindliche" Sortierungen?

Dann verwenden Sie es in der Ausschlussbedingung:

Beim Speichern einer redundanten Spalte int4 wird die Einschränkung teurer und die gesamte Lösung hängt von mehr Vorbedingungen ab.

Behandle Konflikte

Sie könnten INSERT und UPDATE in eine plpgsql-Funktion einschließen und mögliche Ausnahmen von der Ausschlussbeschränkung ( 23P01 exclusion_violation ) auffangen, um sie irgendwie zu handhaben.

Komplettes Codebeispiel:

• Behandlung von EXCEPTION und Rückgabe des Ergebnisses aus der Funktion

Behandeln Konflikt in Postgres 9.5

In Postgres 9.5 können Sie INSERT direkt mit der neuen "UPSERT" -Implementierung verarbeiten. Die Dokumentation:

  

Die optionale ON CONFLICT -Klausel gibt eine alternative Aktion an   einen eindeutigen Verletzung- oder Ausschlussbedingungsverletzungsfehler auslösen.   Für jede einzelne Zeile zum Einfügen vorgeschlagen, entweder die Einfügung   oder, wenn ein Arbiter Constraint oder Index von angegeben wird    conflict_target ist verletzt, die Alternative conflict_action ist   genommen. ON CONFLICT DO NOTHING vermeidet einfach das Einfügen einer Zeile als deren   alternative Aktion. ON CONFLICT DO UPDATE aktualisiert die vorhandene Zeile   das steht im Widerspruch zu der Zeile, die als alternative Aktion eingefügt werden soll.

Allerdings:

  

Beachten Sie, dass Ausschlussbedingungen in ON CONFLICT DO UPDATE nicht unterstützt werden.

Sie können jedoch weiterhin ON CONFLICT DO NOTHING verwenden, wodurch mögliche exclusion_violation -Ausnahmen vermieden werden.Prüfen Sie, ob Zeilen tatsächlich aktualisiert wurden, was günstiger ist:

In diesem Beispiel wird die Überprüfung auf die angegebene Ausschlussbedingung beschränkt. (Ich habe die Einschränkung explizit für diesen Zweck in der obigen Tabellendefinition benannt.) Andere mögliche Ausnahmen werden nicht abgefangen.