Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vvlIf-000dwT-0A for pgsql-general@arkaria.postgresql.org; Fri, 27 Feb 2026 00:01:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vvlId-00GWHK-2n for pgsql-general@arkaria.postgresql.org; Fri, 27 Feb 2026 00:01:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vvlId-00GWH1-1H for pgsql-general@lists.postgresql.org; Fri, 27 Feb 2026 00:01:43 +0000 Received: from mail-pf1-x42c.google.com ([2607:f8b0:4864:20::42c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vvlIa-00000001O5d-1flW for pgsql-general@lists.postgresql.org; Fri, 27 Feb 2026 00:01:42 +0000 Received: by mail-pf1-x42c.google.com with SMTP id d2e1a72fcca58-824b05d2786so1253073b3a.2 for ; Thu, 26 Feb 2026 16:01:41 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772150500; cv=none; d=google.com; s=arc-20240605; b=Yo43vXQ13/M8mz4Ml/jp27XE6UFDbvc5Xf2A3+vPavxn19jcKmzcgK79dw3QbQImp0 Cm01IDsmVadcw+Fp30aHk8cIcbi9B0pH07ZfaJd8ooDqkEaUpTcgjtFAwWgkG3Fy+74A rndfdceVn9FUdBJqIhx4rAL3A8AvBoFKMYvB7HgLkU+rQSelzu44/+BJeJ5Cud8rjiLN c7VFpDsf41mUw0Jf7NNy5Hbg081RICqjQNOIzTc/FrnFTAchimxppF61Z0rX20xFjsV4 qKsWIedMTPbZ8hfhaV3i6KOp7l6s4Qs/L3mjgdsZ/WrIPQicFbdOBMllCxJMgpRu8uus SNWA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=Sho2Ie/5CbI1cg1u15UKM3Sa6xJV6kQlLpdtOcOIDdU=; fh=2DoGC78b0mi8uZ4UY+9ocPF86GSVzeaJWqou/x907js=; b=YnoeLI35HVn9Jej6Le3nUoeXdJO8mGZmhPRoWd9X2kzD90vmqcSfpwGt+kT2IHHhxM UDsVVPi0YCcMcp8xqDY+eO1SAZLGXtjqTbWD2wLYczcLDSt9L5eAzZvrO+v0HUfLEeNL dqQoXMoEjxcfdr5zt0bsm10EIQv5UcagXzeC2K7CejFg0U5rW7MVqqcCykd/FsflQLkj LuMXcrV16A75NNp9REsznR7u21YsUmgaXiTw+ulxTHEbI5J3ylxsFmJLoM4S+GNJ++yF 675fYB0Lgq9fsPAlnNMuxRcs+MJKnGZ3ezKr3Nir9JNVesJDdYuLJ1VoROXzdi+rK5+S S9LA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1772150500; x=1772755300; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Sho2Ie/5CbI1cg1u15UKM3Sa6xJV6kQlLpdtOcOIDdU=; b=a1GfldDyJFwxMOF5IrEjOMXToUils/soQZretFWmA0LFjCw0fMv8vU5dcFP9oXd40a C5pxf9KBnrihd9o1NX4xDhvnnU7miJlNlhuYrZXm08wlT5D9g34qOxO0KnPMGzt5ndZ/ tZ9YKwSFgThW4qBYuOyaYCvSwbhbb7ajfLXfsH7plrDr6ESqsUwuq9UxN93ra0nqMXJR KLwsqHXzBzQg1zPQDVWlT9VePS82N2r0OXvzS8U0zCwVERm/gqnnSDIHbFI8r1CB9dW9 FAHj8l1StDx3FK14P31a+UouXHSR9NWhR3JsCx6bIo5W6AguaPvP+BY+IXfU2V457AZu MBpA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772150500; x=1772755300; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Sho2Ie/5CbI1cg1u15UKM3Sa6xJV6kQlLpdtOcOIDdU=; b=CVcW7uL4OLzVLoLncK7aU4auANfJ5T9qq4Mauh7t3hT7ckw4JIp5Wemi0kcxEQlK9W fv89+rAodW0N6AKq91Hh0m6SHeBaO0Q5UAwQ9C3AaJsRHzE+vJ3bauiYp9W5QvF+4GbY K1EMHj7Hsgpm3gxnSuMNIm6w0PWJQi6OvXZQGUv9yBAoBCL9T2hRIi5uGQZBqNHe7YNG J7t2royPnHdtjnBcY1NO/FFz0/nve8PNEpIA1zw4Ln7ItAZmZ0gdUMMW8MAWv6Y57xWW o2uYSWPE4w1S+uFbokjGSUij9mHU7+uBJ2EdW99u4EE0LpPtPWOiyQiiSZRdtA7q3hY7 i7xg== X-Gm-Message-State: AOJu0YyeQ2d4F80g4Ky9TLf8BZcbGAtBahKrjA9xp8YY2b04G5ZBPQAF n+/iUXdCQmLJV8x59096tLsb1qxYExMuCWBbxnHeOu/XbbiDtPwtFPFKneuk5ssiZvCxHGw5SWn Ml/pd0VvZpa4FuKKPwZvv/ilIzmUV0ANEbQ== X-Gm-Gg: ATEYQzx6yI7WBZJ4Ssq4HIw1M2WcLrLLqI/cBE1IdAaMTGTY/5hWp5Xd+1VIQPhQ/fV tJuwJm9//xKfW5esMiUC4+QQJ1Pj0ocvsQ9YGjeNfx89OCjPnxLShp5kAfdXvMWMfog2RUkOiWD m0NTfvompOPF8HVfG+gnlItmgiEyMbdFQ6NtFr4I6KtpN4vupcRTc7Sq5aSs96RjMJ6CSyVMr8H RZ8KYiFJGoHURV8NaV2OOWnhytGDPGYsZvDI5oA0EhPHaan9wANpdFALWEx7sgALewvR7vNRJHD Hts8vMwU X-Received: by 2002:a05:6a21:6f0d:b0:38b:eadd:449a with SMTP id adf61e73a8af0-395c3ae8af3mr847421637.38.1772150499758; Thu, 26 Feb 2026 16:01:39 -0800 (PST) MIME-Version: 1.0 References: <7f97d4a8-1f3f-47b1-8c42-5e0b8c46d467@aklaver.com> In-Reply-To: From: Ron Johnson Date: Thu, 26 Feb 2026 19:01:27 -0500 X-Gm-Features: AaiRm51vHzbFfak7G03yqgujYnco7o8KsUzpu2PRR837aFoxCgxLh3EBr5AUEzw Message-ID: Subject: Re: Issue with refreshing materialized view from another system To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000006d553064bc2f19f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000006d553064bc2f19f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Michael Nolan wr= ote: > I can run select statements on the mysql server from the azure server, bu= t > 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=E2=80=AFPM Ron Johnson > 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=E2=80=AFPM Michael Nolan = 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 inte= nded >>> 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=E2=80=AFPM Adrian Klaver >>> wrote: >>> >>>> On 2/26/26 1:59 PM, Michael Nolan wrote: >>>> > We have a connection from a PostgreSQL server to a MySQL server whic= h >>>> 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@gmail.com >>>> >>>> >>>> -- >>>> Adrian Klaver >>>> adrian.klaver@aklaver.com >>>> >>> >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000006d553064bc2f19f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
There has to be a view definition stored somewhere= =C2=A0in the PG database catalog.=C2=A0 Find it and run the SELECT stat= ement.

That'll at least narrow down the proble= m (as well as letting you run EXPLAIN on it).

On Thu, = Feb 26, 2026 at 6:48=E2=80=AFPM Michael Nolan <htfoot@gmail.com> wrote:
I can run select statement= s on the mysql server from the azure server, but the command we're usin= g to refresh=C2=A0the matview is:
REFRESH MATERIALIZED VIEW memma= st_ratings;

Mike Nolan

On Thu, Feb 26, 2026 at 5:45= =E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
Mike,
=
1. You're using mysql_fdw to connect?

2. What happens when you just run the raw SELECT statement (redirect= ing stdout to /dev/null, since we only need timings and error messages) fro= m psql?


On Thu, Feb 26,= 2026 at 6:37=E2=80=AFPM Michael Nolan <htfoot@gmail.com> wrote:
My understanding is that the mysql server is at a Linode facility i= n PA, the current production postgres server is in Asheville NC, and the in= tended new server is a Azure server in the eastern US, not sure exactly whe= re.

The two small matviews refresh, the two bigger= ones fail, so it seems size-related, which is why I was wondering if the s= ettings might make a difference because this server isn't fully product= ion-scale yet.=C2=A0 But weve=C2=A0transferred 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=E2=80=AF= PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 2/26/26 1:59 PM, Michael Nolan wrote:<= br> > 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 table= s
> on the MySQL server (running CIVI-CRM, which may not be relevant.)
>
> We are trying to move the PostgreSQL server=C2=A0to 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 t= he
> 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@gmail.com= <mailto:htfoo= t@gmail.com>


--
Adrian Klaver
adrian.klave= r@aklaver.com


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


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--00000000000006d553064bc2f19f--