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: Tue, 16 Apr 2024 13:23:06 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TUL_j2o=GBwTXwp3f2kNBQeY7jZWimN38QQo=Q35o6h9g@mail.gmail.com>
References: <CAB+=1TV2rVqySeVeviDfoKc0ApiLPQ9=i=5PM9R6ek-_aKuhOg@mail.gmail.com>
<[email protected]>
<CAB+=1TUnCwVvHSMS2+wqV8f5OpiKXwkrJ3CDJ_JpRZEQomw=Ew@mail.gmail.com>
<[email protected]>
<CAB+=1TUL_j2o=GBwTXwp3f2kNBQeY7jZWimN38QQo=Q35o6h9g@mail.gmail.com>
On 4/16/24 12:39, veem v wrote:
>
>
> On Sat, 13 Apr 2024 at 21:44, Adrian Klaver <[email protected]
> <mailto:[email protected]>> wrote:
>
> 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?
>
>
> Actually we have row level triggers in oracle which are running for
> smaller volume DML and are making the direct path inserts to happen in
> conventional row by row insert, in presence of trigger. So was wondering
Not sure what the above means, you will need to provide a more detailed
description. Though any DML you are doing on table that has any sort of
constraint, index, trigger, foreign key, default values, etc is going to
have more overhead then into an unencumbered table. FYI, some of the
preceding are system triggers, for example foreign keys.
> if it postgres we will be encountering a similar issue and batch inserts
> may be converted back to row by row automatically. And here we are going
> to process higher volume DMLS in postgresql database.
>
Hard to say with the information provided. Easiest way to find out is
create a test setup and run the code. Though I guess, as I have not
actually tried this, you could have a per row trigger and per statement
trigger for the same action and disable the per row and enable the per
statement trigger for batch operations. Then once the batch operation is
done reverse the process. Again something to test to verify.
--
Adrian Klaver
[email protected]
view thread (2+ 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