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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com