Problèmes liés au clé étrangères avec le partitionnement¶
Glossaire¶
| Nom | Définition |
|---|---|
| Table référençante | Table qui porte la contrainte de clé étrangère. |
| Table référencée | Table référencée par la clé étrangère. |
FK cassé après avoir détaché la partition référençante¶
https://www.postgresql.org/message-id/flat/20230420144344.40744130%40karst
Contexte: les deux tables concernées par la contrainte sont partitionnées.
Hypothèse¶
Détacher une partition coté référençante conserve et ajuste la clé étrangère de la table détachée vers la table référencée.
Observation¶
Une fois la partition détachée, supprimer ou modifier une ligne dans la table référencée peu aboutir à une violation de la contrainte. Des lignes coté table référençante pouvent alors pointer sur des lignes inexistantes dans la table partitionnée.
Reproduction¶
DROP TABLE IF EXISTS p, f, f_1 CASCADE;
-- parent
CREATE TABLE p (
id BIGINT PRIMARY KEY
)
PARTITION BY list (id);
CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
INSERT INTO p VALUES (1);
CREATE TABLE f (
id BIGINT PRIMARY KEY,
p_id BIGINT NOT NULL,
FOREIGN KEY (p_id) REFERENCES p (id)
)
PARTITION BY list (id);
CREATE TABLE f_1 PARTITION OF f FOR VALUES IN (1);
INSERT INTO f VALUES (1,1);
-- scenario
ALTER TABLE f DETACH PARTITION f_1;
/*
* BUG: this succeed where it acutally must fail as "f_1" still have a row
* referencing "p".
*/
DELETE FROM p;
/*
* OK: this fails as EXPECTED as "(2,2)" doesn't exists in "p".
*/
INSERT INTO f_1 VALUES (2,2);
Contournement¶
Supprimer la contrainte manuellement sur la table détachée, la recréer manuellement si nécessaire.
ALTER TABLE f DETACH PARTITION f_1;
ALTER TABLE f_1 DROP CONSTRAINT f_p_id_fkey;
ALTER TABLE f_1 ADD FOREIGN KEY (p_id) REFERENCES p (id); -- if useful
La commande DETACH échoue quand la partition a une FK vers une table partitionnée¶
https://www.postgresql.org/message-id/flat/20230705233028.2f554f73%40karst
Contexte: les deux tables concernées par la contrainte sont partitionnées
Hypothèse¶
- préparer une table avec la clé étrangère pré-existante ;
- l’attacher comme partition ;
- la détacher plus tard fonctionne.
Observation¶
Détacher la partition lève une erreur à cause d’un trigger manquant.
=> ALTER TABLE f DETACH PARTITION f_1; -- BREAK
ERROR: could NOT find ON INSERT CHECK triggers OF FOREIGN KEY CONSTRAINT 17287
Reproduction¶
DROP TABLE IF EXISTS p, f, f_1 CASCADE;
CREATE TABLE p ( id BIGINT PRIMARY KEY )
PARTITION BY list (id);
CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE p_2 PARTITION OF p FOR VALUES IN (2);
CREATE TABLE f (
id BIGINT PRIMARY KEY,
p_id BIGINT NOT NULL,
FOREIGN KEY (p_id) REFERENCES p (id)
)
PARTITION BY list (id);
CREATE TABLE f_1 ( LIKE f INCLUDING ALL );
ALTER TABLE f_1 ADD FOREIGN KEY (p_id) REFERENCES p (id);
ALTER TABLE f ATTACH PARTITION f_1 FOR VALUES IN ('1');
ALTER TABLE f DETACH PARTITION f_1; -- BREAK
Contournement¶
Supprimer la FK avant d’attacher la table.
Si la table est déjà attachée:
- trouver le nom de la FK avec
\d; - ouvrir une transaction par mesure de sécurité ;
Attention
La requête suivante retourne autant de lignes que de partitions dans la table référencée par la FK.
- supprimer les dépendances des anciennes contraintes gênantes ;
DELETE FROM pg_depend d USING pg_constraint pc -- partition contraint JOIN pg_constraint oc ON oc.conparentid = pc.oid -- old constraint JOIN pg_constraint dc ON dc.conparentid = oc.oid -- to delete WHERE pc.conname = 'f_p_id_fkey' -- the name of the top FK AND d.classid = 'pg_constraint'::regclass AND d.refclassid = d.classid AND objid = dc.oid AND refobjid = oc.oid RETURNING d.*;
Attention
Les lignes retournées de la requête suivante ne doivent faire référence qu’à la table à détacher dans conrelid et
exclusivement à des partitions dans la table référencée par la FK pour la colonne confrelid.
-
supprimer les contraintes gênantes ;
DELETE FROM pg_constraint c USING pg_constraint pc -- partition contraint JOIN pg_constraint oc ON oc.conparentid = pc.oid -- old constraint JOIN pg_constraint dc ON dc.conparentid = oc.oid -- to delete WHERE pc.conname = 'f_p_id_fkey' -- the name of the top FK AND c.oid = dc.oid RETURNING c.conrelid::regclass, c.confrelid::regclass, c.* -
détacher la partition ;
- valider le reste du schéma puis valider la transaction.
UPDATE sur une table partitionnée référencée par une contrainte de clé étrangère (< v15)¶
Description du problème¶
Lorsqu’un UPDATE sur une table partitionnée référencée par une contrainte de clé étrangère
provoque la migration d’une ligne vers une autre partition, l’opération est implémentée
sous la forme d’un DELETE sur la partition source, suivi d’un INSERT sur la partition cible.
Sur les versions précédentes, cela pose un souci lorsque la contrainte de clé étrangère
implémente la clause ON DELETE. En effet, dans ce cas, le changement de partition provoque
le déclenchement de l’action associée à la commande DELETE, par exemple : une suppression.
C’est une erreur puisqu’en réalité la ligne est juste déplacée vers une autre partition.
En version 15, le trigger posé par la contrainte de clé étrangère ne se déclenche plus sur
le DELETE exécuté sur la partition, mais sur un UPDATE exécuté sur la table mère. Cela permet
d’obtenir le comportement attendu.
L’implémentation choisie a une limitation : elle ne fonctionne que si la contrainte de clé étrangère concerne la table partitionnée. Cela ne devrait pas être un facteur limitant. En effet, il est rare d’avoir des clés étrangères différentes qui pointent vers les différentes partitions. On trouve généralement plutôt une clé étrangère qui pointe vers une ou plusieurs colonnes de la table partitionnée dans son ensemble.
Exemple du comportement en version 14 puis 15.¶
Mise en place :
CREATE TABLE tpart (i int PRIMARY KEY, t text) PARTITION BY RANGE ( i );
CREATE TABLE tpart_1_10 PARTITION OF tpart FOR VALUES FROM (1) TO (10);
CREATE TABLE tpart_11_20 PARTITION OF tpart FOR VALUES FROM (11) TO (20);
CREATE TABLE foreignk(j int PRIMARY KEY, i int CONSTRAINT fk_tpart_i REFERENCES tpart(i) ON DELETE CASCADE, t text );
INSERT INTO tpart VALUES (1, 'value 1');
INSERT INTO foreignk VALUES (1, 1, 'fk 1');
Voici les données présentes dans les tables :
Mise à jour et nouveaux contrôles en version 14 :
La ligne a bien changé de partition, en revanche elle a été supprimée de la table qui référence la table partitionnée.
Avec PostgreSQL 15, on obtient désormais l’erreur suivante :