Thread: Buffer contents

I wanted to know the contents of the buffer cache in 7.4, so I created
this function and view that seems to work ok:

/* Takes relfilenode (from pg_class) and returns the number of* buffer cache pages it is using.*/
PG_FUNCTION_INFO_V1(block_count);
Datum
block_count(PG_FUNCTION_ARGS)
{       Oid relfilenode = PG_GETARG_OID(0);
       BufferDesc *buf;       Buffer buffer;       int count = 0;
       // 0 is not a valid relfilenode although pg_xactlock       // uses it       if (relfilenode == 0) {
PG_RETURN_INT32(0);       }
 
       for (buffer=1; BufferIsValid(buffer); ++buffer) {               buf = &(BufferDescriptors[buffer - 1]);
    if (buf->tag.rnode.relNode == relfilenode) {                       count ++;               }       }
 
       PG_RETURN_INT32(count);
}


/* Returns the number of buffer cache pages the specified relfilenode* is using. Using "buffer" view is probably
easier.*/
CREATE OR REPLACE FUNCTION block_count (oid)
RETURNS integer
LANGUAGE 'C' STABLE STRICT AS '/foo';

/* View to look at how much of the buffer cache different tables are* currently using.* Example:*   foo=# select * from
bufferlimit 10;*       relname    | block_count*   ---------------+-------------*    foo           |       13782*
bar          |        2575*   ...*   (10 rows)*/
 
CREATE OR REPLACE VIEW buffer AS
SELECT * FROM (SELECT relname, block_count(relfilenode) FROM pg_class) t
WHERE block_count > 0 ORDER BY block_count DESC;