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

On 2/16/23 10:21, Dominique Devienne wrote:
> On Thu, Feb 16, 2023 at 6:58 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto: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.

Because saying it is impossible to use while you say you are using it is
contradictory.

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

Not really.

From your original post:

"...had-hoc text format for values, in key-value pairs in a table, "

So is the information in the mid-tier code in some 'table' like
structure or in text form in a Postgres table.

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

Avoiding triggers all together by using a VIEW query to keep all the
information in one place. Of course this depends on the answer to my
question above about where the denormalized data is actually stored.

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

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