Forums PostgreSQL.fr

romainalcaraz

Membre

CDC en PostgreSQL

Bonjour,

Je suis actuellement sur un projet de journalisation de traitements BDD de type CDC sur une serveur PostgreSQL.
J'ai tenté jusqu'à présent d'utiliser le CDC géré par Talend (une solution d'ETL) mais le résultat ne me convient pas trop.
J'ai donc tenté de le faire de manière interne à ma base pour créer dynamiquement un CDC :

CREATE SCHEMA cdc;
GRANT USAGE ON SCHEMA cdc TO public;
CREATE TYPE cdc.operation_enum AS ENUM( 'DELETE', 'INSERT', 'UPDATE_BEFORE', 'UPDATE_AFTER', 'UPDATE' );
CREATE TABLE cdc.logs2
AS
SELECT
  0::BIGINT AS logs2_id,
  current_timestamp AS cdc_time,
  'DELETE'::cdc.operation_enum AS cdc_opeation,
  src2.*
FROM src2 WHERE 0 = 1;
GRANT INSERT ON cdc.logs2 TO public;

ALTER TABLE cdc.logs2 ADD PRIMARY KEY( logs2_id);
CREATE SEQUENCE cdc.logs2_id_seq ;
GRANT UPDATE ON cdc.logs2_id_seq TO public;
ALTER TABLE cdc.logs2 ALTER logs2_id SET DEFAULT nextval('cdc.logs2_id_seq'::regclass);
ALTER TABLE cdc.logs2 ALTER cdc_time SET DEFAULT current_timestamp;

CREATE OR REPLACE FUNCTION cdc.logs2_archive()
RETURNS trigger AS $$
DECLARE
  op cdc.operation_enum ;
BEGIN
  op = TG_OP;
  IF (TG_WHEN = 'BEFORE' THEN
    IF (TG_OP = 'UPDATE' THEN
      op = 'UPDATE_BEFORE';
    END IF;
    INSERT INTO cdc.logs2 VALUES ( DEFAULT, DEFAULT, op, OLD.*);
  ELSE
    IF (TG_OP = 'UPDATE' THEN
      op = 'UPDATE_AFTER';
    END IF;
    INSERT INTO cdc.logs2 VALUES ( DEFAULT, DEFAULT, op, NEW.*);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cdc_logs2_before BEFORE UPDATE OR DELETE ON src2 FOR EACH ROW EXECUTE PROCEDURE cdc.logs2_archive();
CREATE TRIGGER cdc_logs2_after AFTER INSERT OR UPDATE ON src2 FOR EACH ROW EXECUTE PROCEDURE cdc.logs2_archive();

Pour être clair, j'ai récupéré ce script sur un forum et je l'ai remanié.
Je l'avais exécuté sans modifier mais cela ne marchait pas : lorsque je faisais une suppression sur ma table à 'loguer' (src2), les DELETE ne s'appliquaient pas (les enregistrements réapparaissaient).
Je l'ai modifié, enlevé ce qui me paraissait inutile mais le problème persiste : mes DELETE ne s'appliquent pas !
Est ce que quelqu'un arrive à voir pourquoi ?

Merci !

Marc Cousin

Membre

Re : CDC en PostgreSQL

- Les acronymes nuisent souvent à la compréhension. C'est quoi 'CDC' dans ce contexte ?
- Que veut dire 'ne s'appliquent pas': votre trigger empêchait le DELETE ?

---

Marc.

gleu

Administrateur

Re : CDC en PostgreSQL

La fonction est fausse. Vous ouvrez des parenthèses que vous ne fermez pas dans les structures IF.

---

Guillaume.

gleu

Administrateur

Re : CDC en PostgreSQL

Et pour répondre directement à la question, la doc (http://docs.postgresql.fr/9.0/plpgsql-trigger.html) explique très bien le problème :

Dans le cas d'un before-trigger sur une commande DELETE, la valeur renvoyée n'a aucun effet direct mais doit être non-nulle pour permettre à l'action trigger de continuer. Notez que NEW est nul dans le cadre des triggers DELETE et que renvoyer ceci n'est pas recommandé dans les cas courants. Une pratique utile dans des triggers DELETE serait de renvoyer OLD.

Comme de toute façon, vous ne voulez pas agir sur la finalité des commandes sur la table src2, le plus simple serait de n'utiliser que des triggers after.

---

Guillaume.

romainalcaraz

Membre

Re : CDC en PostgreSQL

CDC c'est pour change data capture pardon.
J'ai un schema ayant ce nom pour séparer mes tables de journal et de sources.

ne s'appliquent pas veut dire que mes DELETE s'effectue mais mes enregistrement réapparaissent immédiatement. quand je fais un DELETE puis un SELECT * j'ai tous (y compris mes enregistrements supprimés).

J'ai modifié ma fonction :

CREATE OR REPLACE FUNCTION cdc.logs2_archive()
RETURNS trigger AS $$
DECLARE
op cdc.operation_enum ;
BEGIN
    op = TG_OP;
    IF (TG_WHEN = 'BEFORE') THEN
        INSERT INTO cdc.logs2 VALUES ( DEFAULT, DEFAULT, op, OLD.*);
    ELSE
        INSERT INTO cdc.logs2 VALUES ( DEFAULT, DEFAULT, op, NEW.*);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Je pensais que c'était mon RETURN NEW; et j'ai donc mis un RETURN NULL; mais cela ne change rien.
Pour les parenthèses c'est juste un bug d'écriture mais elle y étaient bien.

gleu

Administrateur

Re : CDC en PostgreSQL

Vous devez renvoyer une valeur non NULL. Donc si vous utilisez toujours un trigger before, le plus simple est de renvoyer OLD.

---

Guillaume.

Marc Cousin

Membre

Re : CDC en PostgreSQL

Pourquoi vous embêtez vous, pour un trigger qui ne fait que logguer, à écrire du code 'BEFORE' ? Vous pouvez tout écrire en trigger AFTER, ce sera beaucoup plus simple (et plus sûr, puisque vous ne risquez pas de modifier l'ordre). Ça n'a pas vraiment de sens de vous embêter à détecter si le trigger est 'BEFORE' ou 'AFTER', c'est vous qui allez le mettre en place. Ne mettez que des triggers after…

---

Marc.

romainalcaraz

Membre

Re : CDC en PostgreSQL

C'est bon j'ai reussi à régler mon problème j'ai appliquer ce script :
CREATE TABLE logs2
AS
SELECT
    0::BIGINT AS operation_id,
    ''::char AS operation,
    current_timestamp::timestamp AS operation_time,
    src2.*
FROM src2
WHERE 1 = 0;

ALTER TABLE logs2 ADD PRIMARY KEY(operation_id);
CREATE SEQUENCE operation_id_seq ;
ALTER TABLE logs2 ALTER operation_id SET DEFAULT nextval('operation_id_seq'::regclass);
ALTER TABLE logs2 ALTER operation_time SET DEFAULT current_timestamp;
       
CREATE OR REPLACE FUNCTION audit_src2() RETURNS TRIGGER AS $logs2$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO logs2 VALUES (DEFAULT,'D', DEFAULT, OLD.*);
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO logs2 VALUES (DEFAULT,'U', DEFAULT, NEW.*);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO logs2 VALUES (DEFAULT,'I', DEFAULT, NEW.*);
        RETURN NEW;
    END IF;
    RETURN NULL; -- le résultat est ignoré car il s'agit d'un trigger AFTER
END;
$logs2$ language plpgsql;
       
CREATE TRIGGER logs2
    AFTER INSERT OR UPDATE OR DELETE ON src2
    FOR EACH ROW EXECUTE PROCEDURE audit_src2();

Et c'est parfait !