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 1rvXQk-00Ccka-R7 for pgsql-general@arkaria.postgresql.org; Sat, 13 Apr 2024 07:04:07 +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 1rvXQj-0030o6-V9 for pgsql-general@arkaria.postgresql.org; Sat, 13 Apr 2024 07:04:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rvXQj-0030ny-Go for pgsql-general@lists.postgresql.org; Sat, 13 Apr 2024 07:04:05 +0000 Received: from mail-qv1-xf31.google.com ([2607:f8b0:4864:20::f31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rvXQg-000gz5-7G for pgsql-general@lists.postgresql.org; Sat, 13 Apr 2024 07:04:04 +0000 Received: by mail-qv1-xf31.google.com with SMTP id 6a1803df08f44-6962a97752eso12507116d6.2 for ; Sat, 13 Apr 2024 00:04:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712991839; x=1713596639; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=PFwL0sLwXu1vDJ9mzlhUxSBPq1SOex2xHrznPhfrbyU=; b=SRh/ltIpT5xpyIJIaopF8RpuA9y8l2F9xodTifGCxkO4Hxs3wyiRWusbD/X9z2Rj47 Pdz6Y5DwbZt7gAkQC28vERA3m1hpIAewvv1dpJf2yuvYxo92t9mZolIZB942tIZHt2vn 52JgkXwcn1iWmiJh+kVynJ/EWuwtkJemZ4PvuH0ICTZRpTlu6bv2KKRo4LxtY3ynZEHU PjR0GCSvhR9ujcDTAXZ9jqD42nska1/CidzGt73G9LoVMdSzvNiDTfkntniFEBmO6DNl sbJAkfuy4HV1lmfLVyf7RTFiSV+zXZQqTaTmJf8TRx11Nm+zyuA7mKmF04wjYeggO1Pq FRGg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712991839; x=1713596639; h=cc: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=PFwL0sLwXu1vDJ9mzlhUxSBPq1SOex2xHrznPhfrbyU=; b=kra6n//XAh/6mShlCmXTKwCvA2mP5F4B6gK5Y2tx6bl+YCFrMT9nzkj5oGkX2woXS0 EulhWG7sht6T1fCwexxnLEUpRJHtNFi0wwdg4csXIi6Lt/HOj++r75jOvfobDx0IszLN oNgOIGu8DYjQTNOE2RTalriTfKLmZo8riLsExcMp16iTudRuj9bmyvp0CBr88+gQ0+Wa a8haHwwmXHzGrW65B4GO4TjSfv7zdM2Uta9oewv8Z+wO1QF6yFEGVH2ESmiwBwa/u/k6 xnFHLFQK4vcbV33pksiFprUjhtea61MYJYbqL/Y8lNSwAYfrzeg3G5YI5RXd0v4WLeq8 qxDA== X-Gm-Message-State: AOJu0YybP6m7gh6HCewj/RQHvXwfP4Zw2A6iBpFxu0fMsVzABan7Knba MRfdxp+HpCkDiiOjOuDq/Dshjy14e+dVOav7GQgYGvr370K4eW14oMKqNFf22H+3Me9OQfO+gdc vnX4lHV6+FzJ6J7TmlPt6MP5KWl8= X-Google-Smtp-Source: AGHT+IGIpPdMm0IQ08eSP9EY1QEKVLzk4a8g2Foi1LBIPONVgkvr7DogYuDDYd1NRZBzQ36jvLgWFC0JgqUbB9Qd4xU= X-Received: by 2002:a05:6214:516:b0:690:bb1c:9dc4 with SMTP id px22-20020a056214051600b00690bb1c9dc4mr5269642qvb.28.1712991839471; Sat, 13 Apr 2024 00:03:59 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: veem v Date: Sat, 13 Apr 2024 12:33:47 +0530 Message-ID: Subject: Re: Question on trigger To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000018522a0615f4fe20" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000018522a0615f4fe20 Content-Type: text/plain; charset="UTF-8" Thank you Adrian. So it seems the heavy DML tables will see an impact if having triggers (mainly for each row trigger) created on them. And also the bulk DML/array based insert (which inserts multiple rows in one short or one batch) , in those cases it seems the trigger will not make that happen as it will force it to make it happen row by row, as the trigger is row based. Will test anyway though. On Thu, 11 Apr 2024 at 22:00, Adrian Klaver wrote: > On 4/11/24 07:31, veem v wrote: > > Hi, We used to use Oracle database in which we had audit > > triggers(something as below) mandated for all tables by the control > > team. Now we are going to use the postgresql 15.4 database for one of > > our applications. So,wanted to understand if there exists any downside > > of such audit trigger setup for all the tables? Will it impact the bulk > > data insert/update/delete OR slowdown of any of the DML operations > > significantly (and thus will not be advisable to use for all tables but > > selected ones)? > > Triggers are overhead in Postgres as they where in Oracle. If they > didn't cause an issue in Oracle I would suspect that would also be the > case in Postgres. To confirm you would need to create a test setup and > run some common operations and see what the overhead is. > > Some potential performance improvements: > > https://www.postgresql.org/docs/current/sql-createtrigger.html > > "...a trigger that is marked FOR EACH STATEMENT only executes once for > any given operation, regardless of how many rows it modifies (in > particular, an operation that modifies zero rows will still result in > the execution of any applicable FOR EACH STATEMENT triggers)." > > <...> > > "The REFERENCING option enables collection of transition relations, > which are row sets that include all of the rows inserted, deleted, or > modified by the current SQL statement. This feature lets the trigger see > a global view of what the statement did, not just one row at a time. > This option is only allowed for an AFTER trigger that is not a > constraint trigger; also, if the trigger is an UPDATE trigger, it must > not specify a column_name list. OLD TABLE may only be specified once, > and only for a trigger that can fire on UPDATE or DELETE; it creates a > transition relation containing the before-images of all rows updated or > deleted by the statement. Similarly, NEW TABLE may only be specified > once, and only for a trigger that can fire on UPDATE or INSERT; it > creates a transition relation containing the after-images of all rows > updated or inserted by the statement." > > > As example: > > https://www.postgresql.org/docs/current/plpgsql-trigger.html > > Example 43.7. Auditing with Transition Tables > > > > > CREATE OR REPLACE TRIGGER TAB_AUD_TRG > > BEFORE DELETE OR INSERT OR UPDATE > > ON tab > > FOR EACH ROW > > BEGIN > > IF inserting THEN > > :NEW.create_timestamp := systimestamp; > > :NEW.create_userid := sys_context('USERENV','SESSION_USER'); > > :NEW.update_timestamp := systimestamp; > > :NEW.update_userid := sys_context('USERENV','SESSION_USER'); > > ELSIF updating THEN > > IF updating('create_userid') OR updating('create_timestamp') > THEN > > :new.create_userid := :old.create_userid; > > :new.create_timestamp := :old.create_timestamp; > > END IF; > > :NEW.update_timestamp := systimestamp; > > :NEW.update_userid := sys_context('USERENV','SESSION_USER'); > > END IF; > > END; > > / > > > > Regards > > Veem > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --00000000000018522a0615f4fe20 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you Adrian.

So it seems the heavy= DML tables will see an impact if having triggers (mainly for each row trig= ger) created on them.=C2=A0

And also the bulk DML/array = based insert (which inserts multiple rows in one short or one batch) , in t= hose cases it=C2=A0seems the trigger will not make that happen as it will f= orce it to make it happen row by row, as the trigger is row based. Will tes= t anyway though.

On Thu, 11 Apr 2024 at 22:00, Adrian Klaver &= lt;adrian.klaver@aklaver.com> wrote:
On= 4/11/24 07:31, veem v wrote:
> Hi, We used to use Oracle database in which we had audit
> triggers(something as below) mandated for all tables by the control > team. Now we are going to use the postgresql 15.4 database for one of =
> our applications. So,wanted to understand if there exists any downside=
> of such audit trigger setup for all the tables? Will it impact the bul= k
> data insert/update/delete OR=C2=A0slowdown of any of the DML operation= s
> significantly (and thus will not be advisable to use for all tables=C2= =A0but
> selected ones)?

Triggers are overhead in Postgres as they where in Oracle. If they
didn't cause an issue in Oracle I would suspect that would also be the =
case in Postgres. To confirm you would need to create a test setup and
run some common operations and see what the overhead is.

Some potential performance improvements:

https://www.postgresql.org/docs/curren= t/sql-createtrigger.html

"...a trigger that is marked FOR EACH STATEMENT only executes once for=
any given operation, regardless of how many rows it modifies (in
particular, an operation that modifies zero rows will still result in
the execution of any applicable FOR EACH STATEMENT triggers)."

<...>

"The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or
modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time.
This option is only allowed for an AFTER trigger that is not a
constraint trigger; also, if the trigger is an UPDATE trigger, it must
not specify a column_name list. OLD TABLE may only be specified once,
and only for a trigger that can fire on UPDATE or DELETE; it creates a
transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified
once, and only for a trigger that can fire on UPDATE or INSERT; it
creates a transition relation containing the after-images of all rows
updated or inserted by the statement."


As example:

https://www.postgresql.org/docs/current/= plpgsql-trigger.html

Example 43.7. Auditing with Transition Tables

>
> CREATE OR REPLACE TRIGGER TAB_AUD_TRG
>=C2=A0 =C2=A0 BEFORE DELETE OR INSERT OR UPDATE
>=C2=A0 =C2=A0 ON tab
>=C2=A0 =C2=A0 FOR EACH ROW
> BEGIN
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 IF inserting THEN
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :NEW.create_timestamp :=3D systimest= amp;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :NEW.create_userid =C2=A0:=3D sys_co= ntext('USERENV','SESSION_USER');
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :NEW.update_timestamp :=3D systimest= amp;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :NEW.update_userid :=3D sys_context(= 'USERENV','SESSION_USER');
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSIF updating THEN
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 IF =C2=A0updating('create_userid= ') OR updating('create_timestamp') THEN
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :new.create_userid =C2= =A0 :=3D :old.create_userid;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :new.create_timestamp = =C2=A0:=3D :old.create_timestamp;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 END IF;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :NEW.update_timestamp :=3D systimest= amp;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :NEW.update_userid :=3D sys_context(= 'USERENV','SESSION_USER');
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 END IF;
>=C2=A0 =C2=A0 END;
> /
>
> Regards
> Veem

--
Adrian Klaver
adrian.klave= r@aklaver.com

--00000000000018522a0615f4fe20--