Re: Materialized view refreshing problem

[Hellen Jiang <hjiang@xxxxxxxxxxxxxxxxxxxxx>]

When the materialized view was refreshing concurrently, I saw the pg_lock like this: there was no response within 30 seconds when the api calls this query, and we got api time out.

sasanalytics=> select relation::regclass, * from pg_locks where not granted;

                     relation                      | locktype | database |  relation  | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode      | granted | fastpath 

---------------------------------------------------+----------+----------+------------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------+----------

 mvw_cbsd_status_grants_lessinfo_active_softmarker | relation |    16401 | 1886512426 |      |       |            |               |         |       |          | 236/858            | 17332 | ExclusiveLock | f       | f

(1 row)

 

When the materialized view was refreshing normally( without concurrently), I saw the following pg_locks: there was no response within 30 seconds when the api calls this query, and we got api time out.

sasanalytics=> select relation::regclass, * from pg_locks where not granted;

                     relation                      | locktype | database |  relation  | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted | fastpath 

---------------------------------------------------+----------+----------+------------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------

 mvw_cbsd_status_grants_lessinfo_active_softmarker | relation |    16401 | 1886512426 |      |       |            |               |         |       |          | 245/1163           | 15932 | AccessExclusiveLock | f       | f

(1 row)

 

 

From: Tom Lane <tgl@xxxxxxxxxxxxx>
Date: Thursday, August 24, 2023 at 3:02 PM
To: Hellen Jiang <hjiang@xxxxxxxxxxxxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Materialized view refreshing problem

Hellen Jiang <hjiang@xxxxxxxxxxxxxxxxxxxxx> writes:
> But from time and time, we found that it took forever to refresh materialized view( normally or concurrently), and there is no response to query from this materialized view.

That sounds like a locking problem.  Have you looked into pg_locks
while this is happening, to see what may be waiting on what?

                        regards, tom lane