f3fa31327e
It was pointed out that pg_buffercache_summary()'s report of the overall average usage count isn't that useful, and what would be more helpful in many cases is to report totals for each possible usage count. Add a new function to do it like that. Since pg_buffercache 1.4 is already new for v16, we don't need to create a new extension version; we'll just define this as part of 1.4. Nathan Bossart Discussion: https://postgr.es/m/20230130233040.GA2800702@nathanxps13
58 lines
1.5 KiB
Plaintext
58 lines
1.5 KiB
Plaintext
CREATE EXTENSION pg_buffercache;
|
|
select count(*) = (select setting::bigint
|
|
from pg_settings
|
|
where name = 'shared_buffers')
|
|
from pg_buffercache;
|
|
?column?
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
select buffers_used + buffers_unused > 0,
|
|
buffers_dirty <= buffers_used,
|
|
buffers_pinned <= buffers_used
|
|
from pg_buffercache_summary();
|
|
?column? | ?column? | ?column?
|
|
----------+----------+----------
|
|
t | t | t
|
|
(1 row)
|
|
|
|
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
|
|
?column?
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
-- Check that the functions / views can't be accessed by default. To avoid
|
|
-- having to create a dedicated user, use the pg_database_owner pseudo-role.
|
|
SET ROLE pg_database_owner;
|
|
SELECT * FROM pg_buffercache;
|
|
ERROR: permission denied for view pg_buffercache
|
|
SELECT * FROM pg_buffercache_pages() AS p (wrong int);
|
|
ERROR: permission denied for function pg_buffercache_pages
|
|
SELECT * FROM pg_buffercache_summary();
|
|
ERROR: permission denied for function pg_buffercache_summary
|
|
SELECT * FROM pg_buffercache_usage_counts();
|
|
ERROR: permission denied for function pg_buffercache_usage_counts
|
|
RESET role;
|
|
-- Check that pg_monitor is allowed to query view / function
|
|
SET ROLE pg_monitor;
|
|
SELECT count(*) > 0 FROM pg_buffercache;
|
|
?column?
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();
|
|
?column?
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
SELECT count(*) > 0 FROM pg_buffercache_usage_counts();
|
|
?column?
|
|
----------
|
|
t
|
|
(1 row)
|
|
|