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 1vvlQ4-000lIj-0y for pgsql-general@arkaria.postgresql.org; Fri, 27 Feb 2026 00:09:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vvlQ1-00GaWl-1y for pgsql-general@arkaria.postgresql.org; Fri, 27 Feb 2026 00:09:21 +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 1vvlQ1-00GaWM-0o for pgsql-general@lists.postgresql.org; Fri, 27 Feb 2026 00:09:21 +0000 Received: from mail-lj1-x236.google.com ([2a00:1450:4864:20::236]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vvlPy-00000001TjX-1K48 for pgsql-general@lists.postgresql.org; Fri, 27 Feb 2026 00:09:20 +0000 Received: by mail-lj1-x236.google.com with SMTP id 38308e7fff4ca-3870cbd6c40so12762091fa.0 for ; Thu, 26 Feb 2026 16:09:18 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1772150958; cv=none; d=google.com; s=arc-20240605; b=ekvdgewlqtJhXIW0fkHB7J6nzj6519vX0GHbUjkJMlP8zlZbwjzRO050YclXBeL93h wVCAljSk9ezXmgwF1lgs5icpwZioMa71Czr7LjNoQVzTZc/sUgX/haJtfgZ/3GnApjMQ fI9a2QBbRzVg8Mz0Eol4VNaoSGRTepsdIxcGGs0DXFJRA1uUNyNz/2mrydg5YZyzekhW V9BlvQ6OItZ+ko1z00RJEX7XulaGHqLfBweyQF8n40yeb6iOo58FESQyCrfCBcsoTHHi ey+BIEAxpOfsPBAuD4+bfB4I9nOZ2Z4mKi75NCjlGlawcoVQMbxDz2OP+OhfC3/WYepW hwVA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=zTX3hhJIyeT/8jmzNtZzgUgzpIwaDf3r9OJgXb9XScI=; fh=M5d6ZpcaQVHTXkkOH6MmskvUc9TU9yzFINFTBAvu35o=; b=iXP/ej5S9ZIElyuA8O/I/i8RjMnYS20zKDfORPRZ9b3Z7qLx6OKegYNXLIlcvDy6bn 3pLXGu8VEylRc1mLrcpB/COo0oDJq7ZPoF9MsMvjWNB+czYprPMjW4/yjcQIo4LTmaJj mNiTaWAD+zZ/NotoHZMsQJwiMpXGHchXCk0gZtYS+O532HPwchnmOnheCPbwXG3cymIU fkx41XMr5pzqJeOLqYCDqrku7ufwRJStjCAH0KLNM9Mqnyu8vv4CUmwiOGfUaBSpj3sh 5O5bdz+rty8EqKUMdqj8uEX86d/49y3ZKUduLLoAYAx/fILd6MKiwzu3+YrHDtVtMOO0 72/g==; 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=1772150958; x=1772755758; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zTX3hhJIyeT/8jmzNtZzgUgzpIwaDf3r9OJgXb9XScI=; b=ClBMEjLjRcbiqZNO1BlrD0opJavKG9s/3o6G7+j89rJvpwiZR2eoZxhjX2BUZxxjZy UzKUyJB1vrwGjEwXfk9VUOs3ecI+WQY3X6mGQ3fqLIHx2DYA5oSMdwLMOBrTLRtyifYd XqJ8mh/YJGEhKN/8lmw59IvC6Mi6zF1bQr4PH4owSD4asZ5gQKgz/PWNjo4962RJu6fc zP0r26zpmCirDtODOpLT4w1MIUqHiqtxdCXPnKsBLb/mQ2SdhlYQ0cEStBGAT6EGoITO dymtnYyULUCh4No9xIOjENbVoF4GtajF8+wj4KEXG7xESeVazwVGpjA3yKXHnr5qrMg7 vrng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1772150958; x=1772755758; h=cc: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=zTX3hhJIyeT/8jmzNtZzgUgzpIwaDf3r9OJgXb9XScI=; b=lBworMHJ4Z5oaNqjFlCPtccva2JFXQiXJZPpJHmCD3pPX7ro2ytPdA1DOuqZpfVTF1 1VhhFMWvTQKBbWn4/D7WIHmwo2cwdO74wZ7jl2LKNidzq73z+IV9OaSI8HMBqU8SqhBk 2gfX/XNvofbuioCdudv1UM7Zx3x1xAz5Nqbnzvyg6MYOIagHpQ3VoAX24YLmWqRsdmKL ke+Cf6FaLISO4vOlS+MKLMQfLv++kmHoc90nEIqlJe6l5rK/fzcACqUGZqBy7RKYGfDN cTReluX4vwh9Jyl7A/SGLwASNV0c7zqmfjQnqhZLXnGjku8CSuKFXhw3uBMr4N5iMQgl 4IXA== X-Gm-Message-State: AOJu0YwuCpcSdXn536SgDXV0q+RjLgrMbddYX7i6qA8onTI7nryhuJDi tc236ZXvLGFj3v5Q/S3fvv7Sob2R25eje6Lq4AJY6B90g7eGIcuY3Qvdw5CtgXqxq8AwljLG6MQ A+wZAI29a4lbGW/3zUQNxwGpi+XhRjQKA1/hC X-Gm-Gg: ATEYQzw6JlMpGDeL/zWpplbI51T+ZJCug3Y0ITxnV+iI3jRS9P2cRIX1eu0pcL5tO1x 98TiY8sfZ5TBciJD0uGqAPuQoFl/6REeOMojQL02v9BGc1DZDYoXhyi4cpEJ0upx6VrNaDS9WDb HtDJFXkSsRxLy/vopANWsfgeCAG04sSYM7xt/0ChVQn/jwPStfqIun265rDyigjFp4xr4hGHXtX UQw91zJMSL9tQpuVGuoZcIN6nhcur9m4h+49/BLOz8UZ95wfBsEgdQ4M+nQDV9b86T+sWeBbu7L HJe8VrLcPy7R4Rqzk0o9ZE51/KzaTJ9hlm1QWb048QHm4+HzU6qHrY70b1FtawSSfiQ= X-Received: by 2002:a05:651c:41d1:b0:386:eadd:9ddf with SMTP id 38308e7fff4ca-389ff130971mr4039321fa.12.1772150957337; Thu, 26 Feb 2026 16:09:17 -0800 (PST) MIME-Version: 1.0 References: <7f97d4a8-1f3f-47b1-8c42-5e0b8c46d467@aklaver.com> In-Reply-To: From: Michael Nolan Date: Thu, 26 Feb 2026 18:09:06 -0600 X-Gm-Features: AaiRm52cwRILfBFc9umRWchts8_uCxptKIFj1Jl1ovrmeEPCIn1_5yjafLjaZgI Message-ID: Subject: Re: Issue with refreshing materialized view from another system To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004cee3f064bc30cc5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004cee3f064bc30cc5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 (redirecting >>> 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 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 exac= tly >>>> 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 ne= t >>>> 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, bu= t >>>>> 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! >>> >> > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --0000000000004cee3f064bc30cc5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You mean this one:

CREATE MA= TERIALIZED VIEW public.memmast AS
=C2=A0SELECT memmast_simulant.civicrm_= contact_id,
=C2=A0 =C2=A0 memmast_simulant.memid,
....
=C2= =A0 =C2=A0FROM public.memmast_simulant
=C2=A0 WITH NO DATA;
And here's the foreign table:

CRE= ATE FOREIGN TABLE public.memmast_simulant (
=C2=A0 =C2=A0 civicrm_contac= t_id integer,
...
SERVER mysql_civicrm
OPTIONS (
=C2=A0 = =C2=A0 dbname 'skvare8_uscf_civicrm',
=C2=A0 =C2=A0 table_name &= #39;memmast_simulant'
);


My underst= anding is that this view on mysql pulls fields from a lot of different mysq= l tables because that's how CIVI-CRM organizes data.

I can try running that with explain, but my understanding is that fo= reign tables can't be analyzed.

Mike Nolan


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

That'll at least narrow do= wn the problem (as well as letting you run EXPLAIN on it).

On Thu, Feb 26, 2= 026 at 6:48=E2=80=AFPM Michael Nolan <htfoot@gmail.com> wrote:
I can run select = statements on the mysql server from the azure server, but the command we= 9;re using to refresh=C2=A0the matview is:
REFRESH MATERIALIZED V= IEW memmast_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 (= redirecting stdout to /dev/null, since we only need timings and error messa= ges) from 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 fa= cility in PA, the current production postgres server is in Asheville NC, an= d the intended new server is a Azure server in the eastern US, not sure exa= ctly where.

The two small matviews refresh, the tw= o 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.=C2=A0 But weve=C2=A0transferred 175 GB files to it i= n about 6 hours so I think the net connection itself is probably not the is= sue.

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 Nola= n 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!
--0000000000004cee3f064bc30cc5--