Re: Question on trigger - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Question on trigger |
Date | |
Msg-id | 5a4a8556-bef0-4509-b8ce-98ccac24bd60@aklaver.com Whole thread Raw |
In response to | Re: Question on trigger (veem v <veema0000@gmail.com>) |
Responses |
Re: Question on trigger
|
List | pgsql-general |
On 4/13/24 00:03, veem v wrote: > 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. You said you have triggers in the Oracle database and I assumed they worked and where not a show stopping issue there. What makes you think that would be different in Postgres? What type of triggers where there in Oracle, per row, per statement or a mix? > > On Thu, 11 Apr 2024 at 22:00, Adrian Klaver <adrian.klaver@aklaver.com > <mailto: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 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 > <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 > <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 <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: