Thread: Pg_buffercache tidy

Pg_buffercache tidy

From
Mark Kirkwood
Date:
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


Re: Pg_buffercache tidy

From
Bruce Momjian
Date:
Patch applied.  Thanks.

---------------------------------------------------------------------------


Mark Kirkwood wrote:
> 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
>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +