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;