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
nolan(at)tssi(dot)com