Trigger to Count Number of Logical Replication Tables Changes

34 views Asked by At

I'm using Postgres (13 and 15) logical replication to sync data from two servers. I would like to have an update counter whenever data is changed. The counter can be incremented by 1 even if multiple rows are updated, but it is also ok to be incremented the counter by the number of rows updated (but it seems less efficient to me).
I need the counter to increase after initial sync as well as after regular logical replication sync.

Triggers seems not to work without ENABLE ALWAYS.

In addition, If I try trigger that is "FOR EACH STATEMENT" it seems to work only for initial sync and not for regular logical replication sync.

Having "per row" set_time_trig trigger, takes about 1 minute when updating 50k rows in one transaction (when all I need is to increase update_count by 1, why spend 1 minute for it).

How can I improve this?

CREATE TABLE IF NOT EXISTS tst.t2
(
    id bigint NOT NULL,
    c1 int,
    CONSTRAINT pk_t2 PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS tst.time_audit_tbl
(
    table_name character varying(63) COLLATE pg_catalog."default" NOT NULL,
    update_count integer DEFAULT 0,
    CONSTRAINT updated_time_audit_unique UNIQUE (table_name)
);


CREATE FUNCTION tst.set_time() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
    DECLARE
      updated_count int;
    BEGIN
        UPDATE tst.time_audit_tbl SET update_count = update_count + 1 WHERE table_name = CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME);
        GET DIAGNOSTICS updated_count = ROW_COUNT;
        IF updated_count = 0 THEN
              RAISE EXCEPTION 'set_updated_time().  Table not found %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;
        END IF;

        RETURN coalesce(NEW, OLD);      
    END;
    $$;


CREATE  TRIGGER set_time_trig
    AFTER INSERT OR DELETE OR UPDATE 
    ON tst.t2
    FOR EACH ROW
    EXECUTE FUNCTION tst.set_time();
    
ALTER TABLE tst.t2 ENABLE ALWAYS TRIGGER set_time_trig;
0

There are 0 answers