Re: Trigger to Count Number of Logical Replication Table Changes.

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 (<AviW(at)gilat(dot)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.
>