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

On 2/16/23 09:43, Dominique Devienne 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.

"... which would be either impossible or too slow to base any RLS policy
on."

and

"At time point, changing the legacy code base is not really an option..."

seem to be at odds.

So is the current system working or not?

>
> 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.

A VIEW over both sets of data?

>
> Are there techniques for situations like this?
>
> This question is not too far from my earlier question, in the sense that
> a trigger would need to know the context in which it was triggered, i.e.
> directly (then update the other model), or indirectly (don't update, the
> change is boomerang'ing around from our own change).
>
> Any thoughts? --DD
>
> PS: At time point, changing the legacy code base is not really an option...

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com