diff options
Diffstat (limited to 'contrib/pg_buffercache')
| -rw-r--r-- | contrib/pg_buffercache/Makefile | 3 | ||||
| -rw-r--r-- | contrib/pg_buffercache/expected/pg_buffercache.out | 33 | ||||
| -rw-r--r-- | contrib/pg_buffercache/meson.build | 1 | ||||
| -rw-r--r-- | contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql | 17 | ||||
| -rw-r--r-- | contrib/pg_buffercache/pg_buffercache.control | 2 | ||||
| -rw-r--r-- | contrib/pg_buffercache/pg_buffercache_pages.c | 67 | ||||
| -rw-r--r-- | contrib/pg_buffercache/sql/pg_buffercache.sql | 18 |
7 files changed, 139 insertions, 2 deletions
diff --git a/contrib/pg_buffercache/Makefile b/contrib/pg_buffercache/Makefile index d74b3e853c6..d6b58d4da94 100644 --- a/contrib/pg_buffercache/Makefile +++ b/contrib/pg_buffercache/Makefile @@ -7,7 +7,8 @@ OBJS = \ EXTENSION = pg_buffercache DATA = pg_buffercache--1.2.sql pg_buffercache--1.2--1.3.sql \ - pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql + pg_buffercache--1.1--1.2.sql pg_buffercache--1.0--1.1.sql \ + pg_buffercache--1.3--1.4.sql PGFILEDESC = "pg_buffercache - monitoring of shared buffer cache in real-time" REGRESS = pg_buffercache diff --git a/contrib/pg_buffercache/expected/pg_buffercache.out b/contrib/pg_buffercache/expected/pg_buffercache.out index 138556efc9f..635f01e3b21 100644 --- a/contrib/pg_buffercache/expected/pg_buffercache.out +++ b/contrib/pg_buffercache/expected/pg_buffercache.out @@ -8,3 +8,36 @@ from pg_buffercache; 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) + +-- 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 +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) + diff --git a/contrib/pg_buffercache/meson.build b/contrib/pg_buffercache/meson.build index dd9948e5f0b..ff7f9162cee 100644 --- a/contrib/pg_buffercache/meson.build +++ b/contrib/pg_buffercache/meson.build @@ -19,6 +19,7 @@ install_data( 'pg_buffercache--1.1--1.2.sql', 'pg_buffercache--1.2--1.3.sql', 'pg_buffercache--1.2.sql', + 'pg_buffercache--1.3--1.4.sql', 'pg_buffercache.control', kwargs: contrib_data_args, ) diff --git a/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql new file mode 100644 index 00000000000..8f212dc5e93 --- /dev/null +++ b/contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql @@ -0,0 +1,17 @@ +/* contrib/pg_buffercache/pg_buffercache--1.3--1.4.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_buffercache UPDATE TO '1.4'" to load this file. \quit + +CREATE FUNCTION pg_buffercache_summary( + OUT buffers_used int4, + OUT buffers_unused int4, + OUT buffers_dirty int4, + OUT buffers_pinned int4, + OUT usagecount_avg float8) +AS 'MODULE_PATHNAME', 'pg_buffercache_summary' +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; diff --git a/contrib/pg_buffercache/pg_buffercache.control b/contrib/pg_buffercache/pg_buffercache.control index 8c060ae9abf..a82ae5f9bb5 100644 --- a/contrib/pg_buffercache/pg_buffercache.control +++ b/contrib/pg_buffercache/pg_buffercache.control @@ -1,5 +1,5 @@ # pg_buffercache extension comment = 'examine the shared buffer cache' -default_version = '1.3' +default_version = '1.4' module_pathname = '$libdir/pg_buffercache' relocatable = true diff --git a/contrib/pg_buffercache/pg_buffercache_pages.c b/contrib/pg_buffercache/pg_buffercache_pages.c index c5754ea9fa5..1c6a2f22caa 100644 --- a/contrib/pg_buffercache/pg_buffercache_pages.c +++ b/contrib/pg_buffercache/pg_buffercache_pages.c @@ -17,6 +17,7 @@ #define NUM_BUFFERCACHE_PAGES_MIN_ELEM 8 #define NUM_BUFFERCACHE_PAGES_ELEM 9 +#define NUM_BUFFERCACHE_SUMMARY_ELEM 5 PG_MODULE_MAGIC; @@ -59,6 +60,7 @@ typedef struct * relation node/tablespace/database/blocknum and dirty indicator. */ PG_FUNCTION_INFO_V1(pg_buffercache_pages); +PG_FUNCTION_INFO_V1(pg_buffercache_summary); Datum pg_buffercache_pages(PG_FUNCTION_ARGS) @@ -237,3 +239,68 @@ pg_buffercache_pages(PG_FUNCTION_ARGS) else SRF_RETURN_DONE(funcctx); } + +Datum +pg_buffercache_summary(PG_FUNCTION_ARGS) +{ + Datum result; + TupleDesc tupledesc; + HeapTuple tuple; + Datum values[NUM_BUFFERCACHE_SUMMARY_ELEM]; + bool nulls[NUM_BUFFERCACHE_SUMMARY_ELEM]; + + int32 buffers_used = 0; + int32 buffers_unused = 0; + int32 buffers_dirty = 0; + int32 buffers_pinned = 0; + int64 usagecount_total = 0; + + if (get_call_result_type(fcinfo, NULL, &tupledesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + for (int i = 0; i < NBuffers; i++) + { + BufferDesc *bufHdr; + uint32 buf_state; + + /* + * This function summarizes the state of all headers. Locking the + * buffer headers wouldn't provide an improved result as the state of + * the buffer can still change after we release the lock and it'd + * noticeably increase the cost of the function. + */ + bufHdr = GetBufferDescriptor(i); + buf_state = pg_atomic_read_u32(&bufHdr->state); + + if (buf_state & BM_VALID) + { + buffers_used++; + usagecount_total += BUF_STATE_GET_USAGECOUNT(buf_state); + + if (buf_state & BM_DIRTY) + buffers_dirty++; + } + else + buffers_unused++; + + if (BUF_STATE_GET_REFCOUNT(buf_state) > 0) + buffers_pinned++; + } + + memset(nulls, 0, sizeof(nulls)); + values[0] = Int32GetDatum(buffers_used); + values[1] = Int32GetDatum(buffers_unused); + values[2] = Int32GetDatum(buffers_dirty); + values[3] = Int32GetDatum(buffers_pinned); + + if (buffers_used != 0) + values[4] = Float8GetDatum((double) usagecount_total / buffers_used); + else + nulls[4] = true; + + /* Build and return the tuple. */ + tuple = heap_form_tuple(tupledesc, values, nulls); + result = HeapTupleGetDatum(tuple); + + PG_RETURN_DATUM(result); +} 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(); |