postgresqlrdbms

Unable to run pg_buffercache_pages() function


I am trying to explore pg_buffercache extension and facing error while using pg_buffercache_pages() function. Errors are as follow :

test=# SELECT pg_buffercache_pages();
ERROR:  return type must be a row type

test=# SELECT * FROM pg_buffercache_pages();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM pg_buffercache_pages();

                      ^

-- taken from .sql file of regress test
test=# SELECT * FROM pg_buffercache_pages() AS p (wrong int);
ERROR:  incorrect number of output arguments

I have confirmed that I am logged in as a superuser, meanwhile I have gone through the documentation but couldn't find how to run this function.


Solution

  • This function is not intended to be used directly, but via the pg_buffercache view. If you look at the definition of that view, you will see how it is intended to be called:

    \d+ pg_buffercache 
                                 View "public.pg_buffercache"
          Column      │   Type   │ Collation │ Nullable │ Default │ Storage │ Description 
    ══════════════════╪══════════╪═══════════╪══════════╪═════════╪═════════╪═════════════
     bufferid         │ integer  │           │          │         │ plain   │ 
     relfilenode      │ oid      │           │          │         │ plain   │ 
     reltablespace    │ oid      │           │          │         │ plain   │ 
     reldatabase      │ oid      │           │          │         │ plain   │ 
     relforknumber    │ smallint │           │          │         │ plain   │ 
     relblocknumber   │ bigint   │           │          │         │ plain   │ 
     isdirty          │ boolean  │           │          │         │ plain   │ 
     usagecount       │ smallint │           │          │         │ plain   │ 
     pinning_backends │ integer  │           │          │         │ plain   │ 
    View definition:
     SELECT bufferid,
        relfilenode,
        reltablespace,
        reldatabase,
        relforknumber,
        relblocknumber,
        isdirty,
        usagecount,
        pinning_backends
       FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, isdirty boolean, usagecount smallint, pinning_backends integer);
    

    The reason for that error is that the function is defined to return SETOF record, so you have to specify the columns returned by the function when you call it.