public inbox for [email protected]
help / color / mirror / Atom feedRe: Question on trigger
2+ messages / 2 participants
[nested] [flat]
* Re: Question on trigger
@ 2024-04-16 19:39 veem v <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: veem v @ 2024-04-16 19:39 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sat, 13 Apr 2024 at 21:44, Adrian Klaver <[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 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Question on trigger
@ 2024-04-16 20:23 Adrian Klaver <[email protected]>
parent: veem v <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-04-16 20:23 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: pgsql-general <[email protected]>
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]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-16 20:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-16 19:39 Re: Question on trigger veem v <[email protected]>
2024-04-16 20:23 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox