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