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;