Re: [HACKERS] pg_buffercache causes assertion failure - Mailing list pgsql-patches

Tom Lane wrote:
> Mark Kirkwood <markir@paradise.net.nz> writes:
>
>>This patch changes the use of numeric to int8 to represent the
>>relblocknumber column.
>
>
> Applied, thanks.
>
>

This reminds me:

I did some patches for 7.4 and 8.0 a while ago (attached) - while I do
not expect these to be applied (unless it's ok for contrib to get extra
modules in stable releases...), is there somewhere for things like this
to go?

cheers

Mark

P.s : They are amended to use int8 too :-)
diff -Naur pgsql-7.4.7.orig/contrib/Makefile pgsql-7.4.7/contrib/Makefile
--- pgsql-7.4.7.orig/contrib/Makefile    Fri Mar 18 11:44:25 2005
+++ pgsql-7.4.7/contrib/Makefile    Fri Mar 18 10:55:55 2005
@@ -25,6 +25,7 @@
         noupdate    \
         oid2name    \
         pg_autovacuum    \
+        pg_buffercache    \
         pg_dumplo    \
         pg_logger    \
         pgbench        \
diff -Naur pgsql-7.4.7.orig/contrib/README pgsql-7.4.7/contrib/README
--- pgsql-7.4.7.orig/contrib/README    Fri Mar 18 11:44:19 2005
+++ pgsql-7.4.7/contrib/README    Fri Mar 18 10:55:55 2005
@@ -136,6 +136,10 @@
     Automatically performs vacuum
     by Matthew T. O'Connor <matthew@zeut.net>

+pg_buffercache -
+    Real-time queries on the shared buffer cache
+    by Mark Kirkwood <markir@paradise.net.nz>
+
 pg_dumplo -
     Dump large objects
     by Karel Zak <zakkr@zf.jcu.cz>
diff -Naur pgsql-7.4.7.orig/contrib/pg_buffercache/Makefile pgsql-7.4.7/contrib/pg_buffercache/Makefile
--- pgsql-7.4.7.orig/contrib/pg_buffercache/Makefile    Thu Jan  1 12:00:00 1970
+++ pgsql-7.4.7/contrib/pg_buffercache/Makefile    Fri Mar 18 10:55:55 2005
@@ -0,0 +1,17 @@
+# $PostgreSQL: pgsql/contrib/pg_buffercache/Makefile,v 1.1 2005/03/12 15:36:24 neilc Exp $
+
+MODULE_big = pg_buffercache
+OBJS    = pg_buffercache_pages.o
+
+DATA_built = pg_buffercache.sql
+DOCS = README.pg_buffercache
+
+ifdef USE_PGXS
+PGXS = $(shell pg_config --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_buffercache
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff -Naur pgsql-7.4.7.orig/contrib/pg_buffercache/README.pg_buffercache
pgsql-7.4.7/contrib/pg_buffercache/README.pg_buffercache
--- pgsql-7.4.7.orig/contrib/pg_buffercache/README.pg_buffercache    Thu Jan  1 12:00:00 1970
+++ pgsql-7.4.7/contrib/pg_buffercache/README.pg_buffercache    Fri Mar 18 11:47:28 2005
@@ -0,0 +1,110 @@
+Pg_buffercache - Real time queries on the shared buffer cache.
+--------------
+
+  This module consists of a C function 'pg_buffercache_pages()' that returns
+  a set of records, plus a view 'pg_buffercache' to wrapper the function.
+
+  The intent is to do for the buffercache what pg_locks does for locks, i.e -
+  ability to examine what is happening at any given time without having to
+  restart or rebuild the server with debugging code added.
+
+  By default public access is REVOKED from both of these, just in case there
+  are security issues lurking.
+
+
+Installation
+------------
+
+  Build and install the main Postgresql source, then this contrib module:
+
+  $ cd contrib/pg_buffercache
+  $ gmake
+  $ gmake install
+
+
+  To register the functions:
+
+  $ psql -d <database> -f pg_buffercache.sql
+
+
+Notes
+-----
+
+  The definition of the columns exposed in the view is:
+
+       Column     |  references          | Description
+  ----------------+----------------------+------------------------------------
+   bufferid       |                      | Id, 1->shared_buffers.
+   relfilenode    | pg_class.relfilenode | Refilenode of the relation.
+   reldatabase    | pg_database.oid      | Database for the relation.
+   relblocknumber |                      | Offset of the page in the relation.
+   isdirty        |                      | Is the page dirty?
+
+
+  There is one row for each buffer in the shared cache. Unused buffers are
+  shown with all fields null except bufferid.
+
+  Because the cache is shared by all the databases, there are pages from
+  relations not belonging to the current database.
+
+  When the pg_buffercache view is accessed, internal buffer manager locks are
+  taken, and a copy of the buffer cache data is made for the view to display.
+  This ensures that the view produces a consistent set of results, while not
+  blocking normal buffer activity longer than necessary.  Nonetheless there
+  could be some impact on database performance if this view is read often.
+
+
+Sample output
+-------------
+
+  regression=# \d pg_buffercache;
+       View "public.pg_buffercache"
+       Column     |  Type   | Modifiers
+  ----------------+---------+-----------
+   bufferid       | integer |
+   relfilenode    | oid     |
+   reldatabase    | oid     |
+   relblocknumber | bigint  |
+   isdirty        | boolean |
+  View definition:
+   SELECT p.bufferid, p.relfilenode, p.reldatabase,
+          p.relblocknumber, p.isdirty
+     FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid,
+     reltablespace oid, reldatabase oid, relblocknumber bigint,
+     isdirty boolean);
+
+  regression=# SELECT c.relname, count(*) AS buffers
+               FROM pg_class c, pg_buffercache b
+               WHERE b.relfilenode = c.relfilenode
+               GROUP BY c.relname
+               ORDER BY 2 DESC LIMIT 10;
+               relname             | buffers
+  ---------------------------------+---------
+   tenk2                           |     345
+   tenk1                           |     141
+   pg_proc                         |      46
+   pg_class                        |      45
+   pg_attribute                    |      43
+   pg_class_relname_nsp_index      |      30
+   pg_proc_proname_args_nsp_index  |      28
+   pg_attribute_relid_attnam_index |      26
+   pg_depend                       |      22
+   pg_depend_reference_index       |      20
+  (10 rows)
+
+  regression=#
+
+
+Author
+------
+
+  * Mark Kirkwood <markir@paradise.net.nz>
+
+
+Help
+----
+
+  * Design suggestions : Neil Conway <neilc@samurai.com>
+  * Debugging advice : Tom Lane <tgl@sss.pgh.pa.us>
+
+  Thanks guys!
diff -Naur pgsql-7.4.7.orig/contrib/pg_buffercache/pg_buffercache.sql.in
pgsql-7.4.7/contrib/pg_buffercache/pg_buffercache.sql.in
--- pgsql-7.4.7.orig/contrib/pg_buffercache/pg_buffercache.sql.in    Thu Jan  1 12:00:00 1970
+++ pgsql-7.4.7/contrib/pg_buffercache/pg_buffercache.sql.in    Fri Mar 18 11:10:16 2005
@@ -0,0 +1,18 @@
+-- Adjust this setting to control where the objects get created.
+SET search_path = public;
+
+-- Register the function.
+CREATE OR REPLACE FUNCTION pg_buffercache_pages()
+RETURNS SETOF RECORD
+AS 'MODULE_PATHNAME', 'pg_buffercache_pages'
+LANGUAGE 'C';
+
+-- Create a view for convenient access.
+CREATE VIEW pg_buffercache AS
+    SELECT P.* FROM pg_buffercache_pages() AS P
+     (bufferid integer, relfilenode oid, reldatabase oid,
+      relblocknumber int8, isdirty bool);
+
+-- Don't want these to be available at public.
+REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
+REVOKE ALL ON pg_buffercache FROM PUBLIC;
diff -Naur pgsql-7.4.7.orig/contrib/pg_buffercache/pg_buffercache_pages.c
pgsql-7.4.7/contrib/pg_buffercache/pg_buffercache_pages.c
--- pgsql-7.4.7.orig/contrib/pg_buffercache/pg_buffercache_pages.c    Thu Jan  1 12:00:00 1970
+++ pgsql-7.4.7/contrib/pg_buffercache/pg_buffercache_pages.c    Fri Mar 18 11:37:26 2005
@@ -0,0 +1,234 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_buffercache_pages.c
+ *    display some contents of the buffer cache
+ *
+ *      $PostgreSQL$
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "funcapi.h"
+#include "catalog/pg_type.h"
+#include "storage/buf_internals.h"
+#include "storage/bufmgr.h"
+#include "utils/relcache.h"
+
+
+#define NUM_BUFFERCACHE_PAGES_ELEM    5
+
+
+extern Datum pg_buffercache_pages(PG_FUNCTION_ARGS);
+
+
+/*
+ * Record structure holding the to be exposed cache data.
+ */
+typedef struct
+{
+
+    uint32        bufferid;
+    Oid            relfilenode;
+    Oid            reldatabase;
+    BlockNumber    blocknum;
+    bool        isvalid;
+    bool        isdirty;
+
+} BufferCachePagesRec;
+
+
+/*
+ * Function context for data persisting over repeated calls.
+ */
+typedef struct
+{
+
+    AttInMetadata        *attinmeta;
+    TupleTableSlot        *slot;
+    BufferCachePagesRec    *record;
+    char                *values[NUM_BUFFERCACHE_PAGES_ELEM];
+
+} BufferCachePagesContext;
+
+
+/*
+ * Function returning data from the shared buffer cache - buffer number,
+ * relation node/database/blocknum and dirty indicator.
+ */
+PG_FUNCTION_INFO_V1(pg_buffercache_pages);
+Datum
+pg_buffercache_pages(PG_FUNCTION_ARGS)
+{
+
+    FuncCallContext        *funcctx;
+    Datum                result;
+    MemoryContext        oldcontext;
+    BufferCachePagesContext    *fctx;        /* User function context. */
+    TupleDesc            tupledesc;
+    HeapTuple            tuple;
+
+    if (SRF_IS_FIRSTCALL())
+    {
+        RelFileNode    rnode;
+        uint32        i;
+        BufferDesc    *bufHdr;
+
+
+        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(NUM_BUFFERCACHE_PAGES_ELEM, false);
+        TupleDescInitEntry(tupledesc, (AttrNumber) 1, "bufferid",
+                                    INT4OID, -1, 0, false);
+        TupleDescInitEntry(tupledesc, (AttrNumber) 2, "relfilenode",
+                                    OIDOID, -1, 0, false);
+        TupleDescInitEntry(tupledesc, (AttrNumber) 3, "reldatabase",
+                                    OIDOID, -1, 0, false);
+        TupleDescInitEntry(tupledesc, (AttrNumber) 4, "relblockbumber",
+                                    INT8OID, -1, 0, false);
+        TupleDescInitEntry(tupledesc, (AttrNumber) 5, "isdirty",
+                                    BOOLOID, -1, 0, false);
+
+        /* Generate attribute metadata needed later to produce tuples */
+        funcctx->attinmeta = TupleDescGetAttInMetadata(tupledesc);
+
+        /* Allocate a slot for this tupledesc */
+        funcctx->slot = TupleDescGetSlot(tupledesc);
+
+        /*
+         * Create a function context for cross-call persistence
+         * and initialize the buffer counters.
+         */
+        fctx = (BufferCachePagesContext *) palloc(sizeof(BufferCachePagesContext));
+        funcctx->max_calls = NBuffers;
+        funcctx->user_fctx = fctx;
+
+
+        /* Allocate NBuffers worth of BufferCachePagesRec records. */
+        fctx->record = (BufferCachePagesRec *) palloc(sizeof(BufferCachePagesRec) * NBuffers);
+
+        /* allocate the strings for tuple formation */
+        fctx->values[0] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[1] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[2] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[3] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[4] = (char *) palloc(2);
+
+
+        /* Return to original context when allocating transient memory */
+        MemoryContextSwitchTo(oldcontext);
+
+
+        /*
+         * Lock Buffer manager and scan though all the buffers, saving the
+         * relevant fields in the fctx->record structure.
+         */
+        LWLockAcquire(BufMgrLock, LW_SHARED);
+
+        for (i = 0, bufHdr = BufferDescriptors; i < NBuffers; i++, bufHdr++)
+        {
+
+            rnode = bufHdr->tag.rnode;
+
+            fctx->record[i].bufferid = BufferDescriptorGetBuffer(bufHdr);
+            fctx->record[i].relfilenode = rnode.relNode;
+            fctx->record[i].reldatabase = rnode.tblNode;
+            fctx->record[i].blocknum = bufHdr->tag.blockNum;
+
+            if ( bufHdr->flags & BM_DIRTY || bufHdr->cntxDirty)
+            {
+                fctx->record[i].isdirty = true;
+            }
+            else
+            {
+                fctx->record[i].isdirty = false;
+            }
+
+            /* Note if the buffer is valid */
+            if ( (bufHdr->flags & BM_VALID))
+            {
+                fctx->record[i].isvalid = true;
+            }
+            else
+            {
+                fctx->record[i].isvalid = false;
+            }
+
+
+        }
+
+        /* Release Buffer manager. */
+        LWLockRelease(BufMgrLock);
+    }
+
+    funcctx = SRF_PERCALL_SETUP();
+
+    /* Get the saved state */
+    fctx = funcctx->user_fctx;
+
+
+    if (funcctx->call_cntr < funcctx->max_calls)
+    {
+        uint32         i = funcctx->call_cntr;
+        char        *values[NUM_BUFFERCACHE_PAGES_ELEM];
+        int            j;
+
+        /*
+         * Use a temporary values array, initially pointing to
+         * fctx->values, so it can be reassigned w/o losing the storage
+         * for subsequent calls.
+         */
+        for (j = 0; j < NUM_BUFFERCACHE_PAGES_ELEM; j++)
+        {
+            values[j] = fctx->values[j];
+        }
+
+
+        /*
+         * Set all fields except the bufferid to null if the buffer is
+         * unused or not valid.
+         */
+        if (fctx->record[i].blocknum == InvalidBlockNumber ||
+            fctx->record[i].isvalid == false )
+        {
+
+            sprintf(values[0], "%u", fctx->record[i].bufferid);
+            values[1] = NULL;
+            values[2] = NULL;
+            values[3] = NULL;
+            values[4] = NULL;
+
+        }
+        else
+        {
+
+            sprintf(values[0], "%u", fctx->record[i].bufferid);
+            sprintf(values[1], "%u", fctx->record[i].relfilenode);
+            sprintf(values[2], "%u", fctx->record[i].reldatabase);
+            sprintf(values[3], "%u", fctx->record[i].blocknum);
+            if (fctx->record[i].isdirty)
+            {
+                strcpy(values[4], "t");
+            }
+            else
+            {
+                strcpy(values[4], "f");
+            }
+
+        }
+
+
+        /* Build and return the tuple. */
+        tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+        result = TupleGetDatum(funcctx->slot, tuple);
+
+
+        SRF_RETURN_NEXT(funcctx, result);
+    }
+    else
+        SRF_RETURN_DONE(funcctx);
+
+}
+

diff -Naur pgsql-8.0.1.orig/contrib/Makefile pgsql-8.0.1/contrib/Makefile
--- pgsql-8.0.1.orig/contrib/Makefile    Fri Mar 18 10:48:52 2005
+++ pgsql-8.0.1/contrib/Makefile    Fri Mar 18 10:33:15 2005
@@ -26,6 +26,7 @@
         noupdate    \
         oid2name    \
         pg_autovacuum    \
+        pg_buffercache    \
         pg_dumplo    \
         pg_trgm        \
         pgbench        \
diff -Naur pgsql-8.0.1.orig/contrib/README pgsql-8.0.1/contrib/README
--- pgsql-8.0.1.orig/contrib/README    Fri Mar 18 10:48:46 2005
+++ pgsql-8.0.1/contrib/README    Fri Mar 18 10:34:18 2005
@@ -136,6 +136,10 @@
     Automatically performs vacuum
     by Matthew T. O'Connor <matthew@zeut.net>

+pg_buffercache -
+    Real-time queries on the shared buffer cache
+    by Mark Kirkwood <markir@paradise.net.nz>
+
 pg_dumplo -
     Dump large objects
     by Karel Zak <zakkr@zf.jcu.cz>
diff -Naur pgsql-8.0.1.orig/contrib/pg_buffercache/Makefile pgsql-8.0.1/contrib/pg_buffercache/Makefile
--- pgsql-8.0.1.orig/contrib/pg_buffercache/Makefile    Thu Jan  1 12:00:00 1970
+++ pgsql-8.0.1/contrib/pg_buffercache/Makefile    Fri Mar 18 09:59:34 2005
@@ -0,0 +1,17 @@
+# $PostgreSQL: pgsql/contrib/pg_buffercache/Makefile,v 1.1 2005/03/12 15:36:24 neilc Exp $
+
+MODULE_big = pg_buffercache
+OBJS    = pg_buffercache_pages.o
+
+DATA_built = pg_buffercache.sql
+DOCS = README.pg_buffercache
+
+ifdef USE_PGXS
+PGXS = $(shell pg_config --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_buffercache
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff -Naur pgsql-8.0.1.orig/contrib/pg_buffercache/README.pg_buffercache
pgsql-8.0.1/contrib/pg_buffercache/README.pg_buffercache
--- pgsql-8.0.1.orig/contrib/pg_buffercache/README.pg_buffercache    Thu Jan  1 12:00:00 1970
+++ pgsql-8.0.1/contrib/pg_buffercache/README.pg_buffercache    Fri Mar 18 09:59:34 2005
@@ -0,0 +1,112 @@
+Pg_buffercache - Real time queries on the shared buffer cache.
+--------------
+
+  This module consists of a C function 'pg_buffercache_pages()' that returns
+  a set of records, plus a view 'pg_buffercache' to wrapper the function.
+
+  The intent is to do for the buffercache what pg_locks does for locks, i.e -
+  ability to examine what is happening at any given time without having to
+  restart or rebuild the server with debugging code added.
+
+  By default public access is REVOKED from both of these, just in case there
+  are security issues lurking.
+
+
+Installation
+------------
+
+  Build and install the main Postgresql source, then this contrib module:
+
+  $ cd contrib/pg_buffercache
+  $ gmake
+  $ gmake install
+
+
+  To register the functions:
+
+  $ psql -d <database> -f pg_buffercache.sql
+
+
+Notes
+-----
+
+  The definition of the columns exposed in the view is:
+
+       Column     |  references          | Description
+  ----------------+----------------------+------------------------------------
+   bufferid       |                      | Id, 1->shared_buffers.
+   relfilenode    | pg_class.relfilenode | Refilenode of the relation.
+   reltablespace  | pg_tablespace.oid    | Tablespace oid of the relation.
+   reldatabase    | pg_database.oid      | Database for the relation.
+   relblocknumber |                      | Offset of the page in the relation.
+   isdirty        |                      | Is the page dirty?
+
+
+  There is one row for each buffer in the shared cache. Unused buffers are
+  shown with all fields null except bufferid.
+
+  Because the cache is shared by all the databases, there are pages from
+  relations not belonging to the current database.
+
+  When the pg_buffercache view is accessed, internal buffer manager locks are
+  taken, and a copy of the buffer cache data is made for the view to display.
+  This ensures that the view produces a consistent set of results, while not
+  blocking normal buffer activity longer than necessary.  Nonetheless there
+  could be some impact on database performance if this view is read often.
+
+
+Sample output
+-------------
+
+  regression=# \d pg_buffercache;
+       View "public.pg_buffercache"
+       Column     |  Type   | Modifiers
+  ----------------+---------+-----------
+   bufferid       | integer |
+   relfilenode    | oid     |
+   reltablespace  | oid     |
+   reldatabase    | oid     |
+   relblocknumber | bigint  |
+   isdirty        | boolean |
+  View definition:
+   SELECT p.bufferid, p.relfilenode, p.reltablespace, p.reldatabase,
+          p.relblocknumber, p.isdirty
+     FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid,
+     reltablespace oid, reldatabase oid, relblocknumber bigint,
+     isdirty boolean);
+
+  regression=# SELECT c.relname, count(*) AS buffers
+               FROM pg_class c, pg_buffercache b
+               WHERE b.relfilenode = c.relfilenode
+               GROUP BY c.relname
+               ORDER BY 2 DESC LIMIT 10;
+               relname             | buffers
+  ---------------------------------+---------
+   tenk2                           |     345
+   tenk1                           |     141
+   pg_proc                         |      46
+   pg_class                        |      45
+   pg_attribute                    |      43
+   pg_class_relname_nsp_index      |      30
+   pg_proc_proname_args_nsp_index  |      28
+   pg_attribute_relid_attnam_index |      26
+   pg_depend                       |      22
+   pg_depend_reference_index       |      20
+  (10 rows)
+
+  regression=#
+
+
+Author
+------
+
+  * Mark Kirkwood <markir@paradise.net.nz>
+
+
+Help
+----
+
+  * Design suggestions : Neil Conway <neilc@samurai.com>
+  * Debugging advice : Tom Lane <tgl@sss.pgh.pa.us>
+
+  Thanks guys!
diff -Naur pgsql-8.0.1.orig/contrib/pg_buffercache/pg_buffercache.sql.in
pgsql-8.0.1/contrib/pg_buffercache/pg_buffercache.sql.in
--- pgsql-8.0.1.orig/contrib/pg_buffercache/pg_buffercache.sql.in    Thu Jan  1 12:00:00 1970
+++ pgsql-8.0.1/contrib/pg_buffercache/pg_buffercache.sql.in    Fri Mar 18 09:59:34 2005
@@ -0,0 +1,18 @@
+-- Adjust this setting to control where the objects get created.
+SET search_path = public;
+
+-- Register the function.
+CREATE OR REPLACE FUNCTION pg_buffercache_pages()
+RETURNS SETOF RECORD
+AS 'MODULE_PATHNAME', 'pg_buffercache_pages'
+LANGUAGE 'C';
+
+-- Create a view for convenient access.
+CREATE VIEW pg_buffercache AS
+    SELECT P.* FROM pg_buffercache_pages() AS P
+     (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
+      relblocknumber int8, isdirty bool);
+
+-- Don't want these to be available at public.
+REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
+REVOKE ALL ON pg_buffercache FROM PUBLIC;
diff -Naur pgsql-8.0.1.orig/contrib/pg_buffercache/pg_buffercache_pages.c
pgsql-8.0.1/contrib/pg_buffercache/pg_buffercache_pages.c
--- pgsql-8.0.1.orig/contrib/pg_buffercache/pg_buffercache_pages.c    Thu Jan  1 12:00:00 1970
+++ pgsql-8.0.1/contrib/pg_buffercache/pg_buffercache_pages.c    Fri Mar 18 10:31:15 2005
@@ -0,0 +1,242 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_buffercache_pages.c
+ *    display some contents of the buffer cache
+ *
+ *      $PostgreSQL$
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "funcapi.h"
+#include "catalog/pg_type.h"
+#include "storage/buf_internals.h"
+#include "storage/bufmgr.h"
+#include "utils/relcache.h"
+
+
+#define NUM_BUFFERCACHE_PAGES_ELEM    6
+
+extern Datum pg_buffercache_pages(PG_FUNCTION_ARGS);
+
+#if defined (WIN32) || defined (__CYGWIN__)
+extern DLLIMPORT BufferDesc            *BufferDescriptors;
+extern DLLIMPORT volatile uint32    InterruptHoldoffCount;
+#endif
+
+
+
+/*
+ * Record structure holding the to be exposed cache data.
+ */
+typedef struct
+{
+
+    uint32        bufferid;
+    Oid            relfilenode;
+    Oid            reltablespace;
+    Oid            reldatabase;
+    BlockNumber    blocknum;
+    bool        isvalid;
+    bool        isdirty;
+
+} BufferCachePagesRec;
+
+
+/*
+ * Function context for data persisting over repeated calls.
+ */
+typedef struct
+{
+
+    AttInMetadata        *attinmeta;
+    BufferCachePagesRec    *record;
+    char                *values[NUM_BUFFERCACHE_PAGES_ELEM];
+
+} BufferCachePagesContext;
+
+
+/*
+ * Function returning data from the shared buffer cache - buffer number,
+ * relation node/tablespace/database/blocknum and dirty indicator.
+ */
+PG_FUNCTION_INFO_V1(pg_buffercache_pages);
+Datum
+pg_buffercache_pages(PG_FUNCTION_ARGS)
+{
+
+    FuncCallContext        *funcctx;
+    Datum                result;
+    MemoryContext        oldcontext;
+    BufferCachePagesContext    *fctx;        /* User function context. */
+    TupleDesc            tupledesc;
+    HeapTuple            tuple;
+
+    if (SRF_IS_FIRSTCALL())
+    {
+        RelFileNode    rnode;
+        uint32        i;
+        BufferDesc    *bufHdr;
+
+
+        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(NUM_BUFFERCACHE_PAGES_ELEM, 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);
+        TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relblockbumber",
+                                    INT8OID, -1, 0);
+        TupleDescInitEntry(tupledesc, (AttrNumber) 6, "isdirty",
+                                    BOOLOID, -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 = (BufferCachePagesContext *) palloc(sizeof(BufferCachePagesContext));
+        funcctx->max_calls = NBuffers;
+        funcctx->user_fctx = fctx;
+
+
+        /* Allocate NBuffers worth of BufferCachePagesRec records. */
+        fctx->record = (BufferCachePagesRec *) palloc(sizeof(BufferCachePagesRec) * NBuffers);
+
+        /* allocate the strings for tuple formation */
+        fctx->values[0] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[1] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[2] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[3] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[4] = (char *) palloc(3 * sizeof(uint32) + 1);
+        fctx->values[5] = (char *) palloc(2);
+
+
+        /* Return to original context when allocating transient memory */
+        MemoryContextSwitchTo(oldcontext);
+
+
+        /*
+         * Lock Buffer manager and scan though all the buffers, saving the
+         * relevant fields in the fctx->record structure.
+         */
+        LWLockAcquire(BufMgrLock, LW_SHARED);
+
+        for (i = 0, bufHdr = BufferDescriptors; i < NBuffers; i++, bufHdr++)
+        {
+
+            rnode = bufHdr->tag.rnode;
+
+            fctx->record[i].bufferid = BufferDescriptorGetBuffer(bufHdr);
+            fctx->record[i].relfilenode = rnode.relNode;
+            fctx->record[i].reltablespace = rnode.spcNode;
+            fctx->record[i].reldatabase = rnode.dbNode;
+            fctx->record[i].blocknum = bufHdr->tag.blockNum;
+
+            if ( bufHdr->flags & BM_DIRTY || bufHdr->cntxDirty)
+            {
+                fctx->record[i].isdirty = true;
+            }
+            else
+            {
+                fctx->record[i].isdirty = false;
+            }
+
+            /* Note if the buffer is valid */
+            if ( (bufHdr->flags & BM_VALID))
+            {
+                fctx->record[i].isvalid = true;
+            }
+            else
+            {
+                fctx->record[i].isvalid = false;
+            }
+
+
+        }
+
+        /* Release Buffer manager. */
+        LWLockRelease(BufMgrLock);
+    }
+
+    funcctx = SRF_PERCALL_SETUP();
+
+    /* Get the saved state */
+    fctx = funcctx->user_fctx;
+
+
+    if (funcctx->call_cntr < funcctx->max_calls)
+    {
+        uint32         i = funcctx->call_cntr;
+        char        *values[NUM_BUFFERCACHE_PAGES_ELEM];
+        int            j;
+
+        /*
+         * Use a temporary values array, initially pointing to
+         * fctx->values, so it can be reassigned w/o losing the storage
+         * for subsequent calls.
+         */
+        for (j = 0; j < NUM_BUFFERCACHE_PAGES_ELEM; j++)
+        {
+            values[j] = fctx->values[j];
+        }
+
+
+        /*
+         * Set all fields except the bufferid to null if the buffer is
+         * unused or not valid.
+         */
+        if (fctx->record[i].blocknum == InvalidBlockNumber ||
+            fctx->record[i].isvalid == false )
+        {
+
+            sprintf(values[0], "%u", fctx->record[i].bufferid);
+            values[1] = NULL;
+            values[2] = NULL;
+            values[3] = NULL;
+            values[4] = NULL;
+            values[5] = NULL;
+
+        }
+        else
+        {
+
+            sprintf(values[0], "%u", fctx->record[i].bufferid);
+            sprintf(values[1], "%u", fctx->record[i].relfilenode);
+            sprintf(values[2], "%u", fctx->record[i].reltablespace);
+            sprintf(values[3], "%u", fctx->record[i].reldatabase);
+            sprintf(values[4], "%u", fctx->record[i].blocknum);
+            if (fctx->record[i].isdirty)
+            {
+                strcpy(values[5], "t");
+            }
+            else
+            {
+                strcpy(values[5], "f");
+            }
+
+        }
+
+
+        /* Build and return the tuple. */
+        tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+        result = HeapTupleGetDatum(tuple);
+
+
+        SRF_RETURN_NEXT(funcctx, result);
+    }
+    else
+        SRF_RETURN_DONE(funcctx);
+
+}
+