This is an automated email from the ASF dual-hosted git repository.
reshke pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 5f591f23693 Cherry-pick: Add pg_buffercache_usage_counts() to
contrib/pg_buffercache. (#1492)
5f591f23693 is described below
commit 5f591f23693f431a45fbd39fc974d0bbe0d6f4e3
Author: reshke <[email protected]>
AuthorDate: Sun Dec 14 00:53:17 2025 +0500
Cherry-pick: Add pg_buffercache_usage_counts() to contrib/pg_buffercache.
(#1492)
Turns out, `CREATE EXTENSION pg_buffercache` does not work on main.
It is failing due pg_buffercache_usage_counts function is missing.
This function was committed to PostgreSQL as part of
https://github.com/postgres/postgres/commit/f3fa31327ecba75ee0e946abaa56dbf471ba704b
Extension sql 1.4.1 version was cherry-picked to Cloudberry as part of
https://github.com/apache/cloudberry/pull/883/ (see commit
79f2d28ea05e0746170be07386fe728e1003aaac)
```
CREATE EXTENSION pg_buffercache;
+ERROR: function pg_buffercache_usage_counts() does not exist
```
This pr fixes cherry-pick issues, and adds this extension to `ic-contrib`
check
Found during PG16 rebase work
Original commit msg:
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
---
.github/workflows/build-cloudberry.yml | 1 +
.github/workflows/build-deb-cloudberry.yml | 1 +
contrib/pg_buffercache/expected/pg_buffercache.out | 28 +++---
.../pg_buffercache/pg_buffercache--1.3--1.4.sql | 11 +++
contrib/pg_buffercache/pg_buffercache_pages.c | 43 +++++++++
contrib/pg_buffercache/sql/pg_buffercache.sql | 12 +--
doc/src/sgml/pgbuffercache.sgml | 105 ++++++++++++++++++++-
7 files changed, 177 insertions(+), 24 deletions(-)
diff --git a/.github/workflows/build-cloudberry.yml
b/.github/workflows/build-cloudberry.yml
index 04d5e827b6e..4f4d4f3939a 100644
--- a/.github/workflows/build-cloudberry.yml
+++ b/.github/workflows/build-cloudberry.yml
@@ -298,6 +298,7 @@ jobs:
"contrib/pgcrypto:installcheck",
"contrib/tablefunc:installcheck",
"contrib/passwordcheck:installcheck",
+ "contrib/pg_buffercache:installcheck",
"contrib/sslinfo:installcheck"]
},
{"test":"ic-gpcontrib",
diff --git a/.github/workflows/build-deb-cloudberry.yml
b/.github/workflows/build-deb-cloudberry.yml
index 38c2391376e..6299c9500c6 100644
--- a/.github/workflows/build-deb-cloudberry.yml
+++ b/.github/workflows/build-deb-cloudberry.yml
@@ -237,6 +237,7 @@ jobs:
"contrib/pgcrypto:installcheck",
"contrib/tablefunc:installcheck",
"contrib/passwordcheck:installcheck",
+ "contrib/pg_buffercache:installcheck",
"contrib/sslinfo:installcheck"]
},
{"test":"ic-deb-gpcontrib",
diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out
b/contrib/pg_buffercache/expected/pg_buffercache.out
index 7b41872634e..de77aac68b0 100644
--- a/contrib/pg_buffercache/expected/pg_buffercache.out
+++ b/contrib/pg_buffercache/expected/pg_buffercache.out
@@ -17,9 +17,24 @@ from pg_buffercache_summary();
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;
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
?column?
----------
@@ -72,17 +87,7 @@ SELECT count(*) > 0 FROM
gp_buffercache_usage_counts_aggregated WHERE buffers >=
(1 row)
-- Check that the functions / views can't be accessed by default.
-CREATE ROLE buffercache_test;
-SET ROLE buffercache_test;
-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
-RESET role;
-SELECT * FROM pg_buffercache_usage_counts();
-ERROR: permission denied for function pg_buffercache_usage_counts
+SET ROLE pg_database_owner;
-- GPDB
SELECT * FROM pg_buffercache_summary;
ERROR: permission denied for view pg_buffercache_summary
@@ -166,4 +171,3 @@ SELECT count(*) > 0 FROM
gp_buffercache_usage_counts_aggregated;
(1 row)
RESET ROLE;
-DROP ROLE buffercache_test;
diff --git a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
index 8f212dc5e93..d5aebf3ba39 100644
--- a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
+++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql
@@ -12,6 +12,17 @@ CREATE FUNCTION pg_buffercache_summary(
AS 'MODULE_PATHNAME', 'pg_buffercache_summary'
LANGUAGE C PARALLEL SAFE;
+CREATE FUNCTION pg_buffercache_usage_counts(
+ OUT usage_count int4,
+ OUT buffers int4,
+ OUT dirty int4,
+ OUT pinned int4)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_buffercache_usage_counts'
+LANGUAGE C PARALLEL SAFE;
+
-- Don't want these to be available to public.
REVOKE ALL ON FUNCTION pg_buffercache_summary() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pg_buffercache_summary() TO pg_monitor;
+REVOKE ALL ON FUNCTION pg_buffercache_usage_counts() FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_buffercache_usage_counts() TO pg_monitor;
diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c
b/contrib/pg_buffercache/pg_buffercache_pages.c
index 8e0807d6265..e91f35599f0 100644
--- a/contrib/pg_buffercache/pg_buffercache_pages.c
+++ b/contrib/pg_buffercache/pg_buffercache_pages.c
@@ -18,6 +18,7 @@
#define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8
#define NUM_BUFFERCACHE_PAGES_ELEM 9
#define NUM_BUFFERCACHE_SUMMARY_ELEM 5
+#define NUM_BUFFERCACHE_USAGE_COUNTS_ELEM 4
PG_MODULE_MAGIC;
@@ -61,6 +62,7 @@ typedef struct
*/
PG_FUNCTION_INFO_V1(pg_buffercache_pages);
PG_FUNCTION_INFO_V1(pg_buffercache_summary);
+PG_FUNCTION_INFO_V1(pg_buffercache_usage_counts);
Datum
pg_buffercache_pages(PG_FUNCTION_ARGS)
@@ -304,3 +306,44 @@ pg_buffercache_summary(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+Datum
+pg_buffercache_usage_counts(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ int usage_counts[BM_MAX_USAGE_COUNT + 1] = {0};
+ int dirty[BM_MAX_USAGE_COUNT + 1] = {0};
+ int pinned[BM_MAX_USAGE_COUNT + 1] = {0};
+ Datum values[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM];
+ bool nulls[NUM_BUFFERCACHE_USAGE_COUNTS_ELEM] = {0};
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ for (int i = 0; i < NBuffers; i++)
+ {
+ BufferDesc *bufHdr = GetBufferDescriptor(i);
+ uint32 buf_state = pg_atomic_read_u32(&bufHdr->state);
+ int usage_count;
+
+ usage_count = BUF_STATE_GET_USAGECOUNT(buf_state);
+ usage_counts[usage_count]++;
+
+ if (buf_state & BM_DIRTY)
+ dirty[usage_count]++;
+
+ if (BUF_STATE_GET_REFCOUNT(buf_state) > 0)
+ pinned[usage_count]++;
+ }
+
+ for (int i = 0; i < BM_MAX_USAGE_COUNT + 1; i++)
+ {
+ values[0] = Int32GetDatum(i);
+ values[1] = Int32GetDatum(usage_counts[i]);
+ values[2] = Int32GetDatum(dirty[i]);
+ values[3] = Int32GetDatum(pinned[i]);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
values, nulls);
+ }
+
+ return (Datum) 0;
+}
diff --git a/contrib/pg_buffercache/sql/pg_buffercache.sql
b/contrib/pg_buffercache/sql/pg_buffercache.sql
index 5d2beecd707..e9091c9e38c 100644
--- a/contrib/pg_buffercache/sql/pg_buffercache.sql
+++ b/contrib/pg_buffercache/sql/pg_buffercache.sql
@@ -10,12 +10,15 @@ select buffers_used + buffers_unused > 0,
buffers_pinned <= buffers_used
from pg_buffercache_summary();
+SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
+
-- 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();
+SELECT * FROM pg_buffercache_usage_counts();
RESET role;
SELECT count(*) > 0 FROM pg_buffercache_usage_counts() WHERE buffers >= 0;
@@ -42,12 +45,7 @@ SELECT count(*) > 0 FROM gp_buffercache_usage_counts WHERE
buffers >= 0;
SELECT count(*) > 0 FROM gp_buffercache_usage_counts_aggregated WHERE buffers
>= 0;
-- Check that the functions / views can't be accessed by default.
-CREATE ROLE buffercache_test;
-SET ROLE buffercache_test;
-SELECT * FROM pg_buffercache;
-SELECT * FROM pg_buffercache_pages() AS p (wrong int);
-SELECT * FROM pg_buffercache_summary();
-SELECT * FROM pg_buffercache_usage_counts();
+SET ROLE pg_database_owner;
-- GPDB
SELECT * FROM pg_buffercache_summary;
SELECT * FROM pg_buffercache_usage_counts;
@@ -73,5 +71,3 @@ SELECT buffers_used + buffers_unused > 0 FROM
gp_buffercache_summary_aggregated;
SELECT count(*) > 0 FROM gp_buffercache_usage_counts;
SELECT count(*) > 0 FROM gp_buffercache_usage_counts_aggregated;
RESET ROLE;
-
-DROP ROLE buffercache_test;
diff --git a/doc/src/sgml/pgbuffercache.sgml b/doc/src/sgml/pgbuffercache.sgml
index 8f314ee8ff4..a05f010b2a5 100644
--- a/doc/src/sgml/pgbuffercache.sgml
+++ b/doc/src/sgml/pgbuffercache.sgml
@@ -21,9 +21,10 @@
</indexterm>
<para>
- The module provides the <function>pg_buffercache_pages()</function>
- function, wrapped in the <structname>pg_buffercache</structname> view, and
- the <function>pg_buffercache_summary()</function> function.
+ This module provides the <function>pg_buffercache_pages()</function>
+ function (wrapped in the <structname>pg_buffercache</structname> view),
+ the <function>pg_buffercache_summary()</function> function, and the
+ <function>pg_buffercache_usage_counts()</function> function.
</para>
<para>
@@ -38,6 +39,12 @@
row summarizing the state of the shared buffer cache.
</para>
+ <para>
+ The <function>pg_buffercache_usage_counts()</function> function returns a set
+ of records, each row describing the number of buffers with a given usage
+ count.
+ </para>
+
<para>
By default, use is restricted to superusers and roles with privileges of the
<literal>pg_monitor</literal> role. Access may be granted to others
@@ -242,7 +249,7 @@
<structfield>usagecount_avg</structfield> <type>float8</type>
</para>
<para>
- Average usagecount of used shared buffers
+ Average usage count of used shared buffers
</para></entry>
</row>
</tbody>
@@ -266,6 +273,84 @@
</sect2>
<sect2>
+ <title>The <function>pg_buffercache_usage_counts()</function>
Function</title>
+
+ <para>
+ The definitions of the columns exposed by the function are shown in
+ <xref linkend="pgbuffercache_usage_counts-columns"/>.
+ </para>
+
+ <table id="pgbuffercache_usage_counts-columns">
+ <title><function>pg_buffercache_usage_counts()</function> Output
Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>usage_count</structfield> <type>int4</type>
+ </para>
+ <para>
+ A possible buffer usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of buffers with the usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>dirty</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of dirty buffers with the usage count
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pinned</structfield> <type>int4</type>
+ </para>
+ <para>
+ Number of pinned buffers with the usage count
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <function>pg_buffercache_usage_counts()</function> function returns a
+ set of rows summarizing the states of all shared buffers, aggregated over
+ the possible usage count values. Similar and more detailed information is
+ provided by the <structname>pg_buffercache</structname> view, but
+ <function>pg_buffercache_usage_counts()</function> is significantly cheaper.
+ </para>
+
+ <para>
+ Like the <structname>pg_buffercache</structname> view,
+ <function>pg_buffercache_usage_counts()</function> does not acquire buffer
+ manager locks. Therefore concurrent activity can lead to minor inaccuracies
+ in the result.
+ </para>
+ </sect2>
+
+ <sect2 id="pgbuffercache-sample-output">
<title>Sample Output</title>
<screen>
@@ -299,6 +384,18 @@ regression=# SELECT * FROM pg_buffercache_summary();
--------------+----------------+---------------+----------------+----------------
248 | 2096904 | 39 | 0 |
3.141129
(1 row)
+
+
+regression=# SELECT * FROM pg_buffercache_usage_counts();
+ usage_count | buffers | dirty | pinned
+-------------+---------+-------+--------
+ 0 | 14650 | 0 | 0
+ 1 | 1436 | 671 | 0
+ 2 | 102 | 88 | 0
+ 3 | 23 | 21 | 0
+ 4 | 9 | 7 | 0
+ 5 | 164 | 106 | 0
+(6 rows)
</screen>
</sect2>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
