Wie Foreign key definieren, aber nicht prüfen/enforcen?

Liebe Postgresler,

für unseren Datamart setzen wir Postgresql ein und sind damit auch sehr zufrieden. Allerdings bleibt ein Wermutstropfen: Viele ETL-Tools und auch unsere Datenbankmodellierungstool nutzt Foreign Key Informationen, um diverse Aufgaben zu erleichtern oder überhaupt erst zu ermöglichen. Aus Performancegründen und wie im Datamart üblich, geschieht die Konsitenzprüfung der Daten im Vorfeld im ETL Tool. Das Enforcing von Schlüsseln beim Load in den Datamart führt zu erheblichen Performanceeinbußen und ist überdies unnötig. Meine Frage ist deshalb:

Gibt es in Postgres die Möglichkeit, Foreign Keys zwar zu definieren, diese aber nicht zu enforcen?

Viele Grüße,

Bergtroll

Nein, dann wäre es ja MySQL :wink:

Du kannst die Prüfung verzögern, daß diese erst am Ende der TX erfolgt:

test=*# create table b1 (id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "b1_pkey" for table "b1"
CREATE TABLE
test=*# create table b2 (i int references b1 deferrable initially deferred);
CREATE TABLE
test=*# insert into b2 values (2);
INSERT 0 1
test=*# commit;
ERROR:  insert or update on table "b2" violates foreign key constraint "b2_i_fkey"
DETAIL:  Key (i)=(2) is not present in table "b1".

Aber abschalten geht nicht.

Andreas

Oder Oracle :-P. Vielen Dank auf jeden Fall für die, für mich wenngleich auch unglückliche, Antwort.

Viele Grüße,

Bergtroll

Naja, die Bemerkung mit MySQL kommt daher, daß MySQL Check-Constraints syntaktisch prüft. Mehr nicht.

Was genau ist denn für Dich so schlecht am Verhalten von PG? Daß es in solchen Dingen gründlich ist finde ich gerade einen wesentlichen Vorzug von PG.

Andreas

Greenplum (als PostgreSQL fork für Data Warehouse) enforced auf genau jenem Grund keine Foreign Keys.

Wie jetzt, ich kann da gegen FK-Constraints verstoßen?

Andreas

Ja, das ist ein typisches Vorgehen in einem DWH Umfeld.
Bei Oracle kann man das auch machen. Man legt einen FK und sagt Oracle: “vertrau mir, das passt alles. Du brauchst das nicht prüfen”.

Bei einem Bulk load von mehreren 100 Millionen Datensätze kann das die Ladezeit deutlich verringern. Der Vorteil gegenüber einem System ohne FKs ist, dass der Optimizer weiterhin die Existenz des Constraint für Optimierungen verwenden kann (ich glaube später eingefügte Datensätze werden trotzdem validiert, nur die exisiterenden eben nicht).

Meines Wissens bieten “alle” großen Datenbanken sowas - zumindest die, die im DWH Umfeld präsent sind (Teradata, SQL Server auf jeden Fall. Bei DB2 bin ich mir nicht sicher, aber ich glaube da geht das auch).

Okay, das war/ist mir neu.

Andreas

Genau wie castorp sagt: im Data Warehouse Umfeld sind deine Daten in der Regel maschinell generiert.
Dich interessiert maximal die Beziehung der Daten untereinander.

In einem OLTP-Umfeld, wo du sehr häufig mit Menschen interagierst, sieht das komplett anders aus.

hi,

geht doch (seit 9.1):
PostgreSQL: Documentation: 9.1: ALTER TABLE
hier die (initiale) Diskussion auf hackers:
PostgreSQL - hackers - ALTER TABLE … ADD FOREIGN KEY … NOT ENFORCED | Page 3

hier die commits aus dem git-repo:
git.postgresql.org Git - postgresql.git/search

vg hkocam

Oha ;.-)


Andreas