I found it useful to be able to look at what relations are occupying the
cache (mainly for debugging and comparing with page estimates).
I am wondering if it might be a useful addition generally?
How it works a SRF called dump_cache() is created, which is then exposed
as system view pg_dump_cache. So stuff like the following can be
performed (cache of 2000 immediately after 'make installcheck'):
regression=# SELECT c.relname, count(*) AS buffers
regression-# FROM pg_class c, pg_dump_cache d
regression-# WHERE d.relfilenode = c.relfilenode
regression-# GROUP BY c.relname
regression-# ORDER BY 2 DESC LIMIT 10;
relname | buffers
---------------------------------+---------
tenk1 | 345
tenk2 | 345
onek | 138
pg_attribute | 102
road | 81
pg_attribute_relid_attnam_index | 74
onek2 | 69
pg_proc | 59
pg_proc_proname_args_nsp_index | 56
hash_f8_heap | 49
(10 rows)
regression=#
As of now the patch breaks 1 regression test (rules - that new view...)
and does not peek into the contents of the buffers themselves (I was
mainly interested in which relations were there), and does not worry too
much about a consistent view of the buffer contents (as I didn't want it
to block all other activity!).
If it seems like a worthwhile addition I will amend the regression
expected and resubmit.
Any comments?
Mark
diff -Naur pgsql.orig/src/backend/catalog/system_views.sql
pgsql/src/backend/catalog/system_views.sql
--- pgsql.orig/src/backend/catalog/system_views.sql Thu Mar 3 11:29:55 2005
+++ pgsql/src/backend/catalog/system_views.sql Thu Mar 3 11:41:24 2005
@@ -277,3 +277,8 @@
DO INSTEAD NOTHING;
GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
+
+CREATE VIEW pg_dump_cache AS
+ SELECT D.* FROM pg_dump_cache() AS D
+ (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid);
+
diff -Naur pgsql.orig/src/backend/utils/adt/Makefile
pgsql/src/backend/utils/adt/Makefile
--- pgsql.orig/src/backend/utils/adt/Makefile Thu Mar 3 11:29:53 2005
+++ pgsql/src/backend/utils/adt/Makefile Thu Mar 3 11:32:10 2005
@@ -24,7 +24,7 @@
tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \
network.o mac.o inet_net_ntop.o inet_net_pton.o \
ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \
- ascii.o quote.o pgstatfuncs.o encode.o
+ ascii.o quote.o pgstatfuncs.o encode.o dumpcache.o
like.o: like.c like_match.c
diff -Naur pgsql.orig/src/backend/utils/adt/dumpcache.c
pgsql/src/backend/utils/adt/dumpcache.c
--- pgsql.orig/src/backend/utils/adt/dumpcache.c Thu Jan 1 12:00:00 1970
+++ pgsql/src/backend/utils/adt/dumpcache.c Thu Mar 3 11:51:53 2005
@@ -0,0 +1,119 @@
+/*-------------------------------------------------------------------------
+ *
+ * dumpcache.c
+ * display some contents for the buffer cache
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "funcapi.h"
+#include "catalog/pg_type.h"
+#include "storage/buf_internals.h"
+#include "storage/bufmgr.h"
+#include "utils/relcache.h"
+
+
+extern Datum dump_cache(PG_FUNCTION_ARGS);
+
+
+/*
+ * Function context for data persisting over repeated calls.
+ */
+typedef struct {
+ int buffer;
+ AttInMetadata *attinmeta;
+ BufferDesc *bufhdr;
+ RelFileNode rnode;
+ char *values[3];
+} dumpcache_fctx;
+
+
+/*
+ * Return a tuple that has bufferid, relfilenoide, reltablespace and
+ * reldatabase OIDs.
+ */
+Datum
+dump_cache(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ Datum result;
+ MemoryContext oldcontext;
+ dumpcache_fctx *fctx; /* User function context. */
+ TupleDesc tupledesc;
+ HeapTuple tuple;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Switch context when allocating stuff to be used in later
calls */
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ /* construct a tuple to return */
+ tupledesc = CreateTemplateTupleDesc(4, false);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 1, "bufferid",
+
INT4OID, -1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 2, "relfilenode",
+ OIDOID,
-1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 3, "reltablespace",
+ OIDOID,
-1, 0);
+ TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase",
+ OIDOID,
-1, 0);
+
+ /* Generate attribute metadata needed later to produce tuples */
+ funcctx->attinmeta = TupleDescGetAttInMetadata(tupledesc);
+
+ /*
+ * Create a function context for cross-call persistence
+ * and initialize the buffer counters.
+ */
+ fctx = (dumpcache_fctx *) palloc(sizeof(dumpcache_fctx));
+
+ fctx->buffer = 0;
+ fctx->bufhdr = BufferDescriptors;
+
+ funcctx->user_fctx = fctx;
+ funcctx->max_calls = NBuffers;
+
+ /* allocate the strings for tuple formation */
+ fctx->values[0] = (char *) palloc(NAMEDATALEN + 1);
+ fctx->values[1] = (char *) palloc(NAMEDATALEN + 1);
+ fctx->values[2] = (char *) palloc(NAMEDATALEN + 1);
+ fctx->values[3] = (char *) palloc(NAMEDATALEN + 1);
+
+
+ /* Return to original context when allocating transient memory
*/
+ MemoryContextSwitchTo(oldcontext);
+ }
+ /* <user defined code> */
+ funcctx = SRF_PERCALL_SETUP();
+
+ /* Get the saved state, and set up the result */
+ fctx = funcctx->user_fctx;
+
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ /* Get the relation node */
+ fctx->rnode = (fctx->bufhdr)->tag.rnode;
+
+ /* setup cstrings and create a tuple from them */
+ sprintf(fctx->values[0], "%u", fctx->buffer);
+ sprintf(fctx->values[1], "%u", fctx->rnode.relNode);
+ sprintf(fctx->values[2], "%u", fctx->rnode.spcNode);
+ sprintf(fctx->values[3], "%u", fctx->rnode.dbNode);
+
+ tuple = BuildTupleFromCStrings(funcctx->attinmeta,
fctx->values);
+ result = HeapTupleGetDatum(tuple);
+
+
+ /* increment the buffer count and the buffer it points to */
+ fctx->buffer++;
+ fctx->bufhdr++;
+
+ SRF_RETURN_NEXT(funcctx, result);
+ }
+ else
+ SRF_RETURN_DONE(funcctx);
+}
+
diff -Naur pgsql.orig/src/include/catalog/pg_proc.h
pgsql/src/include/catalog/pg_proc.h
--- pgsql.orig/src/include/catalog/pg_proc.h Thu Mar 3 11:30:02 2005
+++ pgsql/src/include/catalog/pg_proc.h Thu Mar 3 11:39:27 2005
@@ -3615,6 +3615,8 @@
DATA(insert OID = 2558 ( int4 PGNSP PGUID 12 f f t
f i 1 23 "16" _null_ bool_int4 - _null_ ));
DESCR("convert boolean to int4");
+/* dump cache */
+DATA(insert OID = 2510 ( pg_dump_cache PGNSP PGUID 12 f f t t v 0 2249 ""
_null_ dump_cache - _null_ ));
/*
* Symbolic values for provolatile column: these indicate whether the result
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
