I have a materialized view "matview" in Postgres DB. For performace reasons it is a materialized view and not a simple SQL view. Source SQL of this "matview" joins together fields from "local_source_table" in the local database (Postgres) and fields from a "foreign_table" (using tds_fdw) from a foreign remote database (MSSQL). The "foreign_table" in my local database actually reads a remote view in the remote MSSQL database. The view in the remote database reads data from its own source tables, but I don´t know much details about that. Except of this remote view, I don´t have access or even enough information about the remote database. All the data I need is in the remote view and therefore also in my "foreign_table" and subsequently in the "matview". I would like to keep the "matview" view up-to-date, so I want to REFRESH MATERIALIZED VIEW automatically after changes in the source tables happen. If the changes happen in the "local_source_table", I have a trigger on that table, that executes the REFRESH MATERIALIZED VIEW of the "matview". But if the changes happen in the remote database, such a trigger will not work in my local database, because the foreign table doesn´t know about the updates or inserts or deletes in the foreign database.
Is there a way how to detect these updates in the "foreign_table" from within my local database, so I can then refresh the "matview" based on this? I would like to avoid scheduled refreshing as I need the data to be in sync almost in real-time.
I tried to write a trigger on the "foreign_table" to listen for updates (insert, update, delete) but the "foreign_table" seems to be more like a view and so it doesn´t have access to these transactions, which happen in the remote database.