Trigger to Count Number of Logical Replication Table Changes.
От | Avi Weinberg |
---|---|
Тема | Trigger to Count Number of Logical Replication Table Changes. |
Дата | |
Msg-id | DB9PR07MB718015AADA2C8F9AF81DA3DBCBB0A@DB9PR07MB7180.eurprd07.prod.outlook.com обсуждение исходный текст |
Ответы |
Re: Trigger to Count Number of Logical Replication Table Changes.
|
Список | 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;
В списке pgsql-general по дате отправления: