Re: Question on trigger

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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com