You mean this one:
CREATE MATERIALIZED VIEW public.memmast AS
SELECT memmast_simulant.civicrm_contact_id,
memmast_simulant.memid,
....
FROM public.memmast_simulant
WITH NO DATA;
And here's the foreign table:
CREATE FOREIGN TABLE public.memmast_simulant (
civicrm_contact_id integer,
...
SERVER mysql_civicrm
OPTIONS (
dbname 'skvare8_uscf_civicrm',
table_name 'memmast_simulant'
);
My understanding is that this view on mysql pulls fields from a lot of
different mysql tables because that's how CIVI-CRM organizes data.
I can try running that with explain, but my understanding is that foreign
tables can't be analyzed.
Mike Nolan
On Thu, Feb 26, 2026 at 6:01 PM Ron Johnson <[email protected]> wrote:
> There has to be a view definition stored *somewhere* in the PG database
> catalog. Find it and run the SELECT statement.
>
> That'll at least narrow down the problem (as well as letting you run
> EXPLAIN on it).
>
> On Thu, Feb 26, 2026 at 6:48 PM Michael Nolan <[email protected]> wrote:
>
>> I can run select statements on the mysql server from the azure server,
>> but the command we're using to refresh the matview is:
>> REFRESH MATERIALIZED VIEW memmast_ratings;
>>
>> Mike Nolan
>>
>> On Thu, Feb 26, 2026 at 5:45 PM Ron Johnson <[email protected]>
>> wrote:
>>
>>> Mike,
>>>
>>> 1. You're using mysql_fdw to connect?
>>>
>>> 2. What happens when you just run the raw SELECT statement (redirecting
>>> stdout to /dev/null, since we only need timings and error messages) from
>>> psql?
>>>
>>>
>>> On Thu, Feb 26, 2026 at 6:37 PM Michael Nolan <[email protected]> wrote:
>>>
>>>> My understanding is that the mysql server is at a Linode facility in
>>>> PA, the current production postgres server is in Asheville NC, and the
>>>> intended new server is a Azure server in the eastern US, not sure exactly
>>>> where.
>>>>
>>>> The two small matviews refresh, the two bigger ones fail, so it seems
>>>> size-related, which is why I was wondering if the settings might make a
>>>> difference because this server isn't fully production-scale yet. But
>>>> weve transferred 175 GB files to it in about 6 hours so I think the net
>>>> connection itself is probably not the issue.
>>>>
>>>> Mike Nolan
>>>>
>>>> On Thu, Feb 26, 2026 at 4:11 PM Adrian Klaver <
>>>> [email protected]> wrote:
>>>>
>>>>> On 2/26/26 1:59 PM, Michael Nolan wrote:
>>>>> > We have a connection from a PostgreSQL server to a MySQL server
>>>>> which is
>>>>> > used to update a materialized view on the PostgreSQL server from
>>>>> tables
>>>>> > on the MySQL server (running CIVI-CRM, which may not be relevant.)
>>>>> >
>>>>> > We are trying to move the PostgreSQL server to a new cloud server.
>>>>> >
>>>>> > On the current production system, all the materialized views work,
>>>>> but
>>>>> > the biggest of them can take about an hour.
>>>>> >
>>>>> > On the new system, the smallest of the materialized views works, but
>>>>> the
>>>>> > larger ones all seem to time out.
>>>>>
>>>>> "... move the PostgreSQL server to a new cloud server", where is that
>>>>> relative to the MySQL server compared to old Postgres server?
>>>>>
>>>>> >
>>>>> > Could this be some kind of setting on PostgreSQL, like a memory or
>>>>> > buffer issue?
>>>>> >
>>>>> > Mike Nolan
>>>>> > [email protected] <mailto:[email protected]>
>>>>>
>>>>>
>>>>> --
>>>>> Adrian Klaver
>>>>> [email protected]
>>>>>
>>>>
>>>
>>> --
>>> Death to <Redacted>, and butter sauce.
>>> Don't boil me, I'm still alive.
>>> <Redacted> lobster!
>>>
>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
