Re: Add the number of pinning backends to pg_buffercache's output

On Sat, Apr 12, 2014 at 9:25 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> Hi,
>
> The last week I twice had the need to see how many backends had some
> buffers pinned. Once during development and once while analyzing a stuck
> vacuum (waiting for a cleanup lock).
> I'd like to add a column to pg_buffercache exposing that. The name I've
> come up with is 'pinning_backends' to reflect the fact that it's not the
> actual pincount due to the backend private arrays.

This name sounds good to me.

+CREATE OR REPLACE VIEW pg_buffercache AS
+    SELECT P.* FROM pg_buffercache_pages() AS P
+    (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid,
+     relforknumber int2, relblocknumber int8, isdirty bool, usagecount int2,
+     pincount int8);

pincount should be pinning_backends here?

This may be harmless but pinning_backends should be defined as int4
rather than int8
because BufferDesc->refcount is just defined as unsigned and it's
converted to Datum
by Int32GetDatum().

+-- complain if script is sourced in psql, rather than via CREATE EXTENSION

s/CREATE/ALTER

+\echo Use "CREATE EXTENSION pg_buffercache" to load this file. \quit

The message should be something like "ALTER EXTENSION pg_buffercache
UPDATE TO '1.1'".

+            /* might not be used, but the array is long enough */
+            values[8] = Int32GetDatum(fctx->record[i].pinning_backends);
+            nulls[8] = false;

Why is the above source comment needed?

Regards,

-- 
Fujii Masao