pg_buffercache: Add pg_buffercache_summary()

Using pg_buffercache_summary() is significantly cheaper than querying
pg_buffercache and summarizing in SQL.

Author: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>
Reviewed-by: Zhang Mingli <zmlpostgres@gmail.com>
Discussion: https://postgr.es/m/CAGPVpCQAXYo54Q%3D8gqBsS%3Du0uk9qhnnq4%2B710BtUhUisX1XGEg%40mail.gmail.com

1 files changed, 18 insertions, 0 deletions

diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql b/contrib/pg_buffercache/sql/pg_buffercache.sql
index e1ba6f7e8d4..2e2e0a74517 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -4,3 +4,21 @@ select count(*) = (select setting::bigint
                    from pg_settings
                    where name = 'shared_buffers')
 from pg_buffercache;
+
+select buffers_used + buffers_unused > 0,
+        buffers_dirty <= buffers_used,
+        buffers_pinned <= buffers_used
+from pg_buffercache_summary();
+
+-- 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;
+SELECT * FROM pg_buffercache_pages() AS p (wrong int);
+SELECT * FROM pg_buffercache_summary();
+RESET role;
+
+-- Check that pg_monitor is allowed to query view / function
+SET ROLE pg_monitor;
+SELECT count(*) > 0 FROM pg_buffercache;
+SELECT buffers_used + buffers_unused > 0 FROM pg_buffercache_summary();