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:01:27 -0500
Message-ID: <CANzqJaB6o2bWhtYRv7Ar+t1i--Xx8HTwpdoGhwQ756_vDz3w-w@mail.gmail.com> (raw)
In-Reply-To: <CAOzAquK-eUGYRRz1s9djD+jC-WDdnOZ6EyKAxoD_LkrcrYF+1A@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>
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!
view thread (2+ messages)
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: <CANzqJaB6o2bWhtYRv7Ar+t1i--Xx8HTwpdoGhwQ756_vDz3w-w@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