public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Issue with refreshing materialized view from another system
Date: Thu, 26 Feb 2026 19:13:18 -0500
Message-ID: <CANzqJaBBOtYW+gOd21YWF2tn8Jd+3RYeZ49rdV45fnbkdV_foQ@mail.gmail.com> (raw)
In-Reply-To: <CAOzAqu+6g9+LScXC4d2qRm=qSMZswd0ZuhXtO-Qmp1RktipHjg@mail.gmail.com>
References: <CAOzAquKm3DzMA3bdW5obh+ZWjuFJ=y9n4Wg7d=qFfkOXf0tHoA@mail.gmail.com>
<[email protected]>
<CAOzAquLVSry4UbiVJDG=RtoVNabiMbXZ=cmCOKa=Foa_sbhauw@mail.gmail.com>
<CANzqJaBOOxsH4VE5Hk=si0_Drz1chRKxuebBQru9kWkbBi0E0w@mail.gmail.com>
<CAOzAquK-eUGYRRz1s9djD+jC-WDdnOZ6EyKAxoD_LkrcrYF+1A@mail.gmail.com>
<CANzqJaB6o2bWhtYRv7Ar+t1i--Xx8HTwpdoGhwQ756_vDz3w-w@mail.gmail.com>
<CAOzAqu+6g9+LScXC4d2qRm=qSMZswd0ZuhXtO-Qmp1RktipHjg@mail.gmail.com>
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 <[email protected]> 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 <[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!
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Issue with refreshing materialized view from another system
In-Reply-To: <CANzqJaBBOtYW+gOd21YWF2tn8Jd+3RYeZ49rdV45fnbkdV_foQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox