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.
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.