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