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