MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Wed, 4 Sep 2024 18:19:47 +0530
Message-ID: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
Subject: question on audit columns
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009b905406214a9ca4"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqd%3D83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug%40mail.gmail.com>
Precedence: bulk

--0000000000009b905406214a9ca4
Content-Type: text/plain; charset="UTF-8"

Hello,
In postgres database , we have all the tables with audit columns like
created_by_user, created_timestamp,updated_by_user, updated_timestamp. So
we have these fields that were supposed to be populated by the time at
which the insert/update operation happened on the database but not at the
application level. So we are planning to populate the created_by_user,
created_timestamp columns by setting a default value of "current_timestamp"
and "current_user" for the two columns,  but no such this is available to
populate while we do the update of the row, so the only option seems to be
through a trigger.

So wanted to check with the experts here  ,considering the table will be
DML heavy table (300M+ transactions will be inserted daily), Is is okay to
have the trigger for this table for populating all the audit columns or
should we keep default for  created_by_user, created_timestamp and just
trigger for the update related two audit column? Basically wanted to see,
if the default value does the same thing as a trigger or it does something
more optimally than trigger?

Regards
Yudhi

--0000000000009b905406214a9ca4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div>In postgres database , we have all the tables w=
ith audit columns like created_by_user, created_timestamp,updated_by_user, =
updated_timestamp. So we have these=C2=A0fields that were supposed to be po=
pulated by the time at which the insert/update operation happened on the da=
tabase but not at the application level. So we are planning=C2=A0to populat=
e the created_by_user, created_timestamp columns by setting a default value=
 of &quot;current_timestamp&quot; and &quot;current_user&quot; for the two =
columns,=C2=A0 but no such this is available to populate while we do the up=
date of the row, so the only option seems=C2=A0to be through a trigger.=C2=
=A0</div><div><br></div><div>So wanted to check with the experts here=C2=A0=
 ,considering the table will be DML heavy table (300M+ transactions will be=
 inserted daily), Is is okay to have the trigger for this table for populat=
ing all the audit columns=C2=A0or should=C2=A0we keep default for=C2=A0 cre=
ated_by_user, created_timestamp and just trigger for the update related two=
 audit column? Basically wanted to see, if the default value does the same =
thing as a trigger or it does something more optimally than trigger?</div><=
div><br></div><div>Regards</div><div>Yudhi</div></div>

--0000000000009b905406214a9ca4--