CDC sous PostgreSQL

[romain.alcaraz]

Bonjour,

Je suis actuellement sur un projet de mise en place d'un journal qui tient en temps réel toutes les modifications, insertions et suppressions de certaines tables dans la base de données.
J'ai commencé par utiliser un outils d'ETL (Talend) qui possède des composants CDC (Change Data Capture) et qui permet de gérer ce genre de mécanismes mais après plusieurs tests cela ne me convenait pas vraiment.
J'ai donc décidé de gérer cela moi même au sein du SGBD (ici postgre) et donc j'ai créé une table 'journal' qui s'alimente en fonction des Insert, Update et Delete d'une table source.
Cette table contient donc tous les champs de la table source et en plus un op_id (l'id de la table) une date_debut (date d'insertion ou de modifs) et une date_fin (date de modifs ou date de suppression) et en fait si j'ai par exemple 1 insertion, 4 modifs sur le même enregistrement, puis une suppression, mon journal aura 5 lignes (voir procédure pour l'aspect du journal)
J'ai donc créé des triggers pour 'catcher' mes insert, update et delete qui vont appeler chacun une procédure précise qui va gérer le journal.

J'en arrive à mon problème : pour l'insertion tout va bien, mes traitements conservent une rapidité correcte mais lorsque je lance un update en masse (par exemple passer en UpperCase 2 colonnes de toute la table) le temps d'exécution est multiplié par 100. Pareil pour le Delete.
Je sais que mes procédures lance des update sur le journal dans le cas de l'update et du delete sur la source, ça doit avoir un rapport avec ça. Je cherche désespérément comment arranger tout ça mais je ne trouve pas.

J'ai mis ma procédure en fichier attachée.

Merci d'avance pour mes sauveurs !

[estofilo]

Il n'y a pas d'index sur table_log? Il en faudrait certainement pour accéler les UPDATE dessus.

[SQLpro]

Faites vous des triggers ensembliste ou FOR EACH ROW ?

D'autre part les mécanismes de CDC fait par l'intermédiaire de déclencheurs sont toujours très consommateurs en ressources et allongent par nature la durée des transactions, donc le verrouillage. C'est pourquoi lorsqu'ils sont intégrés dans le SGBDR ils ne reposent pas sur des déclencheurs, mais sur une lecture asynchrone du journal de transaction par exemple, comme c'est le cas de CDC et Change Tracking de MS SQL Server !

A +

[romain.alcaraz]

Envoyé par SQLpro

Faites vous des triggers ensembliste ou FOR EACH ROW ?
A +

Je le fait 'FOR EACH ROW' comme noté dans mon fichier .sql attaché
Par contre, vous dites qu'il est possible de mettre en place un CDC sans passer par des déclencheurs ?
Comment faire sous postgreSQL ?

Envoyé par estofilo

Il n'y a pas d'index sur table_log? Il en faudrait certainement pour accéler les UPDATE dessus.

Je vais vérifier pour les index sur la table 'journal'. Merci.

[romain.alcaraz]

Bon,
J'ai ajouté un index sur la table_log sur les champs qui forment la lé primaire de la table source (en gros les champs dont j'ai besoin dans la clause where de mon update) et c'est parfait !
Je passe de 25 lignes updatées / sec à 1232 lignes updatées / sec...Il n'y a pas photo !

Bon après je suis conscient que c'est plus lents que lorsqu'il n'y a pas de journalisation branchée mais c'est logique puisque je fais 1 update et 1 insert sur la table_log supplémentaire pour chaque update sur la table source.
Encore merci pour tout.

[SQLpro]

C'est idiot de faire des déclencheurs ligne à ligne alors que vous pouvez les faire ensembliste donc optimisés !!!
Remplacez ce genre de lignes par :

Code :

    UPDATE TABLE_LOG SET date_fin = current_timestamp WHERE <id> = OLD.<id> AND date_fin IS NULL;

Par :

Code :

1
2
3
4
5
    UPDATE TABLE_LOG 
       SET date_fin = current_timestamp 
       WHERE <id> IN (SELECT id
                      FROM   OLD
                      WHERE  date_fin IS NULL);

Et faites des triggers FOR EACH STATEMENT !

A +

[estofilo]

Non ça ne fonctionnera pas avec postgresql.