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...)
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.
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.Code:CREATE TRIGGER T_PRODUCT_TRIGGER BEFORE INSERT ON "T_PRODUCT" FOR EACH ROW EXECUTE PROCEDURE trigger_insert_into_T_PRODUCT();
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:
Code:CREATE OR REPLACE FUNCTION _MaxPartitionSize() RETURNS bigint AS $$ BEGIN RETURN 100000; END; $$ LANGUAGE plpgsql;Code: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.
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Ich arbeite mit der aktuellen Version.
Ich habe die Funktion auch schon auf Dollar-Qouting umgestellt, aber da habe ich das gleiche Problem:
Ich möchte erreichen, dass Abfragen (und Inserts) schneller gehen. Also es soll die Datenbank eigentlich nur performanter machen.Code: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;
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 ;-)
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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Versuch es mal mit einem RETURN NULL.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Danke, ich habe es dann mit folgender Funktion+Tigger hinbekommen:
Code: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!!!
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen