On 4/16/24 12:39, veem v wrote:
>
>
> On Sat, 13 Apr 2024 at 21:44, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> 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
adrian.klaver@aklaver.com
