Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sH9vq-0042Il-95 for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 22:25:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sH9vn-00Amk5-Ez for pgsql-general@arkaria.postgresql.org; Tue, 11 Jun 2024 22:25:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sH9vm-00Amjw-WB for pgsql-general@lists.postgresql.org; Tue, 11 Jun 2024 22:25:32 +0000 Received: from mail-oi1-x22d.google.com ([2607:f8b0:4864:20::22d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sH9vl-000rQ2-50 for pgsql-general@postgresql.org; Tue, 11 Jun 2024 22:25:30 +0000 Received: by mail-oi1-x22d.google.com with SMTP id 5614622812f47-3d21b3da741so929599b6e.2 for ; Tue, 11 Jun 2024 15:25:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718144728; x=1718749528; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=FlZLUHpoxIQlDRvbnUaWgoKERb2Eh6vMP1kMOAhQlzU=; b=LKlMu094ufcNMxwCr9gPOl/Jw8Js+LuoENMguJdr0iWbhdLCRZ91QI/zENzjBHiWzP Dimmj2gC5IobB5KEQ1m06VsBRUKEjFjgDStcUdGvys2JwmGeGj7Kt8oZ/oPKXNkZLO+B JIqBHGrsMqPZ8HcThuI2jpbAtBrlxFHwedaGMBiYOZbJx5SqfJvob9tMGfH0obHVzkSa sOsKsDCmR4iHZNJ9FOXPxo/kh65ncAwGeIstVFvAapxw5/PoguGdW236KEfp7ItQ2Tqt FP+WMPNR8faFNOGHcwjf4GrXkTGH6iP/eZMiV6XG3F3fHoezu3Cc7e5ZFQsncKwSxhla UKvQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718144728; x=1718749528; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=FlZLUHpoxIQlDRvbnUaWgoKERb2Eh6vMP1kMOAhQlzU=; b=JfoOJpYFnp25kUqLtGjb6MCj+Wx/2F0WZG3T3KQ+SZTEErJ+8KZ6TPr5BbY9GTXokb HQx1Dv6n7ezqecIf9/GgKlsrzCLqlFRbDW7FyNpz8xwzNAQXNe8AXcq3sRTG2U7SzsNo Hj8HUYiE5V0sJtlo59cQu8o157G9BJ1650dOqcB7RPSVnVVOIMRLsksY+fj1RrcQeEM4 jh1dhtkJIrZspjTsU/P6ddOw40yYAON+rT0P8ia8ZFIDkU5Z1ne08Y1wJi25YXW3Y+93 ByR36sX5sf7z8QPtG5HEqBfy5At1VXku3Do1eEw9AxcyVwacGLbssmlO9fqItdbFlYNx 1AZA== X-Gm-Message-State: AOJu0YwgFasmm3324qd1gBKGKVjlnx+ku1YmUdu8fuX2Lua8/6eau4n1 ulIXrcfQrfxJ1vyYZ++SQqISi1aL3AbRvpmRmlnQ5rfJoheMno3tB5bVagaxuMdHwsNg9BDIt1Y 0+TNNuADQ2VTU1rg/h09MLIBNs1em9A== X-Google-Smtp-Source: AGHT+IGL/kdyEW2oIyVCpCdrx7oVknhKrIFeGcvBffRmFb+wyN5AyYhrbkZhzNka1bh/NOvWKoH8+WQBT84Vur8GIQU= X-Received: by 2002:a05:6870:a923:b0:254:acbe:5828 with SMTP id 586e51a60fabf-25514b7ed32mr143249fac.18.1718144727887; Tue, 11 Jun 2024 15:25:27 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 11 Jun 2024 18:25:16 -0400 Message-ID: Subject: Re: Does trigger only accept functions? To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000002db6bb061aa4befc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002db6bb061aa4befc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jun 11, 2024 at 2:53=E2=80=AFPM veem v wrote: > > On Tue, 11 Jun 2024 at 17:03, hubert depesz lubaczewski > wrote: > >> On Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote: >> > to be called from ~50 triggers? or any other better approach exists to >> > handle this? >> >> pgaudit extension? >> >> Or just write all the changes to single table? >> >> Or use dynamic queries that will build the insert based on the name of >> table the event happened on? >> >> Or pass arguments? >> >> Best regards, >> >> depesz >> >> > Thank you so much. I hope you mean something as below when you say making > it dynamic. Because we have the audit tables having more number of column= s > as compared to the source table and for a few the column name is a bit > different. > > -- Trigger for deletes > CREATE TRIGGER before_delete > BEFORE DELETE ON source_table > FOR EACH ROW EXECUTE FUNCTION log_deletes(); > > > -- Trigger for source_table1 > CREATE TRIGGER before_delete_source_table1 > BEFORE DELETE ON source_table1 > FOR EACH ROW EXECUTE FUNCTION log_deletes(); > > -- Trigger for source_table2 > CREATE TRIGGER before_delete_source_table2 > BEFORE DELETE ON source_table2 > FOR EACH ROW EXECUTE FUNCTION log_deletes(); > > > CREATE OR REPLACE FUNCTION log_deletes() > RETURNS TRIGGER AS $$ > BEGIN > IF TG_TABLE_NAME =3D 'source_table1' THEN > INSERT INTO delete_audit1 ( col1, col2, col3) > VALUES (OLD.col1, OLD.col2, OLD.col3); > ELSIF TG_TABLE_NAME =3D 'source_table2' THEN > INSERT INTO delete_audit2 ( col4, col5, col6) > VALUES (OLD.col4, OLD.col5, OLD.col6); > -- Add more conditions for other tables > Dear god, no. Since all the functions are going to be similar, I'd write a shell script to generate all the triggers, one per relevant. If you're going to record every field, then save effort, and don't bother enumerating them. You'll need to dig into the PG catalog's guts to list columns in the correct order, but Google and Stack Exchange makes that easy enough. (And, of course, that single trigger would be SLOW.) This is essentially what we did 25 years ago to "logically replicate" data from our OLTP system to the OLAP system. There were two log tables for every table to be replicated: foo_LOG1 and foo_LOG2. The trigger wrote to foo_LOG1 on even days, and foo_LOG2 on odd days. It even added a current_timestamp column, and action_code ("I" for insert, "D" for delete, and "U" for update). At around 01:00, a batch job copied out all of "yesterday's" log data (there were 80-90 tables), and then truncated the table. --0000000000002db6bb061aa4befc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Jun 11, 2024 at 2:53=E2=80=AFPM v= eem v <veema0000@gmail.com>= ; wrote:

On Tue, 11 Jun 2024 = at 17:03, hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Tue, Jun 11, 2024 at 12:47:14AM +05= 30, veem v wrote:
> to be called from ~50 triggers? or any other better approach exists to=
> handle this?

pgaudit extension?

Or just write all the changes to single table?

Or use dynamic queries that will build the insert based on the name of
table the event happened on?

Or pass arguments?

Best regards,

depesz


Thank you so much. I hope you mean som= ething=C2=A0as below when you say making it dynamic. Because we have the au= dit tables having more number of columns as compared to the source table an= d for a few the column name is a bit different.

-- Trigger for deletes
CREATE TRIGGER before_deleteBEFORE DELETE ON source_table
FOR EACH ROW EXECUTE FUNCTION log_deletes= ();


-- Trigger for source_table1
CREATE TRIGGER before_delete= _source_table1
BEFORE DELETE ON source_table1
FOR EACH ROW EXECUTE FU= NCTION log_deletes();

-- Trigger for source_table2
CREATE TRIGGER= before_delete_source_table2
BEFORE DELETE ON source_table2
FOR EACH = ROW EXECUTE FUNCTION log_deletes();


CREATE OR REPLACE FUNCTION l= og_deletes()
RETURNS TRIGGER AS $$
BEGIN
=C2=A0 =C2=A0 IF TG_TABLE= _NAME =3D 'source_table1' THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSER= T INTO delete_audit1 ( col1, col2, col3)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 VAL= UES (OLD.col1, OLD.col2, OLD.col3);
=C2=A0 =C2=A0 ELSIF TG_TABLE_NAME = =3D 'source_table2' THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO= delete_audit2 ( col4, col5, col6)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 VALUES (O= LD.col4, OLD.col5, OLD.col6);
=C2=A0 =C2=A0 -- Add more conditions for o= ther tables

Dear god= , no.

Since all the functions are going to be simi= lar, I'd write a shell script to generate all the triggers,=C2=A0one pe= r relevant.=C2=A0 If you're going to record every field, then save effo= rt, and don't bother enumerating them.=C2=A0 You'll need to dig int= o the PG catalog's guts to list columns in the correct order, but Googl= e and Stack Exchange makes that easy enough.

(And,= of course, that single trigger would be SLOW.)

Th= is is essentially what we did 25 years ago to "logically replicate&quo= t; data from our OLTP system to the OLAP system.=C2=A0 There were two log t= ables for every table to be replicated: foo_LOG1 and foo_LOG2.=C2=A0 The tr= igger wrote to foo_LOG1 on even days, and foo_LOG2 on odd days.=C2=A0 It ev= en added a current_timestamp column, and action_code ("I" for ins= ert, "D" for delete, and "U" for update).
At around 01:00, a batch job copied out all of "yesterday&= #39;s" log data (there were 80-90 tables), and then truncated the tabl= e.

--0000000000002db6bb061aa4befc--