Postgres Pro
Downloads
Home   >   mailing lists

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

How to avoid Trigger ping/pong / infinite loop

From
Dominique Devienne
Date:
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.

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.

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

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

From
Adrian Klaver
Date:
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.klaver@aklaver.com

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

From
Dominique Devienne
Date:
On Thu, Feb 16, 2023 at 6:58 PM Adrian Klaver <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. 

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.

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

From
"David G. Johnston"
Date:
On Thu, Feb 16, 2023 at 10:43 AM Dominique Devienne <ddevienne@gmail.com> wrote:
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).

As with the other question - a trigger does not know how the insert/update/delete action came into existence, it is only reacting to the fact that a given row is being added/updated/removed from its table.  You might be able to expend a non-trivial amount of effort trying to figure out schemes whereby such information can be inferred but you are fighting the system.

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

Then maybe you need to design the new system to behave in a manner similar to the legacy system for the stuff they share in common.  You can then have a uni-directional trigger going from legacy to modern.

David J.

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

From
David Wheeler
Date:

Are there techniques for situations like this?

Just have two triggers, one for each column, and ensure that if your trigger doesn’t change the value then it doesn’t do an update on the other column. Each time you do update both triggers will run but only one will make a change, so that will break the cycle

For insert case ensure the triggers do nothing if their source col is null

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

From
Adrian Klaver
Date:
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

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

From
Ken Tanzer
Date:
On Thu, Feb 16, 2023 at 9:43 AM Dominique Devienne <ddevienne@gmail.com> 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.

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.

Are there techniques for situations like this?


Just thinking out loud, but would it work to add an optional (not NOT NULL) "last changed" timestamp field to the legacy table, like a boolean flag or a source code or some such.  If you can add the field, I would think that change might be invisible to your app.  If that part works, then your triggers could supply and use that value to determine where the most recent updates are coming from.

Cheers,
Ken
  

--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.