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

On Thu, Feb 16, 2023 at 6:58 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

I don't see why you say that.

So is the current system working or not?
>

The legacy system is 3-tier, so uses the denormalized info in C++ in the
mid-tier,
while the new system uses PostgreSQL and is 2-tier, so the same denormalized
info must drive both modes of execution. The same code-base is used for
both,
but when different backends. Making the code base work under two back ends,
is already hard enough, w/o changing it more extensively to use a new
normalized
model even in the legacy case. So is that clearer?

But that's a bit orthogonal to my question too.

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

I'm not following. How is that related to mutual synchronization via
triggers?
Keeping two copies of the data is of course denormalization, but cannot be
avoided.
One copy is basically an optimization for RLS, so could be read-only I
guess, making
the sync one-way and simpler, but then that would force any new code to
also use
the old denormalized way to update the info. Thus I'd prefer the new model
to be
read-write, but then that requires two-sync sync. Thus my question.