From: | Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella(at)gmail(dot)com> |
---|---|
To: | Avi Weinberg <AviW(at)gilat(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Trigger to Count Number of Logical Replication Table Changes. |
Date: | 2023-11-17 19:32:31 |
Message-ID: | CAHbZ42wqumf9kiB6wDzabqroeSd_rui8h-Euw4Y414iHfd6uVA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Torsten Förtsch | 2023-11-17 20:00:20 | query plan |
Previous Message | Tom Lane | 2023-11-17 18:48:54 | Re: Unused CTE affects result set |