Обсуждение: Before image of selective columns.
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
Gambhir Singh
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
> On Jan 10, 2025, at 2:55 AM, Laurenz Albe <laurenz.albe@cybertec.at> 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 whenthe table definition changes? It's not just that you would have to keep the audit table schema in sync, it's possibleto have a change which is not compatible with the old definition, such that you now really can't have one table thatcovers both old and new exactly, and then have to resort to renaming columns or some such...
On Fri, Jan 10, 2025 at 8:41 AM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jan 10, 2025, at 2:55 AM, Laurenz Albe <laurenz.albe@cybertec.at> 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!