Обсуждение: Trigger functions and FDW tables
Background: About two years ago the membership system I developed for a client was moved from our in-house postgresql app to civi-CRM, which uses MySQL. (I'm semi-retired, the move to civi-CRM is part of a long term technology change.)
We have a FDW that simulates the old membership table that can be used to get a record from the civi-CRM servers (in a different data center), it takes 4-5 seconds for each query, how much of this is network delays and how much is how long it takes to build a record from the very different data structures used by civi-CRM is unclear.
There is also a materialized view of the old membership table that is updated twice a day (6PM and 3AM) from the civi-CRM FDW, it has about 1 million rows and takes anywhere from 30 to 75 minutes to update.
While our membership transactions are now handled in civi-CRM, we still do event registrations in the Postgresql system. The problem is if a new member is created in civi-CRM, it might be up to 15 hours until that member record is available in the materialized view table.
We have a trigger function that handles updating associated tables when an event registration transaction is processed (updating the accounting and event registration systems). It was checking the materialized view membership table to verify that the ID was valid (and to look up some data about the member that might relate to how an event registration is processed.) Of course for a brand new member the member record may not be in the matview yet, this throws an error.
So I tried changing that trigger function to look at the simulated table that queries the FDW. The trigger function was hanging the database, possibly due to the 4-5 second lag time to query the remote MySQL server or possibly due to lockouts. I have revered back to checking the materialized view, but this means that some transactions are not being fully processed.
Is it not recommended to use a FDW table in a trigger function?
--
Mike Nolan
On Mon, 2022-04-04 at 09:18 -0500, Michael Nolan wrote: > Is it not recommended to use a FDW table in a trigger function? Yes, I would say that is not recommended. While a lag of a few seconds, like you describe, should not be the normal case (you should investigate that), it is hard to exclude it in the face of network failures (you could end up waiting for the keepalive timeout of 2 hours). So the trigger can potentially run a long time, which makes your transaction long. A long transaction can block other sessions or autovacuum progress. The rule of thumb is that you should have nothing inside a database transaction that *could* take a long time (even if it is normally fast). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Thanks for confirming my suspicions, I'm working on a plan B to deal with this as best I can.
--
Mike Nolan
On Mon, Apr 4, 2022 at 9:23 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2022-04-04 at 09:18 -0500, Michael Nolan wrote:
> Is it not recommended to use a FDW table in a trigger function?
Yes, I would say that is not recommended. While a lag of a few seconds,
like you describe, should not be the normal case (you should investigate that),
it is hard to exclude it in the face of network failures (you could end up
waiting for the keepalive timeout of 2 hours).
So the trigger can potentially run a long time, which makes your transaction
long. A long transaction can block other sessions or autovacuum progress.
The rule of thumb is that you should have nothing inside a database transaction
that *could* take a long time (even if it is normally fast).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com