Feedback-ID: i76984098:Fastmail
Message-ID: <7480b130-7926-439e-b550-181cbfc12a35@aklaver.com>
Date: Tue, 16 Apr 2024 13:23:06 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Question on trigger
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAB+=1TV2rVqySeVeviDfoKc0ApiLPQ9=i=5PM9R6ek-_aKuhOg@mail.gmail.com>
 <f6131f59-d4a8-42a2-9870-eb330716f161@aklaver.com>
 <CAB+=1TUnCwVvHSMS2+wqV8f5OpiKXwkrJ3CDJ_JpRZEQomw=Ew@mail.gmail.com>
 <5a4a8556-bef0-4509-b8ce-98ccac24bd60@aklaver.com>
 <CAB+=1TUL_j2o=GBwTXwp3f2kNBQeY7jZWimN38QQo=Q35o6h9g@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAB+=1TUL_j2o=GBwTXwp3f2kNBQeY7jZWimN38QQo=Q35o6h9g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7480b130-7926-439e-b550-181cbfc12a35%40aklaver.com>
Precedence: bulk

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