Mécanisme d'enregistrement des dates⚓
PlumePg propose un système pour garder une trace des dates de création et dernière modification des tables. Assez rudimentaire, il ne prend en charge que les tables simples, ignorant notamment les vues et vues matérialisées.
Les dates sont enregistrées dans la table z_plume.stamp_timestamp. Son champ relid contient les identifiants PostgreSQL des tables (OID), son champ created les dates de création et son champ modified les dates de dernière modification.
À l’installation de PlumePg, les fonctionnalités d’enregistrement des dates de création et dernière modification des tables sont inactives. La table z_plume.stamp_timestamp est et restera vide sans intervention de l’administrateur.
Dès lors que l’administrateur le souhaite, deux stratégies peuvent être mises en oeuvre :
Soit enregistrer automatiquement les dates pour toutes les tables dès leur création.
Soit enregistrer les dates de dernière modification uniquement pour certaines tables explicitement désignées.
Le choix de l'une de ces stratégies et le choix plus général d'activer ou non l'enregistrement des dates devront notamment prendre en considération les enjeux de performance des traitements sur la base considérée. Lorsque le suivi est activé pour une table donnée, il se matérialise par une commande UPDATE supplémentaire lancée silencieusement à chaque commande d'ajout, modification ou suppression d'enregistrements sur la table. La date n'est enregistrée qu'une fois quel que soit le nombre d'enregistrements affectés, néanmoins ceci pourrait allonger significativement le temps d'exécution de scripts impliquant l'exécution successive d'un grand nombre de requêtes INSERT, UPDATE ou DELETE.
Remarque :
Les opérations d'activation et désactivation de l'enregistrement des dates décrites ci-après impliquent la manipulation de déclencheurs sur évènement. Dès lors, elles ne peuvent être réalisées que par un super-utilisateur.
Suivi automatique intégral⚓
Pour mettre en place le suivi intégral, on activera les trois déclencheurs sur évènement de PlumePg.
ALTER EVENT TRIGGER plume_stamp_table_creation ENABLE ;
ALTER EVENT TRIGGER plume_stamp_table_modification ENABLE ;
ALTER EVENT TRIGGER plume_stamp_table_drop ENABLE ;
plume_stamp_table_creation se charge d’enregistrer les dates de création des tables dans z_plume.stamp_timestamp et crée sur les nouvelles tables les déclencheurs plume_stamp_data_edit qui assureront le suivi des modifications des données. Il ne s'exécute qu'une fois par table, lors de sa création.
plume_stamp_table_modification met à jour la date de dernière modification en cas de modification de structure des tables, soit à chaque commande ALTER TABLE.
plume_stamp_table_drop efface de z_plume.stamp_timestamp les lignes correspondant aux tables qui viennent d’être supprimées.
Suivi au cas par cas⚓
Pour n’activer le suivi des modifications que sur certaines tables explicitement désignées, il faut laisser inactif le déclencheur sur évènement plume_stamp_table_creation.
ALTER EVENT TRIGGER plume_stamp_table_modification ENABLE ;
ALTER EVENT TRIGGER plume_stamp_table_drop ENABLE ;
Il faudra alors lancer manuellement la fonction z_plume.stamp_create_trigger sur une table pour commencer à enregistrer ses dates de modification. Cette fonction prend pour argument le nom de la table casté en regclass.
SELECT z_plume.stamp_create_trigger('schema_name.table_name'::regclass) ;
La fonction renvoie True si la création du déclencheur a fonctionné, False sinon. Elle doit être exécutée par le propriétaire de la table.
Une alternative consisterait à activer le suivi automatique intégral comme ci-avant, en supprimant ensuite manuellement le suivi des modifications sur les quelques tables où il n'est pas pertinent :
DROP TRIGGER plume_stamp_data_edit ON schema_name.table_name ;
DELETE FROM z_plume.stamp_timestamp WHERE relid = 'schema_name.table_name'::regclass ;
Reprise de l'existant⚓
Que plume_stamp_table_creation soit actif ou non, les déclencheurs plume_stamp_data_edit qui mettent à jour la date de dernière modification lorsque le contenu de leur table est modifié ne sont pas automatiquement créés sur les tables préexistantes.
Plutôt que de lancer sur chaque table la fonction z_plume.stamp_create_trigger, il est possible d’utiliser la fonction z_plume.stamp_create_triggers, qui permet de créer les déclencheurs pour toutes les tables d’un ou plusieurs schémas déterminés.
Exécutée sans aucun argument, la fonction crée des déclencheurs plume_stamp_data_edit sur les tables de tous les schémas hors schémas système (information_schema et tous les schémas dont le nom commence par « pg_ ») :
SELECT * FROM z_plume.stamp_create_triggers() ;
Pour créer les déclencheurs sur les tables d’un schéma :
SELECT * FROM z_plume.stamp_create_triggers('schema_name') ;
Pour créer les déclencheurs sur les tables de plusieurs schémas :
SELECT * FROM z_plume.stamp_create_triggers(schemas_include:=ARRAY['schema_name_1', 'schema_name_2']) ;
Pour créer les déclencheurs sur les tables de tous les schémas sauf exceptions listées :
SELECT * FROM z_plume.stamp_create_triggers(schemas_exclude:=ARRAY['schema_name_1', 'schema_name_2']) ;
La fonction renvoie la liste des tables des schémas cibles, en indiquant dans le champ result :
Si la création du déclencheur sur la table a réussi (« success »).
Si elle a échoué (« failure »). Le détail de l’erreur sera alors précisé par des messages.
Ou si le déclencheur existait déjà (« trigger already exists »). La fonction n’ayant alors aucun effet.
Intégration directe dans les métadonnées⚓
Pour ne pas trop affecter les performances et laisser au producteur de la donnée la maîtrise de ces informations, les dates ne sont pas immédiatement mises à jour dans les fiches de métadonnées, seulement dans la table stamp_timestamp, qui peut être considérée comme un espace de stockage temporaire. Le plugin QGIS permet de les importer facilement lors de l’édition d’une fiche, soit à la demande en cliquant sur un bouton, soit automatiquement à l’ouverture de la fiche (cf. Métadonnées calculées, catégories dct:created et dct:modified).
Il est toutefois possible de forcer l’actualisation au fil de l’eau de la date de dernière modification en activant le déclencheur stamp_timestamp_to_metadata défini sur la table stamp_timestamp :
ALTER TABLE z_plume.stamp_timestamp ENABLE TRIGGER stamp_timestamp_to_metadata ;
Ceci ne vaudra que pour les tables qui ont déjà une fiche de métadonnées, raison pour laquelle les dates de création ne sont pas concernées.
Cette approche a un réel intérêt pour assurer que les dates ne soient pas perdues, car les fiches de métadonnées peuvent être considérées comme un lieu de stockage pérenne, tandis que le contenu de la table stamp_timestamp n'est par exemple pas préservé en cas de restauration de la base.
Lorsque ce déclencheur est actif, il n’y a aucun intérêt à utiliser les fonctionnalités de calcul pour la date de dernière modification (catégorie dct:modified).
Observations sur la gestion des suppressions de tables⚓
Quelle que soit la stratégie choisie, il n’est pas indispensable d’activer le déclencheur sur évènement plume_stamp_table_drop. À défaut, il faudra éliminer régulièrement les lignes de z_plume.stamp_timestamp correspondant aux tables qui n’existent plus avec la fonction z_plume.stamp_clean_timestamp.
SELECT z_plume.stamp_clean_timestamp() ;
La fonction renvoie le nombre de lignes supprimées de z_plume.stamp_timestamp. Elle doit être exécutée par le propriétaire de z_plume.stamp_timestamp.
À noter qu’il n’est généralement pas intéressant de conserver des lignes mortes dans la table stamp_timestamp, sauf à disposer d’un moyen pour retrouver le nom de la table correspondante à partir de son ancien identifiant (OID) désormais non référencé dans pg_class.
Fausses modifications⚓
Le mécanisme mis en place par PLumePg peut donner lieu à des faux positifs, c'est-à-dire à des cas où la date de dernière modification sera actualisée sans que la table ni son contenu n’aient véritablement changé.
Le déclencheur sur évènement plume_stamp_table_modification est ainsi activé par toutes les commandes ALTER TABLE, y compris celles qui n’affectent pas réellement la table, comme un changement de nom qui conserve le nom d’origine.
Les déclencheurs plume_stamp_data_edit sont activés par toutes les commandes INSERT, UPDATE, DELETE et TRUNCATE, y compris celles qui - sans pour autant échouer - n’ont aucun effet. Par exemple une commande UPDATE ou DELETE telle qu’aucune ligne ne remplit la condition de sa clause WHERE. Il paraissait en effet préférable d’avoir recours à des déclencheurs ON EACH STATEMENT qu’à des déclencheurs ON EACH ROW, certes moins susceptibles de retenir des fausses modifications mais susceptibles d’allonger considérablement le temps d’exécution des requêtes.