Re: Adjust pg_stat_get_lock() prorows to match lock types

ZizhuanLiu X-MAN Fri, 05 Jun 2026 20:30:57 -0700

&gt;From: Chao Li <[email protected]&gt;
&gt;Date: 2026-05-15 16:34
&gt;To: Postgres hackers <[email protected]&gt;
&gt;Subject: Re: Adjust pg_stat_get_lock() prorows to match lock types
&gt;
&gt;
&gt;
&gt;&gt;&nbsp;On&nbsp;May&nbsp;4,&nbsp;2026,&nbsp;at&nbsp;10:23,&nbsp;Chao&nbsp;Li&nbsp;<[email protected]&gt;&nbsp;wrote:
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Hi,
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;I&nbsp;read&nbsp;the&nbsp;code&nbsp;of&nbsp;pg_stat_lock()&nbsp;and&nbsp;played&nbsp;a&nbsp;bit&nbsp;with&nbsp;it.&nbsp;I&nbsp;happened&nbsp;to&nbsp;notice&nbsp;one&nbsp;thing:&nbsp;the&nbsp;function&nbsp;always&nbsp;returns&nbsp;12&nbsp;rows,&nbsp;but&nbsp;the&nbsp;planner&nbsp;estimates&nbsp;10&nbsp;rows:
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;evantest=#&nbsp;EXPLAIN&nbsp;ANALYZE&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;pg_catalog.pg_stat_lock;
&gt;&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QUERY&nbsp;PLAN
&gt;&gt;&nbsp;-----------------------------------------------------------------------------------------------------------------------
&gt;&gt;&nbsp;Function&nbsp;Scan&nbsp;on&nbsp;pg_stat_get_lock&nbsp;l&nbsp;&nbsp;(cost=0.00..0.10&nbsp;rows=10&nbsp;width=64)&nbsp;(actual&nbsp;time=0.067..0.071&nbsp;rows=12.00&nbsp;loops=1)
&gt;&gt;&nbsp;Planning&nbsp;Time:&nbsp;0.121&nbsp;ms
&gt;&gt;&nbsp;Execution&nbsp;Time:&nbsp;0.126&nbsp;ms
&gt;&gt;&nbsp;(3&nbsp;rows)
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Then&nbsp;I&nbsp;found&nbsp;that,&nbsp;in&nbsp;pg_proc.dat,&nbsp;the&nbsp;function's&nbsp;prorows&nbsp;is&nbsp;defined&nbsp;as&nbsp;10.&nbsp;Since&nbsp;the&nbsp;function&nbsp;returns&nbsp;one&nbsp;row&nbsp;per&nbsp;lock&nbsp;type,&nbsp;and&nbsp;lock&nbsp;types&nbsp;are&nbsp;not&nbsp;something&nbsp;that&nbsp;change&nbsp;frequently,&nbsp;I&nbsp;think&nbsp;it&nbsp;is&nbsp;better&nbsp;to&nbsp;give&nbsp;the&nbsp;planner&nbsp;a&nbsp;more&nbsp;accurate&nbsp;row&nbsp;count.&nbsp;After&nbsp;changing&nbsp;prorows&nbsp;to&nbsp;12,&nbsp;the&nbsp;plan&nbsp;looks&nbsp;like&nbsp;this:
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;evantest=#&nbsp;EXPLAIN&nbsp;ANALYZE&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;pg_catalog.pg_stat_lock;
&gt;&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QUERY&nbsp;PLAN
&gt;&gt;&nbsp;-----------------------------------------------------------------------------------------------------------------------
&gt;&gt;&nbsp;Function&nbsp;Scan&nbsp;on&nbsp;pg_stat_get_lock&nbsp;l&nbsp;&nbsp;(cost=0.00..0.12&nbsp;rows=12&nbsp;width=64)&nbsp;(actual&nbsp;time=0.134..0.138&nbsp;rows=12.00&nbsp;loops=1)
&gt;&gt;&nbsp;Planning:
&gt;&gt;&nbsp;&nbsp;&nbsp;Buffers:&nbsp;shared&nbsp;hit=13
&gt;&gt;&nbsp;Planning&nbsp;Time:&nbsp;0.313&nbsp;ms
&gt;&gt;&nbsp;Execution&nbsp;Time:&nbsp;0.228&nbsp;ms
&gt;&gt;&nbsp;(5&nbsp;rows)
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;While&nbsp;there,&nbsp;I&nbsp;also&nbsp;made&nbsp;two&nbsp;small&nbsp;tweaks&nbsp;to&nbsp;two&nbsp;function&nbsp;comments&nbsp;in&nbsp;pgstat_lock.c.&nbsp;If&nbsp;those&nbsp;are&nbsp;not&nbsp;considered&nbsp;worth&nbsp;changing,&nbsp;I&nbsp;am&nbsp;okay&nbsp;with&nbsp;removing&nbsp;them&nbsp;from&nbsp;the&nbsp;patch.
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Please&nbsp;see&nbsp;the&nbsp;attached&nbsp;patch&nbsp;for&nbsp;details.
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Best&nbsp;regards,
&gt;&gt;&nbsp;--
&gt;&gt;&nbsp;Chao&nbsp;Li&nbsp;(Evan)
&gt;&gt;&nbsp;HighGo&nbsp;Software&nbsp;Co.,&nbsp;Ltd.
&gt;&gt;&nbsp;https://www.highgo.com/
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;<v1-0001-Adjust-pg_stat_get_lock-row-estimate-and-comments.patch&gt;
&gt;
&gt;Rebased.
&gt;
&gt;Best&nbsp;regards,
&gt;--
&gt;Chao&nbsp;Li&nbsp;(Evan)
&gt;HighGo&nbsp;Software&nbsp;Co.,&nbsp;Ltd.
&gt;https://www.highgo.com/



Hi Chao,
After testing with GDB, I found that before your patch, the catalog data of 
pg_stat_get_lock shows (proowner = 10):
{oid = 6509, proname = {data = "pg_stat_get_lock", '\000' <repeats 47 
times&gt;}, pronamespace = 11, proowner = 10, prolang = 12, procost = 1, 
prorows = 10, provariadic = 0, prosupport = 0, prokind = 102 'f', prosecdef = 
false, proleakproof = false, proisstrict = true, proretset = true, provolatile 
= 118 'v', proparallel = 114 'r', pronargs = 0, pronargdefaults = 0, prorettype 
= 2249, proargtypes = {vl_len_ = 96, ndim = 1, dataoffset = 0, elemtype = 26, 
dim1 = 0, lbound1 = 0, values = 0x7f09190754e0}}
`
With your v2 patch applied, the data becomes (proowner = 12):
{oid = 6509, proname = {data = "pg_stat_get_lock", '\000' <repeats 47 
times&gt;}, pronamespace = 11, proowner = 10, prolang = 12, procost = 1,
&nbsp; prorows = 12, provariadic = 0, prosupport = 0, prokind = 102 'f', 
prosecdef = false, proleakproof = false, proisstrict = true, proretset = true,
&nbsp; provolatile = 118 'v', proparallel = 114 'r', pronargs = 0, 
pronargdefaults = 0, prorettype = 2249, proargtypes = {vl_len_ = 96, ndim = 1,
&nbsp; &nbsp; dataoffset = 0, elemtype = 26, dim1 = 0, lbound1 = 0, values = 
0x7f013c201788}}
`
The estimated row count 12 of pg_stat_get_lock matches the number of lockable 
object types:
relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, 
object, userlock, advisory, and applytransaction. &nbsp;
This is exactly as mentioned in the documentation "Table 27.11. Wait Events of 
Type Lock", as shown below.
`
xman=# explain select * from pg_catalog.pg_stat_lock;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
-------------------------------------------------------------------------
&nbsp;Function Scan on pg_stat_get_lock l &nbsp;(cost=0.00..0.12 rows=12 
width=64)
(1 row)


xman=# explain analyze select * from pg_catalog.pg_stat_lock;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp;
-----------------------------------------------------------------------------------------------------------------------
&nbsp;Function Scan on pg_stat_get_lock l &nbsp;(cost=0.00..0.12 rows=12 
width=64) (actual time=0.115..0.124 rows=12.00 loops=1)
&nbsp;Planning Time: 4941.026 ms
&nbsp;Execution Time: 0.240 ms
(3 rows)


xman=# select * from pg_catalog.pg_stat_lock;
&nbsp; &nbsp; &nbsp;locktype &nbsp; &nbsp; | waits | wait_time | 
fastpath_exceeded | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;stats_reset &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp;
------------------+-------+-----------+-------------------+-------------------------------
&nbsp;relation &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; 
&nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
2026-06-06 10:59:06.350828+08
&nbsp;extend &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; 
&nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;frozenid &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; 
&nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
2026-06-06 10:59:06.350828+08
&nbsp;page &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; 
&nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;tuple &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; 0 | &nbsp; 
&nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;transactionid &nbsp; &nbsp;| &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 
&nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
2026-06-06 10:59:06.350828+08
&nbsp;virtualxid &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; 
&nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
2026-06-06 10:59:06.350828+08
&nbsp;spectoken &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; 0 | &nbsp; &nbsp; 
&nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
2026-06-06 10:59:06.350828+08
&nbsp;object &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; 
&nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;userlock &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; 
&nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
2026-06-06 10:59:06.350828+08
&nbsp;advisory &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; 
&nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
2026-06-06 10:59:06.350828+08
&nbsp;applytransaction | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 
10:59:06.350828+08
(12 rows)




`
Thanks for the patch.




regards,
--
ZizhuanLiu&nbsp;(X-MAN)&nbsp;
[email protected]

Re: Adjust pg_stat_get_lock() prorows to match lock types

Reply via email to