Display Pg buffer cache (WIP) - Mailing list pgsql-patches

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