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 <[email protected]>
Date: Thursday, August 24, 2023 at 3:02 PM
To: Hellen Jiang <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Materialized view refreshing problem
Hellen Jiang <[email protected]> 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
