public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alban Hertroys <[email protected]>
To: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Trigger usecase
Date: Wed, 31 Jul 2024 08:40:24 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAD=mzVU0rx4A0onfC4jHxZ7XreZrk9DNa9pFoGDOjPD1ZhfHgw@mail.gmail.com>
References: <CAD=mzVU0rx4A0onfC4jHxZ7XreZrk9DNa9pFoGDOjPD1ZhfHgw@mail.gmail.com>


> On 30 Jul 2024, at 17:16, sud <[email protected]> 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.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Trigger usecase
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox