Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Trigger usecase
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <CAD=mzVU0rx4A0onfC4jHxZ7XreZrk9DNa9pFoGDOjPD1ZhfHgw@mail.gmail.com>
Date: Wed, 31 Jul 2024 08:40:24 +0200
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <CAD32BEE-CF9B-4289-B7D1-8A4E0D68BF92@gmail.com>
References: <CAD=mzVU0rx4A0onfC4jHxZ7XreZrk9DNa9pFoGDOjPD1ZhfHgw@mail.gmail.com>
To: sud <suds1434@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/CAD32BEE-CF9B-4289-B7D1-8A4E0D68BF92%40gmail.com>
Precedence: bulk


> On 30 Jul 2024, at 17:16, sud <suds1434@gmail.com> wrote:
>=20
> Hello,=20
>=20
> We have a streaming application (using apache flink and kafka) which =
populates data in the tables of a postgres database version 15.4.
>=20
> 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?=20
>=20
> 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.=20

Is your data consistent if this operation doesn=E2=80=99t 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=E2=80=99t 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.