Horton
Menu

Thread

  1. Trigger usecase

    sud <suds1434@gmail.com> — 2024-07-30T15:16:32Z

    Hello,

We have a streaming application (using apache flink and kafka) which
populates data in the tables of a postgres database version 15.4.

Now while loading transactions data we also get some reference data
information from source (for example customer information) and for these ,
we dont want to modify or override the existing customer data but want to
keep the old data with a flag as inactive and the new record should get
inserted with flag as active. So for such use case , should we cater this
inside the apache flink application code or should we handle this using
trigger on the table level which will execute on each INSERT and execute
this logic?

I understand trigger is difficult to debug and monitor stuff. But here in
this case , team mates is saying , we shouldn't put such code logic into a
streaming application code so should rather handle through trigger.

I understand, technically its possible bith the way, but want to understand
experts opinion on this and pros ans cons?

Regards
Sud

  2. Re: Trigger usecase

    David G. Johnston <david.g.johnston@gmail.com> — 2024-07-30T15:41:26Z

    On Tue, Jul 30, 2024 at 8:16 AM sud <suds1434@gmail.com> wrote:

>
> I understand, technically its possible bith the way, but want to
> understand experts opinion on this and pros ans cons?
>
>
Have client code call a function that performs the relevant work directly
instead of having a trigger perform similar work.

Probably should just remove insert/update/delete permissions from most
users on that table as well, make the function owned by the table owner and
with security definer so it is allowed to perform the needed work.  But the
normal application user is unable to bypass using said function to perform
DML on the table.

David J.

  3. Re: Trigger usecase

    Laurenz Albe <laurenz.albe@cybertec.at> — 2024-07-30T17:24:06Z

    On Tue, 2024-07-30 at 20:46 +0530, sud wrote:
> Now while loading transactions data we also get some reference data information from
> source (for example customer information) and for these , we dont want to modify or
> override the existing customer data but want to keep the old data with a flag as
> inactive and the new record should get inserted with flag as active. So for such
> use case , should we cater this inside the apache flink application code or should
> we handle this using trigger on the table level which will execute on each INSERT
> and execute this logic? 
> 
> I understand trigger is difficult to debug and monitor stuff. But here in this case ,
> team mates is saying , we shouldn't put such code logic into a streaming application
> code so should rather handle through trigger. 
> 
> I understand, technically its possible bith the way, but want to understand experts
> opinion on this and pros ans cons?

It is largely a matter of taste.

The advantage of a trigger is that it works even if somebody bypasses the application
to insert data.

I think that triggers are easy to debug, but again, that's a matter of taste.

Yours,
Laurenz Albe

  4. Re: Trigger usecase

    sud <suds1434@gmail.com> — 2024-07-30T18:46:01Z

    On Tue, Jul 30, 2024 at 10:54 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

>
> It is largely a matter of taste.
>
> The advantage of a trigger is that it works even if somebody bypasses the
> application
> to insert data.
>
> I think that triggers are easy to debug, but again, that's a matter of
> taste.
>
>
> Thank you David and Laurenz.

Creating triggers to populates some audit table or say populating data in
audit columns (created_by, updated_by,created_date,updated_date) is fine i
believe, however this use case was to load/persist data in table with SCD-2
style, so is it good idea to use the trigger for such use case?

Not sure of the exact pros and cons, but we were following certain rules
like , if it's business logic which needs to be implemented in Database,
then it should not be done using triggers but rather should be done through
database procedure/functions. Hope this understanding correct.

Regards
Sud

  5. Re: Trigger usecase

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-07-30T18:50:48Z

    On 7/30/24 11:46, sud wrote:
> 
> On Tue, Jul 30, 2024 at 10:54 PM Laurenz Albe <laurenz.albe@cybertec.at 
> <mailto:laurenz.albe@cybertec.at>> wrote:
> 
> 
>     It is largely a matter of taste.
> 
>     The advantage of a trigger is that it works even if somebody
>     bypasses the application
>     to insert data.
> 
>     I think that triggers are easy to debug, but again, that's a matter
>     of taste.
> 
> 
> Thank you David and Laurenz.
> 
> Creating triggers to populates some audit table or say populating data 
> in audit columns (created_by, updated_by,created_date,updated_date) is 
> fine i believe, however this use case was to load/persist data in table 
> with SCD-2 style, so is it good idea to use the trigger for such use case?
> 
> Not sure of the exact pros and cons, but we were following certain rules 
> like , if it's business logic which needs to be implemented in Database, 
> then it should not be done using triggers but rather should be done 
> through database procedure/functions. Hope this understanding correct.

Triggers have to use procedures/functions so I am not understanding the 
issue.

> 
> Regards
> Sud

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  6. Re: Trigger usecase

    David G. Johnston <david.g.johnston@gmail.com> — 2024-07-30T20:28:35Z

    On Tue, Jul 30, 2024 at 11:46 AM sud <suds1434@gmail.com> wrote:

>
> Not sure of the exact pros and cons, but we were following certain rules
> like , if it's business logic which needs to be implemented in Database,
> then it should not be done using triggers but rather should be done through
> database procedure/functions. Hope this understanding correct.
>

That is my personal take.  For process-oriented stuff you can follow the
trail of calls all the way through to the end of the process and its final
result.  With triggers you follow the trail to the insert/update/delete
then stop thinking that's it, while in reality it continues because you
have triggers performing yet more work.

David J.

  7. Re: Trigger usecase

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-07-30T21:11:28Z

    On 7/30/24 13:28, David G. Johnston wrote:
> On Tue, Jul 30, 2024 at 11:46 AM sud <suds1434@gmail.com 
> <mailto:suds1434@gmail.com>> wrote:
> 
> 
>     Not sure of the exact pros and cons, but we were following certain
>     rules like , if it's business logic which needs to be implemented in
>     Database, then it should not be done using triggers but rather
>     should be done through database procedure/functions. Hope this
>     understanding correct.
> 
> 
> That is my personal take.  For process-oriented stuff you can follow the 
> trail of calls all the way through to the end of the process and its 
> final result.  With triggers you follow the trail to the 
> insert/update/delete then stop thinking that's it, while in reality it 
> continues because you have triggers performing yet more work.
> 

"On insert/update/delete to this table the following actions are taken 
via triggers using the supplied function/procedure:

Insert
     Data is sent to audit table using table_audit()
Update
     Data is sent to audit table using table_audit()
Delete
     Data is sent to audit table using table_audit()

See function specific documentation below

[...]
"
> David J.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

  8. Re: Trigger usecase

    Alban Hertroys <haramrae@gmail.com> — 2024-07-31T06:40:24Z

    > On 30 Jul 2024, at 17:16, sud <suds1434@gmail.com> wrote:
> 
> Hello, 
> 
> We have a streaming application (using apache flink and kafka) which populates data in the tables of a postgres database version 15.4.
> 
> Now while loading transactions data we also get some reference data information from source (for example customer information) and for these , we dont want to modify or override the existing customer data but want to keep the old data with a flag as inactive and the new record should get inserted with flag as active. So for such use case , should we cater this inside the apache flink application code or should we handle this using trigger on the table level which will execute on each INSERT and execute this logic? 
> 
> I understand trigger is difficult to debug and monitor stuff. But here in this case , team mates is saying , we shouldn't put such code logic into a streaming application code so should rather handle through trigger. 

Is your data consistent if this operation doesn’t happen correctly? Is it okay to have no, or multiple, records where the flag is active for the same application transaction?

The benefit of doing this in a trigger is that the operations happen in a single database transaction, guaranteeing that there is only ever a single row that has the active flag set for every application transaction.

There are other ways to guarantee that, using exclusion constraints (which you should probably have on this table anyway), which would allow to handle such in the application. Such constraints can raise exceptions in your code, that need handling.

So I say, at least put an exclusion constraint on that table if you didn’t already, and then decide what approach suits you best.

Alban Hertroys
--
Als je de draak wilt steken met iemand,
dan helpt het,
als die een punthoofd heeft.