Tabellenpartitionierung mittels Trigger

Hallo,
ich habe Probleme mit einer BigSerial-Spalte in zusammenhang mit partitionierten Tabellen und Triggern:

Ich habe einen Trigger geschrieben der die Daten, die in eine Parent-Tabelle eingefügt werden sollen, abfängt und stattdessen in eine Child-Tabelle schreibt.

CREATE TRIGGER T_PRODUCT_TRIGGER BEFORE INSERT ON "T_PRODUCT" FOR EACH ROW EXECUTE PROCEDURE trigger_insert_into_T_PRODUCT();

Das Problem ist, dass ich den Trigger nicht ‘INSTEAD OF’ ausführen lassen kann. -->; Der Datensatz wird nach dem Trigger-Insert in die Child-Tabelle nochmals in die Parent-Tabelle geschrieben.
Kann ich das irgendwie abfangen/unterbinden bzw. den Trigger irgendwie zu einem ‘INSTEAD OF’ machen?

Ich habe es schon mit Rules versucht… das scheiterte auch ziemlich schnell, da man dort nicht mit Variablen hantieren kann.

Hier meine Funktionen:

CREATE OR REPLACE FUNCTION _MaxPartitionSize() RETURNS bigint AS $$
BEGIN
RETURN 100000;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION trigger_insert_into_T_PRODUCT() RETURNS TRIGGER AS '
DECLARE
TablePostfixInt bigint;
TableName text;
IndexName text;
BEGIN
TablePostfixInt := CURRVAL(pg_get_serial_sequence(''"T_PRODUCT"'',''ProductSerialId'')) / _MaxPartitionSize();
TableName := ''T_PRODUCT_'' || TablePostfixInt;
IndexName := TableName || ''_INDEX'';

IF EXISTS (SELECT relname FROM pg_class WHERE relname = TableName) THEN 
EXECUTE ''INSERT INTO "'' || TableName || ''" ("ProductArticleId", "ProductId", "ProductSerialnumber", "ProductCode", "ProductText", "TimeBegin", "TimeEnd") VALUES ('' || New."ProductArticleId" || '','' || New."ProductId" || '','''''' || New."ProductSerialnumber" || '''''','''''' || New."ProductCode" || '''''','''''' || New."ProductText" || '''''','''''' || New."TimeBegin" || '''''','''''' || New."TimeEnd" || '''''')'';
ELSE
EXECUTE ''CREATE TABLE "'' || TableName || ''" () INHERITS ("T_PRODUCT") '';
EXECUTE ''ALTER TABLE "'' || TableName || ''" OWNER TO postgres'';
EXECUTE ''ALTER TABLE "'' || TableName || ''" ADD CONSTRAINT "'' || TableName || ''_Key7" PRIMARY KEY ("ProductId","ProductArticleId")'';
EXECUTE ''ALTER TABLE "'' || TableName || ''" ADD CONSTRAINT "'' || TableName || ''_Was" FOREIGN KEY ("ProductArticleId") REFERENCES "T_ARTICLE" ("ArticleId") ON DELETE NO ACTION ON UPDATE NO ACTION'';
EXECUTE ''CREATE INDEX "'' || IndexName || ''" ON  "'' || TableName || ''" ("ProductId","ProductArticleId","TimeBegin","TimeEnd","ProductSerialId") '';
EXECUTE ''INSERT INTO "'' || TableName || ''" ("ProductArticleId", "ProductId", "ProductSerialnumber", "ProductCode", "ProductText", "TimeBegin", "TimeEnd") VALUES ('' || New."ProductArticleId" || '','' || New."ProductId" || '','''''' || New."ProductSerialnumber" || '''''','''''' || New."ProductCode" || '''''','''''' || New."ProductText" || '''''','''''' || New."TimeBegin" || '''''','''''' || New."TimeEnd" || '''''')'';
END IF;
RETURN NULL;
END; ' LANGUAGE plpgsql;
ALTER FUNCTION trigger_insert_into_T_PRODUCT() OWNER TO postgres;

ich wäre für jede Hilfe sehr dankbar!

Nachtrag:
Die Lösung mittels ‘Rule’ funktioniert auch nicht, weil man dort kein IF verwenden kann (und das ‘IF EXISTS’ brauche ich ja um herauszufinden ob die Tabelle existiert oder nicht…)

Welche Version ist am Start, aktuelle Versionen unterstützen Dollar-Quoting.

Prinzipiell: jedes INSERT dann dynamisch zu generieren ist nicht grad performant. Besser wäre, Du legst die Partitionen an, so wie hier: PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Partitioning

Was willst Du überhaupt erreichen mit den Partitionen? Für eine Optimierung bei Suchanfragen dürfte der Nutzen Deiner Partitionen gegen NULL gehen. Auch der Index, den Du da erzeugst, scheint mir nicht grad sinnvoll.

Ich arbeite mit der aktuellen Version.

Ich habe die Funktion auch schon auf Dollar-Qouting umgestellt, aber da habe ich das gleiche Problem:

CREATE OR REPLACE FUNCTION trigger_insert_into_T_PRODUCT() RETURNS TRIGGER AS $trigger_insert_into_T_PRODUCT$
DECLARE
    TablePostfixInt bigint;
    TableName text;
    IndexName text;
BEGIN
    RAISE NOTICE 'Zähler = %', CURRVAL(pg_get_serial_sequence('"T_PRODUCT"','ProductSerialId')) ;    
    
    TablePostfixInt := CURRVAL(pg_get_serial_sequence('"T_PRODUCT"','ProductSerialId')) / _MaxPartitionSize();
    TableName := 'T_PRODUCT_' || TablePostfixInt;
    IndexName := TableName || '_INDEX';
    
    RAISE NOTICE 'Zähler = %', CURRVAL(pg_get_serial_sequence('"T_PRODUCT"','ProductSerialId')) ;    
    
    IF EXISTS (SELECT relname FROM pg_class WHERE relname = TableName) THEN 
            EXECUTE 'INSERT INTO  "' || TableName || '" ("ProductArticleId", "ProductId", "ProductSerialnumber", "ProductCode", "ProductText", "TimeBegin", "TimeEnd") VALUES ( '|| New."ProductArticleId" ||' , '|| New."ProductId" ||' ,''' || New."ProductSerialnumber" || ''',''' || New."ProductCode" || ''',''' || New."ProductText" || ''',''' || New."TimeBegin" || ''',''' || New."TimeEnd" || ''')';
    ELSE
            EXECUTE 'CREATE TABLE "' || TableName || '" () INHERITS ("T_PRODUCT")';
            EXECUTE 'ALTER TABLE  "' || TableName || '" OWNER TO postgres';
            EXECUTE 'ALTER TABLE  "' || TableName || '" ADD CONSTRAINT "' || TableName || '_Key7" PRIMARY KEY ("ProductId","ProductArticleId")';
            EXECUTE 'ALTER TABLE  "' || TableName || '" ADD CONSTRAINT "' || TableName || '_Was" FOREIGN KEY ("ProductArticleId") REFERENCES "T_ARTICLE" ("ArticleId") ON DELETE NO ACTION ON UPDATE NO ACTION';
            EXECUTE 'CREATE INDEX "' || IndexName || '" ON "' || TableName || '" ("ProductId","ProductArticleId","TimeBegin","TimeEnd","ProductSerialId")';    
            --EXECUTE 'INSERT INTO  "' || TableName || '" ("ProductArticleId", "ProductId", "ProductSerialnumber", "ProductCode", "ProductText", "TimeBegin", "TimeEnd") VALUES ( || New."ProductArticleId" || , || New."ProductId" || ,'' || New."ProductSerialnumber" || '','' || New."ProductCode" || '','' || New."ProductText" || '','' || New."TimeBegin" || '','' || New."TimeEnd" || '')';
            EXECUTE 'INSERT INTO  "' || TableName || '" ("ProductArticleId", "ProductId", "ProductSerialnumber", "ProductCode", "ProductText", "TimeBegin", "TimeEnd") VALUES ( '|| New."ProductArticleId" ||' , '|| New."ProductId" ||' ,''' || New."ProductSerialnumber" || ''',''' || New."ProductCode" || ''',''' || New."ProductText" || ''',''' || New."TimeBegin" || ''',''' || New."TimeEnd" || ''')';
    END IF;

    RAISE NOTICE 'Zähler = %', CURRVAL(pg_get_serial_sequence('"T_PRODUCT"','ProductSerialId')) ;    
    
    RETURN 1;
END;
$trigger_insert_into_T_PRODUCT$ LANGUAGE plpgsql;
ALTER FUNCTION trigger_insert_into_T_PRODUCT() OWNER TO postgres;

Ich möchte erreichen, dass Abfragen (und Inserts) schneller gehen. Also es soll die Datenbank eigentlich nur performanter machen.
Desweiteren sollte das ding ‘selbstwartend’ sein. Das soll heißen, dass ich nicht alle x Monate bzw. alle 100k Datensätze oder so neue Partitionen erstellen müsste… sondern dass das die Datenbank selbstständig erledigt.
Dazu ist noch zu sagen, dass wir uns dafür entschieden haben die Partitionen nicht abhängig vom aktuellen Zeitstempel des Insers zu machen, sondern abhängig von der Anzahl der Datensätze, die in einer Tabelle sind (sodass jede Tabelle gleich veile Datensätze in sich trägt).
Kannst du auch bitte noch was dazu sagen, warum die Indexerstellung deiner Meinung nach ‘nicht grad sinnvoll’ ist?

Im Endeffekt brauche ich eine Lösungsmöglichkeit, wie ich einige der Tabellen in unserer Datenbank performant halten kann - trotz sehr großer Datenmengen. Ist nicht genau dafür die Partitionierung gemacht wurden?

Gruß Torsten

Also, zuerst einmal: Wenn Du die Elterntabelle abfragst, zeigt diese Dir den Inhalt der Kindtabellen an. Deine Funktion funktioniert also :wink:

So, Inserts: Du mußt für jede Zeile das Statement dynamisch erstellen. Da wird für jede Zeile dann auch neu ein Plan berechnet. Das kostet, und zwar Zeit. Deine Lösung wird Inserts ganz sicher nicht beschleunigen.

Index: zeig mir Deine Abfragen, bzw. die Pläne dazu. Hilft der Index? Dein Index ist relativ komplex, über viele Spalten. Die Wartung ist daher ‘teuer’ (beim Insert). Hast Du geprüft, ob bei Selects der Index genutzt wird? EXPLAIN ist Dein Freund.

Partitionierung: das wirkt IMHO nur für Abfragen beschleunigend, wenn Du constraint exclusion nutzen kannst. Bei Deinem Partitionierungsschema bezweifle ich, das dies möglich ist. Die Wahl einer Kindtabelle hat, das vermute ich, keinen Bezug zu den WHERE-Bedingungen Deiner Selects. Daher werden (vermutlich) bei einem Select immer alle Kindtabellen durchsucht, ob Index-Scan oder Seq-Scan sei dahingestellt.

Fazit: Du bremst Inserts UND Selects. Aber hey, wie schon gesagt: daß vermute ich nur, ich kenne ja Deine Abfragen nicht…

Andreas

Versuch es mal mit einem RETURN NULL.

Ähm, ja, hatte ich vergessen zu schreiben …


Andreas

Danke, ich habe es dann mit folgender Funktion+Tigger hinbekommen:

CREATE OR REPLACE FUNCTION trigger_insert_into_T_PRODUCT() RETURNS TRIGGER AS $$
DECLARE
    currentcount bigint;
    TablePostfixInt bigint;
    TableName text;
    IndexName text;
BEGIN
    currentcount := CURRVAL(pg_get_serial_sequence('"T_PRODUCT"','ProductSerialId'));
    TablePostfixInt := currentcount / _MaxPartitionSize();
    TableName := 'T_PRODUCT_' || TablePostfixInt;
    IndexName := TableName || '_INDEX';
    --RAISE NOTICE 'Zähler: % --> %', CURRVAL(pg_get_serial_sequence('"T_PRODUCT"','ProductSerialId'), CURRVAL(pg_get_serial_sequence('"T_PRODUCT"','ProductSerialId') + 1);    
    IF EXISTS (SELECT relname FROM pg_class WHERE relname = TableName) THEN 
        EXECUTE    'INSERT INTO "'|| TableName || '" ("ProductSerialId","ProductArticleId", "ProductId", "ProductSerialnumber","ProductCode", "ProductText", "TimeBegin", "TimeEnd") VALUES ('|| currentcount || ','|| New."ProductArticleId" ||' , '|| New."ProductId" ||' ,''' || New."ProductSerialnumber" || ''',''' || New."ProductCode" || ''',''' || New."ProductText" || ''',''' || New."TimeBegin" || ''',''' || New."TimeEnd" || ''')';
    ELSE
        EXECUTE 'CREATE TABLE "' || TableName || '" () INHERITS ("T_PRODUCT")';
        EXECUTE 'ALTER TABLE  "' || TableName || '" OWNER TO postgres';
        EXECUTE 'ALTER TABLE  "' || TableName || '" ADD CONSTRAINT "' || TableName || '_Key7" PRIMARY KEY ("ProductId","ProductArticleId")';
        EXECUTE 'ALTER TABLE  "' || TableName || '" ADD CONSTRAINT "' || TableName || '_Was" FOREIGN KEY ("ProductArticleId") REFERENCES "T_ARTICLE" ("ArticleId") ON DELETE NO ACTION ON UPDATE NO ACTION';
        EXECUTE 'CREATE INDEX "' || IndexName || '" ON "' || TableName || '" ("ProductId","ProductArticleId","TimeBegin","TimeEnd","ProductSerialId")';    
        EXECUTE    'INSERT INTO "'|| TableName || '" ("ProductSerialId","ProductArticleId", "ProductId", "ProductSerialnumber","ProductCode", "ProductText", "TimeBegin", "TimeEnd") VALUES ('|| currentcount || ','|| New."ProductArticleId" ||' , '|| New."ProductId" ||' ,''' || New."ProductSerialnumber" || ''',''' || New."ProductCode" || ''',''' || New."ProductText" || ''',''' || New."TimeBegin" || ''',''' || New."TimeEnd" || ''')';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
ALTER FUNCTION trigger_insert_into_T_PRODUCT() OWNER TO postgres; 
CREATE TRIGGER insert_T_PRODUCT_trigger BEFORE INSERT ON "T_PRODUCT" FOR EACH ROW EXECUTE PROCEDURE trigger_insert_into_T_PRODUCT();

Aber es gibt immernoch das selbe Problem wie damals… man kann keine Foreign Keys auf Eltern-Tabellen legen, weil die Foreign Keys nicht die Kinder durchsuchen:
Es Kommt zu Fehlermeldung, dass der Datensatz x nicht in Tabelle y vorhanden ist.

Wir werden jetzt versuchen:
-entweder die Foreign keys mit ‘CHECK-Constraints’+‘Not Null’ zu ersetzen
-oder die Foreign keys komplett weglassen und versuchen deren Funktionalität über ausformulierte Abfragen / Inserts nachzubilden

Ich denke mal mit ‘CHECK-Constraints’+‘Not Null’ würden wir den besseren/‘Kostensparenderen’ Weg gehen, das müssen wir aber noch prüfen.

Gruß und nochmal vielen Dank für die Tipps!!!

Du willst eine partitionierte Tabelle als Nachschlagetabelle nutzen?


Andreas