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 1vvlU8-000pHH-2T for pgsql-general@arkaria.postgresql.org; Fri, 27 Feb 2026 00:13:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vvlU7-00Gdwo-2I for pgsql-general@arkaria.postgresql.org; Fri, 27 Feb 2026 00:13:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vvlU7-00GdwY-0z for pgsql-general@lists.postgresql.org; Fri, 27 Feb 2026 00:13:35 +0000 Received: from mail-pg1-x531.google.com ([2607:f8b0:4864:20::531]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vvlU4-00000001TlA-1uPR for pgsql-general@lists.postgresql.org; Fri, 27 Feb 2026 00:13:35 +0000 Received: by mail-pg1-x531.google.com with SMTP id 41be03b00d2f7-c648bc907ebso858770a12.3 for ; Thu, 26 Feb 2026 16:13:33 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772151210; cv=none; d=google.com; s=arc-20240605; b=jVJW4YpibB6fDUOuVz2TDytIB1bMEkOJgh22k+dhg+n76QYfK1/AwSKaJmGiVnlYVJ 6m936keVAALQVYR0zcmlXAWLzD8SZeFKBhp2UbdyNYYpw80t84NKiIm0aGoeZ4kcfhkT MzeyYGe5l6jw2vFKBeFACLT5TufCRwkCkfTUQ+9uVzz9i6qGu3t9aaxCADWxBCNousRm 7PkGYrqztbbvxMUjLJaKYEZgjit3zCH8zUW1JU2Eja+MjYCEhP8OYYzy4Fhp9Du8vz9d UliM1ggk780m+zWP8FCoLAP1qF9QvtRVoKaq3RZPU/Pa7R/k/yUFDH/qxtnEgwvIVjBY DeFQ== 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=15pvt0CKv1wPTjdBtlOxic0WBOkvwUcPH5LH+jG55L8=; fh=2DoGC78b0mi8uZ4UY+9ocPF86GSVzeaJWqou/x907js=; b=fgeZdrb0uieX/YVwZxaF7uGY223Obum6CUVdC2l5Egkx99JP1GMpGdpRJvdhyTrcyv 3b7D13qMiOarb5XpYGFmU+Cyy+dwiMsslpXJ0sM6Pn6QhViaYxXonSWVZJ6W9b+shJJv agDR3DXkiclUGDFIFq12vMfhId+09bn8kc1M++ziwzod3ZA11pfXs70tN2u4lqUqDAV7 Wkw2so7o2vcGR7lIclHAX3kZP0tH4Xd5s98AJQYlJMkMx75MqsuBodCzWoFh7p+Z3YEG h27gLlnDGXMZ044Pw3+41cV2YbtAIsmA4Ky8d+XjVGxBYzcHBGQUvXZDNYYa0BqTDljC GnzA==; 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=1772151210; x=1772756010; 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=15pvt0CKv1wPTjdBtlOxic0WBOkvwUcPH5LH+jG55L8=; b=aCZt9s9CrDWH7WxPK819/TUqJKyktvDhwT9toVP2TUKvleYAIAeEOXTZJrI00uwHDG 06Pc5ODOuSV+t9l6KXg5mNFuQex23+o2iqh0QiCXpEndQVls7mZxdI3oEvVROPHH+aXB KOYhiN67lARJQfdNDiXwKlpGYOGQQ1yYH3DdC/FoS+b0RQlL1LEUJxdjA/dt6DjHX3Ns o3v0Zf1IWWNkprRhbcEe7bLXNZUuourtT5YAlbVZVEN7ULFih17ys6QwLC7Pycg8k6hd b74B1/mR75azagAQvdfoeqat5+NLy2CAlpe1v+kPW8Px+ACzsAj1g/SgeTR1ElpkVDG8 3rFw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772151210; x=1772756010; 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=15pvt0CKv1wPTjdBtlOxic0WBOkvwUcPH5LH+jG55L8=; b=GNj/AwvoPqi+GOfzURCS0q6EBOB8/282UfmrV7AHxBcbxkFm7886ASSZxLV+0tddAx btWiPdl0xaU2j9X+gaP7tRUbuYGH6YJT5ZHeCzXZ8+Bh3LLRNV0RCHf+KPdFPDlquyhX K3IQ7kFeSbEdabPoGBpODOfyMmhUW4tkKmHLReRFWkw+IHDY8yOKQOtsCupGDUJklnRb Lje0JrymnppXD+G9nm858cBSQzr1riUzJzjAsTdGpzw6aA9ocr2YJjPVsPvz25wVbp3m kd9wChQ8V7AzShGXb5gNuFUXpbtg60xJPho65LxTUbbCCq515vhmbMQCT4wJWW4DARUL PM8w== X-Gm-Message-State: AOJu0YyAgJbtap5eZkHX00LZIvRuC24tpE/6xIVCOIOTMjxDtRNzwqOv 8DHoogfVsZJGtD5S4CsCJJfwfpt4RiZrOWVijSDQLWe9HnBVwC69JBR+6XHoplGVwL6I1DILFXW ohxfK6qMqBtp+gS1LpOkJaucDA2LKP1LNRw== X-Gm-Gg: ATEYQzx4nUP42ksgDXXCXivEYuyb3jmRoc4w6/YBCskQRcdwjNL5JWHDSf/sKfnv/Sp KzwU2eGX1ZbjbQ2dCX8JmcwqFDkqaCwGdlkNVHzU/EVYmOnLU5f7tHt6kUX03CqwGBzGt2AYJIC hg5Oj2BTKFRZfMzbtwMNWy8IBUnR0iokmII/JIbIi9hZG6RMbofQ1mxaadswTDI69utXbMqQaop 89qos8glMsRRfZbov45PIVSdF1Uu6pWAuyBV7w39clXlY88OL2KSEMYGwnL8B0rMwSUa6XEjtNm d3PCN5c1 X-Received: by 2002:a05:6300:2208:b0:390:ca32:da2c with SMTP id adf61e73a8af0-395c3a6f114mr962918637.24.1772151210161; Thu, 26 Feb 2026 16:13:30 -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:13:18 -0500 X-Gm-Features: AaiRm51bYzobMhl5rjO6LxiliOZvsOZDwepTcOieMZCTACPogE-iNDKNsNaNw14 Message-ID: Subject: Re: Issue with refreshing materialized view from another system To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000005eb9e6064bc31b69" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005eb9e6064bc31b69 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM Michael Nolan wr= ote: > 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=E2=80=AFPM Ron Johnson > 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=E2=80=AFPM Michael Nolan = 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=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 (redirectin= g >>>> stdout to /dev/null, since we only need timings and error messages) fr= om >>>> 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 th= e >>>>> intended new server is a Azure server in the eastern US, not sure exa= ctly >>>>> 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 n= et >>>>> connection itself is probably not the issue. >>>>> >>>>> Mike Nolan >>>>> >>>>> On Thu, Feb 26, 2026 at 4:11=E2=80=AFPM Adrian Klaver < >>>>> adrian.klaver@aklaver.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 tha= t >>>>>> 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! >>>> >>> >> >> -- >> 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! --0000000000005eb9e6064bc31b69 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You can try and run the query, to see how long it = takes.

Do that both from psql and mysql.=C2=A0= That'll tell you something=C2=A0about where the problem is.
On Thu, Feb 26, 2026 at 7:09=E2=80=AFPM Michael Nolan <= ;htfoot@gmail.com> wrote:
= You mean this one:

CREATE MATERIALIZED VIEW public= .memmast AS
=C2=A0SELECT memmast_simulant.civicrm_contact_id,
=C2=A0 = =C2=A0 memmast_simulant.memid,
....
=C2=A0 =C2=A0FROM public.m= emmast_simulant
=C2=A0 WITH NO DATA;

And here&#= 39;s the foreign table:

CREATE FOREIGN TABLE publi= c.memmast_simulant (
=C2=A0 =C2=A0 civicrm_contact_id integer,
...
SERVER mysql_civicrm
OPTIONS (
=C2=A0 =C2=A0 dbname 'skva= re8_uscf_civicrm',
=C2=A0 =C2=A0 table_name 'memmast_simulant= 9;
);


My understanding is that this vie= w on mysql pulls fields from a lot of different mysql tables because that&#= 39;s how CIVI-CRM organizes data.

I can try runnin= g that with explain, but my understanding is that foreign tables can't = be analyzed.

Mike Nolan

=
On Thu= , Feb 26, 2026 at 6:01=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:<= br>
There has to be a view definition stored somewhere=C2=A0in the= PG database catalog.=C2=A0 Find it and run the SELECT statement.

That'll at least narrow down the problem (as well as le= tting you run EXPLAIN on it).

On Thu, Feb 26, 2026 at 6:48=E2=80=AFPM Micha= el Nolan <htfoot@g= mail.com> wrote:
I can run select statements on the mysql serv= er from the azure server, but the command we're using to refresh=C2=A0t= he matview is:
REFRESH MATERIALIZED VIEW memmast_ratings;

=
Mike Nolan

On Thu, Feb 26, 2026 at 5:45=E2=80=AFPM Ron John= son <ronljo= hnsonjr@gmail.com> wrote:
Mike,

1. Yo= u're using mysql_fdw to connect?

2. What happe= ns when you just run the raw SELECT statement (redirecting stdout to /dev/n= ull, since we only need timings and error messages) from psql?

On Thu, Feb 26, 2026 at 6:37=E2=80= =AFPM Michael Nolan <htfoot@gmail.com> wrote:
My understa= nding is that the mysql server is at a Linode facility in PA, the current p= roduction postgres server is in Asheville NC, and the intended new server i= s a Azure server in the eastern US, not sure exactly where.

<= /div>
The two small matviews refresh, the two bigger ones fail, so it s= eems size-related, which is why I was wondering if the settings might make = a difference because this server isn't fully production-scale yet.=C2= =A0 But weve=C2=A0transferred 175 GB files to it in about 6 hours so I thin= k the net connection itself is probably not the issue.

=
Mike Nolan

On Thu, Feb 26, 2026 at 4:11=E2=80=AFPM Adrian Klaver = <adrian.k= laver@aklaver.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 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!


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