Hi The only thing that can be established is that if the record does not exist, the trigger will not be updating anything in the table "tst.time_audit_tbl" for the condition "table_name = CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME)"
Maybe, checking before the UPDATE, if the record does not exist, add it. IF NOT EXISTS (SELECT 1 FROM tst.time_audit_tbl WHERE table_name = CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME)) THEN INSERT INTO FROM tst.time_audit_tbl (table_name) VALUES (CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME)); END IF Atte. JRBM El jue, 16 nov 2023 a las 14:19, Avi Weinberg (<a...@gilat.com>) escribió: > 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. >
