public inbox for [email protected]  

help / color / mirror / Atom feed

Before image of selective columns.
4+ messages / 4 participants
[nested] [flat]

* Before image of selective columns.
@ 2025-01-10 09:50  Gambhir Singh <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Gambhir Singh @ 2025-01-10 09:50 UTC (permalink / raw)
  To: [email protected]

Hi,

I got a request from my application team that they want the before image of
selective columns when any delete occurs in DB. They will use this
information for some audit purposes. Also we have CDC configured between
on-prem PostgreSQL and Aurora PostgreSQL and certain tables are very large
in size and have heavy delete operations on them.

Please help me find a way to meet the app team's requirements without
impacting CDC.

Thanks & Regards
Gambhir Singh


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Before image of selective columns.
@ 2025-01-10 09:55  Laurenz Albe <[email protected]>
  parent: Gambhir Singh <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Laurenz Albe @ 2025-01-10 09:55 UTC (permalink / raw)
  To: Gambhir Singh <[email protected]>; [email protected]

On Fri, 2025-01-10 at 15:20 +0530, Gambhir Singh wrote:
> I got a request from my application team that they want the before image of
> selective columns when any delete occurs in DB. They will use this
> information for some audit purposes.

That looks like a job for a trigger; perhaps a statement level trigger
with a transition table.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Before image of selective columns.
@ 2025-01-10 13:40  Scott Ribe <[email protected]>
  parent: Laurenz Albe <[email protected]>
  0 siblings, 1 reply; 4+ messages in thread

From: Scott Ribe @ 2025-01-10 13:40 UTC (permalink / raw)
  To: Gambhir Singh <[email protected]>; [email protected]

> On Jan 10, 2025, at 2:55 AM, Laurenz Albe <[email protected]> wrote:
> 
> That looks like a job for a trigger; perhaps a statement level trigger
> with a transition table.

Indeed.

I just want to add from experience: put the row values into a JSON column in the audit table. Otherwise, what do you do when the table definition changes? It's not just that you would have to keep the audit table schema in sync, it's possible to have a change which is not compatible with the old definition, such that you now really can't have one table that covers both old and new exactly, and then have to resort to renaming columns or some such...




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Before image of selective columns.
@ 2025-01-10 14:43  Ron Johnson <[email protected]>
  parent: Scott Ribe <[email protected]>
  0 siblings, 0 replies; 4+ messages in thread

From: Ron Johnson @ 2025-01-10 14:43 UTC (permalink / raw)
  To: Pgsql-admin <[email protected]>

On Fri, Jan 10, 2025 at 8:41 AM Scott Ribe <[email protected]>
wrote:

> > On Jan 10, 2025, at 2:55 AM, Laurenz Albe <[email protected]>
> wrote:
> >
> > That looks like a job for a trigger; perhaps a statement level trigger
> > with a transition table.
>
> Indeed.
>
> I just want to add from experience: put the row values into a JSON column
> in the audit table. Otherwise, what do you do when the table definition
> changes? It's not just that you would have to keep the audit table schema
> in sync, it's possible to have a change which is not compatible with the
> old definition, such that you now really can't have one table that covers
> both old and new exactly, and then have to resort to renaming columns or
> some such...
>

For years, we've used triggers to populate such tables, but never had
incompatibility problems.  Mainly because we add new columns and upsize
existing columns to compatible types.

Everyone's situation is different, though.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2025-01-10 14:43 UTC | newest]

Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-10 09:50 Before image of selective columns. Gambhir Singh <[email protected]>
2025-01-10 09:55 ` Laurenz Albe <[email protected]>
2025-01-10 13:40   ` Scott Ribe <[email protected]>
2025-01-10 14:43     ` Ron Johnson <[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