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 buffer limit 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;





Thu, 9 Sep 2004 14:16:47 -0400 (EDT)
Chester Kustarz <chester@arbor.net>
Buffer contents