Re: How to avoid Trigger ping/pong / infinite loop

On Thu, Feb 16, 2023 at 9:43 AM Dominique Devienne <ddevienne(at)gmail(dot)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

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.