Re: Issue with refreshing materialized view from another system

You can try and run the query, *to see how long it takes*.

Do that both from psql and mysql. That'll tell you *something* about where
the problem is.

On Thu, Feb 26, 2026 at 7:09 PM Michael Nolan <htfoot(at)gmail(dot)com> wrote:

> 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 <ronljohnsonjr(at)gmail(dot)com>
> 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 <htfoot(at)gmail(dot)com> 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 <ronljohnsonjr(at)gmail(dot)com>
>>> 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 <htfoot(at)gmail(dot)com> 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 <
>>>>> adrian(dot)klaver(at)aklaver(dot)com> 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
>>>>>> > htfoot(at)gmail(dot)com <mailto:htfoot(at)gmail(dot)com>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Adrian Klaver
>>>>>> adrian(dot)klaver(at)aklaver(dot)com
>>>>>>
>>>>>
>>>>
>>>> --
>>>> 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!
>>
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!