Re: How to avoid Trigger ping/pong / infinite loop - Mailing list pgsql-general
On Thu, Feb 16, 2023 at 9:43 AM Dominique Devienne <ddevienne@gmail.com> wrote:
Hi. I have a large "legacy" code base that write information necessary for Row-Level-Security in a highly denormalized custom had-hoc text format for values, in key-value pairs in a table, which would be either impossible or too slow to base any RLS policy on. The values are basically lists or maps, depending on the key.I also have new code that will read and write that same information, but would much prefer to use a normalized data model, the same one that would be appropriate for efficient RLS.So I'm wondering if I can have my cake and eat it too, by synchronizing the normalized and denormalized information (necessarily duplicated then), server-side via triggers, w/o running into infinite loops.Are there techniques for situations like this?
Just thinking out loud, but would it work to add an optional (not NOT NULL) "last changed" timestamp field to the legacy table, like a boolean flag or a source code or some such. If you can add the field, I would think that change might be invisible to your app. If that part works, then your triggers could supply and use that value to determine where the most recent updates are coming from.
Cheers,
Ken
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: