Re: Trigger to Count Number of Logical Replication Table Changes. - Mailing list pgsql-general

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 not to work without ENABLE ALWAYS.

In addition, If I try trigger that is "FOR EACH STATEMENT" it works 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 (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;

 

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.