This patch wraps the DDL in a BEGIN...COMMIT (as suggested by Jim for
pg_freespacemap a while ago).
In addition I have amended the example queries to correctly project out
only relations in the current database (relations with the same name
from different databases were getting counted together).
Cheers
Mark
Index: pg_buffercache.sql.in
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/pg_buffercache/pg_buffercache.sql.in,v
retrieving revision 1.3
diff -c -r1.3 pg_buffercache.sql.in
*** pg_buffercache.sql.in 27 Feb 2006 16:09:48 -0000 1.3
--- pg_buffercache.sql.in 19 Mar 2006 10:45:06 -0000
***************
*** 1,4 ****
--- 1,5 ----
-- Adjust this setting to control where the objects get created.
+ BEGIN;
SET search_path = public;
-- Register the function.
***************
*** 16,18 ****
--- 17,21 ----
-- Don't want these to be available at public.
REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC;
REVOKE ALL ON pg_buffercache FROM PUBLIC;
+
+ COMMIT;
Index: README.pg_buffercache
===================================================================
RCS file: /projects/cvsroot/pgsql/contrib/pg_buffercache/README.pg_buffercache,v
retrieving revision 1.2
diff -c -r1.2 README.pg_buffercache
*** README.pg_buffercache 31 May 2005 00:07:47 -0000 1.2
--- README.pg_buffercache 19 Mar 2006 10:55:44 -0000
***************
*** 76,83 ****
isdirty boolean);
regression=# SELECT c.relname, count(*) AS buffers
! FROM pg_class c, pg_buffercache b
! WHERE b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;
relname | buffers
--- 76,84 ----
isdirty boolean);
regression=# SELECT c.relname, count(*) AS buffers
! FROM pg_class c INNER JOIN pg_buffercache b
! ON b.relfilenode = c.relfilenode INNER JOIN pg_database d
! ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;
relname | buffers