Referenzierte Tabellen (mit Foreign Keys) partitionieren

Hallo,

ich habe mich vor geraumer Zeit mit Tabellenpartition beschäftigt.
Dabei bin ich auch gut Voran gekommen. Ich habe es hinbekommen,
dass Daten vor dem einfügen in die “Muttertabelle” über einen Trigger abgefangen werden.
Name der “Muttertabelle” ist t_value.
Der abgefangene, neue, Datensatz wird dann anstatt in die Muttertabelle in eine INHERITS-Tabelle der Muttertabelle geschrieben. Jeden Monat wird eine neue INHERITS-Tabelle angelegt.
Namen der INHERITS-Tabellen sind z.B.:
t_value_200906, t_value_200907, t_value_200908, t_value_200909, t_value_200910, t_value_200911, t_value_200912, t_value_201001, … usw.

Nun möchte ich “alte Datenbanken” (ohne partitionierte Tabellen) updaten.
Gedacht hab ich mir das so:
-INHERITS-Tabellen der vergangenen Zeit erstellen

-INSERT INTO t_value_[JAHR][MONAT] FROM t_value WHERE Time <= … AND Time > … verwenden um die Daten in die partitionierten INHERITS-Tabellen zu bekommen

-TRUNCATE TABLE t_value

jetz gehts ans eingemachte…

ERROR: cannot truncate a table referenced in a foreign key constraint
SQL Status:0A000
Detail:Table “t_key” references “t_value”.
Hinweis:Truncate table “t_key” at the same time, or use TRUNCATE … CASCADE.


Jetzt meine Frage:
Kann ich irgendwie die ‘referenced foreign keys’ temporär lösen,
die Tabelle t_value 'truncate’n
und dann die ‘referenced foreign keys’ wieder auf die t_value legen?


Kennt jemand eine bessere/einfacherere/sauberere Lösungsmöglichkeit ?
Oder komme ich nur zum Ziel durch komplett neu Aufsetzen der DBs ?

PS:
hier mal das Script, wie die (wichtigen) Tabellen damals erstellt wurden:

CREATE SEQUENCE s_value_id_seq;                                        
CREATE TABLE t_value        ( 
                Id            INTEGER DEFAULT nextval( 's_value_id_seq' )
                            NOT NULL PRIMARY KEY                ,  
                Key            DOUBLE PRECISION                ,  
                Time            TIMESTAMP NOT NULL                ,  
                BatchId        INTEGER REFERENCES t_hardware( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        , 
                CharacteristicId    INTEGER REFERENCES t_characteristic( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        ,  
                Value            DOUBLE PRECISION                ,  
                UNIQUE        ( Id )
                            );
                
CREATE INDEX t_value_batchid ON t_value (BatchId);
CREATE INDEX t_value_characteristicid ON t_value (CharacteristicId);
CREATE INDEX t_value_key ON t_value (Key);
CREATE INDEX t_value_time ON t_value (Time);


CREATE SEQUENCE s_key_id_seq;                                          
CREATE TABLE t_key        ( Id            INTEGER DEFAULT nextval( 's_key_id_seq' )
                            NOT NULL PRIMARY KEY                ,  
                  Key            DOUBLE PRECISION                , 
                  Time            TIMESTAMP NOT NULL                ,  
                  FirstId        INTEGER REFERENCES t_value( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        , 
                  LastId        INTEGER REFERENCES t_value( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        ,  
                  UNIQUE        ( Id )
                );

CREATE INDEX t_key_key ON t_key (Key);
CREATE INDEX t_key_time ON t_key (Time);

CREATE SEQUENCE s_log_id_seq;                                        
CREATE TABLE t_log        ( Id            INTEGER DEFAULT nextval( 's_log_id_seq' )
                            NOT NULL PRIMARY KEY                ,  
                  Time            TIMESTAMP NOT NULL                ,  
                  Type            INTEGER                        ,  
                  RefId            INTEGER                        , 
                  Number        TEXT                        ,  
                  Name             TEXT                            , 
                  Description        TEXT                        ,  
                  Picture        OID                        ,  
                  UNIQUE        ( Id )
                );
                
CREATE INDEX t_log_time ON t_log (Time);
CREATE INDEX t_log_type ON t_log (Type);


CREATE SEQUENCE s_piece_id_seq;                                         
CREATE TABLE t_piece        ( Id            INTEGER DEFAULT nextval( 's_piece_id_seq' )
                            NOT NULL PRIMARY KEY                ,  
                  TimeBeg        TIMESTAMP NOT NULL                ,  
                  TimeEnd        TIMESTAMP NOT NULL                , 
                  PartId        INTEGER REFERENCES t_hardware( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        ,  
                  BatchId        INTEGER REFERENCES t_hardware( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        ,  
                  Key            DOUBLE PRECISION                ,  
                  SerialNumber        DOUBLE PRECISION                ,    
                  Data1            TEXT                        ,  
                  Data2            TEXT                        ,  
                  Data3            TEXT                        ,  
                  Data4            TEXT                        ,  
                  Data5            TEXT                        ,  
                  Data6            TEXT                        ,  
                  Data7            TEXT                        ,  
                  Data8            TEXT                        , 
                  Data9            TEXT                        ,  
                  Data10        TEXT                        , 
                  FirstId        INTEGER REFERENCES t_value( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        , 
                  LastId        INTEGER REFERENCES t_value( Id )
                            ON DELETE CASCADE ON UPDATE CASCADE        ,  
                  CntWriteAccess    INTEGER                        , 
                  UNIQUE        ( Id )
                );
            
CREATE INDEX t_piece_timebeg ON t_piece (TimeBeg);
CREATE INDEX t_piece_timeend ON t_piece (TimeEnd);
CREATE INDEX t_piece_partid ON t_piece (PartId);
CREATE INDEX t_piece_batchid ON t_piece (BatchId);
CREATE INDEX t_piece_key ON t_piece (Key);
CREATE INDEX t_piece_serialnumber ON t_piece (SerialNumber);

“TRUNCATE TABLE t_value CASCADE” würde auch alle Daten aus ‘t_piece’ und ‘t_key’ löschen. Das möchte ich aber verhinden!

Ich würde mich uber Antwort sehr freuen!

Würde beim erstellen der Tabellen t_piece und t_key das hier: “ON DELETE NO ACTION ON UPDATE CASCADE” schon reichen???

MfG Torsten

Ok, ich habe es jetz mal so versucht:

-INHERITS-Tabellen der vergangenen Zeit erstellen

-INSERT INTO t_value_[JAHR][MONAT] FROM t_value WHERE Time <= … AND Time > … verwenden um die Daten in die partitionierten INHERITS-Tabellen zu bekommen

-ALTER TABLE ONLY t_piece DROP CONSTRAINT t_piece_firstid_fkey;
-ALTER TABLE ONLY t_piece DROP CONSTRAINT t_piece_lastid_fkey;
-ALTER TABLE ONLY t_key DROP CONSTRAINT t_key_firstid_fkey;
-ALTER TABLE ONLY t_key DROP CONSTRAINT t_key_lastid_fkey;

-truncate table t_value;


Jetzt ist die t_value erstmal leer und die Daten in t_key und t_piece sind erhalten geblieben.
Die Daten von t_value sind nun auf die INHERIT-Tabellen aufgeteilt.

jetzt möchte ich die constraints wieder erstellen:

-ALTER TABLE ONLY t_key ADD CONSTRAINT t_key_firstid_fkey FOREIGN KEY (id) REFERENCES t_value (id) ON DELETE CASCADE ON UPDATE CASCADE;

ERROR: insert or update on table “t_key” violates foreign key constraint “t_key_firstid_fkey”
SQL Status:23503
Detail:Key (id)=(376) is not present in table “t_value”.

Wenn ich jedoch
select * from t_value where id=376
ausführe, bekomme ich den richtigen Datensatz bei Datenanzeige (von der einen INHERIT-Tabelle von t_value).

wie kann ich dem constraint erklären, dass es sich um eine partitionierte tabelle handelt und es den Datensatz doch gibt ???
…need help…

Mfg Torsten

Ok, hab endlich gefunden wonch ich suchen hätte müssen, mit diesem Ergebnis:

Ich habe jetzt die Ursache des Problemes zumindestens erst mal isolieren können:
Da die Datensätze in die vererbten Tabellen eingefügt wurden und nicht
in die vererbende Tabelle object, funktioniert der Foreignkey
Constraint nicht. > :frowning:

Er funktioniert nur bei Direkt in object eingefügte Werte. Ich werde
also stattdessen mit einem Trigger arbeiten müssen.

Zwar schade das das nicht geht, aber mit einem Trigger wäre ich auch zufrieden, wenn er denn das erhoffte Ergebnis bringt.
Könnte mir jemand einen Tipp geben, wie ich aufbauen kann/muss das er so wirkt als wären die REFERENCES FOREIGN KEY verwendet worden?

Gibts ein PostgreSQL-Update oder einen Eintrag in der .conf - Datei der mit weiterhilft?

Hallo,

ich bins nochmal…
Ich würde mich freuen, falls jemand, der sich damit auskennt, mir etwas ähnliches sagen könnte:

“versuchs erst gar nicht, es geht nicht!”
oder
“es gibt da (irgendwie) eine möglichkeit, wir habens auch geschafft!”

MfG Torsten

Dies würde ich vermuten… aber vielleicht geht es ja doch?

Okay, ich denke, dies geht nicht. Der FK muß ja auf eine table-oid verweisen, und zwar auf die, die auch die referenzierten Daten beinhaltet.


Andreas

Part(ition)ing Is Such Sweet Sorrow - David Fetter’s blog

Andreas

Danke !!!