|
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).
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; |
