public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: veem v <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Question on trigger
Date: Thu, 11 Apr 2024 09:30:32 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TV2rVqySeVeviDfoKc0ApiLPQ9=i=5PM9R6ek-_aKuhOg@mail.gmail.com>
References: <CAB+=1TV2rVqySeVeviDfoKc0ApiLPQ9=i=5PM9R6ek-_aKuhOg@mail.gmail.com>
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
[email protected]
view thread (4+ messages) latest in thread
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