Обсуждение: [MASSMAIL]Create Materialized View from postgresql_fdw hang
Hi All,
Sr. PostgreSQL Database Administrator,
Mobile: 7755929449.
I am trying to create a materialized view with postgresql_fdw, and it hangs for hours. In pg_stat_activity I am seeing wait_event_type and wait_event Extension.
In the source server, the state is "idle in transaction" and the query is "FETCH 100 FROM c1". Any suggestions on how to speed this up?
Thanks & Regards,
Naveen Kumar .M,On Thu, 2024-04-04 at 15:07 +0300, Naveen Kumar wrote: > I am trying to create a materialized view with postgresql_fdw, and it hangs for hours. > In pg_stat_activity I am seeing wait_event_type and wait_event Extension. Do you *always* see that wait event or only occasionally? Occasional occurrences could be normal; in that case, it is probably the query that takes a long time. Yours, Laurenz Albe
Hi Laurenz,
Thanks for responding. Most of the time I am seeing wait event, same view if I create directly in source DB, it’s quick.
Sr. PostgreSQL Database Administrator,
Mobile: 7755929449.
Thanks & Regards,
Naveen Kumar .M,On Thu, 4 Apr 2024 at 8:52 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-04-04 at 15:07 +0300, Naveen Kumar wrote:
> I am trying to create a materialized view with postgresql_fdw, and it hangs for hours.
> In pg_stat_activity I am seeing wait_event_type and wait_event Extension.
Do you *always* see that wait event or only occasionally?
Occasional occurrences could be normal; in that case, it is
probably the query that takes a long time.
Yours,
Laurenz Albe
You can probably check the size of the materialized view since they occupy physical space on disk. Periodic checking should show increase in size
Sent via the Samsung Galaxy S22+ 5G, an AT&T 5G smartphone
-------- Original message --------
From: Naveen Kumar <naveenchowdaryon@gmail.com>
Date: 4/4/24 3:08 PM (GMT-05:00)
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Create Materialized View from postgresql_fdw hang
Hi Laurenz,
Thanks for responding. Most of the time I am seeing wait event, same view if I create directly in source DB, it’s quick.
Sr. PostgreSQL Database Administrator,
Mobile: 7755929449.
Thanks & Regards,
Naveen Kumar .M,On Thu, 4 Apr 2024 at 8:52 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Thu, 2024-04-04 at 15:07 +0300, Naveen Kumar wrote:
> I am trying to create a materialized view with postgresql_fdw, and it hangs for hours.
> In pg_stat_activity I am seeing wait_event_type and wait_event Extension.
Do you *always* see that wait event or only occasionally?
Occasional occurrences could be normal; in that case, it is
probably the query that takes a long time.
Yours,
Laurenz Albe
Thanks.
On Fri, 5 Apr 2024 at 12:53 AM, jwiencek3 <jwiencek3@comcast.net> wrote:
You can probably check the size of the materialized view since they occupy physical space on disk. Periodic checking should show increase in sizeSent via the Samsung Galaxy S22+ 5G, an AT&T 5G smartphone-------- Original message --------From: Naveen Kumar <naveenchowdaryon@gmail.com>Date: 4/4/24 3:08 PM (GMT-05:00)To: Laurenz Albe <laurenz.albe@cybertec.at>Subject: Re: Create Materialized View from postgresql_fdw hangHi Laurenz,Thanks for responding. Most of the time I am seeing wait event, same view if I create directly in source DB, it’s quick.Mobile: 7755929449.Sr. PostgreSQL Database Administrator,Thanks & Regards,Naveen Kumar .M,On Thu, 4 Apr 2024 at 8:52 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:On Thu, 2024-04-04 at 15:07 +0300, Naveen Kumar wrote:
> I am trying to create a materialized view with postgresql_fdw, and it hangs for hours.
> In pg_stat_activity I am seeing wait_event_type and wait_event Extension.
Do you *always* see that wait event or only occasionally?
Occasional occurrences could be normal; in that case, it is
probably the query that takes a long time.
Yours,
Laurenz Albe