How to avoid Trigger ping/pong / infinite loop

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to avoid Trigger ping/pong / infinite loop
Date: 2023-02-16 17:43:09
Message-ID: CAFCRh-9gaDm41u-R1Ecc1=0hAtJnisxgqUC8csWUeJpkFGGxBQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2023-02-16 17:51:04 Re: DELETE trigger, direct or indirect?
Previous Message Dominique Devienne 2023-02-16 17:17:28 Re: DELETE trigger, direct or indirect?