pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate Materialized View from postgresql_fdw hang
5+ messages / 3 participants
[nested] [flat]
* Create Materialized View from postgresql_fdw hang
@ 2024-04-04 12:07 Naveen Kumar <naveenchowdaryon@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Naveen Kumar @ 2024-04-04 12:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
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,
Sr. PostgreSQL Database Administrator,
Mobile: 7755929449.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Materialized View from postgresql_fdw hang
@ 2024-04-04 17:52 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Naveen Kumar <naveenchowdaryon@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-04-04 17:52 UTC (permalink / raw)
To: Naveen Kumar <naveenchowdaryon@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Materialized View from postgresql_fdw hang
@ 2024-04-04 19:08 Naveen Kumar <naveenchowdaryon@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Naveen Kumar @ 2024-04-04 19:08 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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.
Thanks & Regards,
Naveen Kumar .M,
Sr. PostgreSQL Database Administrator,
Mobile: 7755929449.
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Materialized View from postgresql_fdw hang
@ 2024-04-04 21:53 jwiencek3 <jwiencek3@comcast.net>
parent: Naveen Kumar <naveenchowdaryon@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: jwiencek3 @ 2024-04-04 21:53 UTC (permalink / raw)
To: Naveen Kumar <naveenchowdaryon@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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> 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.Thanks & Regards,Naveen Kumar .M,Sr. PostgreSQL Database Administrator,Mobile: 7755929449.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Create Materialized View from postgresql_fdw hang
@ 2024-04-05 19:43 Naveen Kumar <naveenchowdaryon@gmail.com>
parent: jwiencek3 <jwiencek3@comcast.net>
0 siblings, 0 replies; 5+ messages in thread
From: Naveen Kumar @ 2024-04-05 19:43 UTC (permalink / raw)
To: jwiencek3 <jwiencek3@comcast.net>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
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 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.
>
>
> Thanks & Regards,
> Naveen Kumar .M,
> Sr. PostgreSQL Database Administrator,
> Mobile: 7755929449.
>
>
>
> 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
>>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-04-05 19:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-04 12:07 Create Materialized View from postgresql_fdw hang Naveen Kumar <naveenchowdaryon@gmail.com>
2024-04-04 17:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-04 19:08 ` Naveen Kumar <naveenchowdaryon@gmail.com>
2024-04-04 21:53 ` jwiencek3 <jwiencek3@comcast.net>
2024-04-05 19:43 ` Naveen Kumar <naveenchowdaryon@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox