public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: veem v <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Question on trigger
Date: Sat, 13 Apr 2024 09:14:44 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TUnCwVvHSMS2+wqV8f5OpiKXwkrJ3CDJ_JpRZEQomw=Ew@mail.gmail.com>
References: <CAB+=1TV2rVqySeVeviDfoKc0ApiLPQ9=i=5PM9R6ek-_aKuhOg@mail.gmail.com>
<[email protected]>
<CAB+=1TUnCwVvHSMS2+wqV8f5OpiKXwkrJ3CDJ_JpRZEQomw=Ew@mail.gmail.com>
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 <[email protected]
> <mailto:[email protected]>> 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
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Question on trigger
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox