Re: [PATCHES] [HACKERS] pg_buffercache causes assertion failure
Neil Conway wrote: On Tue, 2005-05-31 at 13:07 +1200, Mark Kirkwood wrote: I did some patches for 7.4 and 8.0 a while ago (attached) - while I do not expect these to be applied Right, I don't see a need to backport this. is there somewhere for things like this to go? Pg Foundry? Of course! Thanks Mark ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] pg_buffercache causes assertion failure
On Tue, 2005-05-31 at 13:07 +1200, Mark Kirkwood wrote: > I did some patches for 7.4 and 8.0 a while ago (attached) - while I do > not expect these to be applied Right, I don't see a need to backport this. > is there somewhere for things like this to go? Pg Foundry? -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] pg_buffercache causes assertion failure
Tom Lane wrote: Mark Kirkwood <[EMAIL PROTECTED]> writes: This patch changes the use of numeric to int8 to represent the relblocknumber column. Applied, thanks. This reminds me: I did some patches for 7.4 and 8.0 a while ago (attached) - while I do not expect these to be applied (unless it's ok for contrib to get extra modules in stable releases...), is there somewhere for things like this to go? cheers Mark P.s : They are amended to use int8 too :-) diff -Naur pgsql-7.4.7.orig/contrib/Makefile pgsql-7.4.7/contrib/Makefile --- pgsql-7.4.7.orig/contrib/Makefile Fri Mar 18 11:44:25 2005 +++ pgsql-7.4.7/contrib/MakefileFri Mar 18 10:55:55 2005 @@ -25,6 +25,7 @@ noupdate\ oid2name\ pg_autovacuum \ + pg_buffercache \ pg_dumplo \ pg_logger \ pgbench \ diff -Naur pgsql-7.4.7.orig/contrib/README pgsql-7.4.7/contrib/README --- pgsql-7.4.7.orig/contrib/README Fri Mar 18 11:44:19 2005 +++ pgsql-7.4.7/contrib/README Fri Mar 18 10:55:55 2005 @@ -136,6 +136,10 @@ Automatically performs vacuum by Matthew T. O'Connor +pg_buffercache - + Real-time queries on the shared buffer cache + by Mark Kirkwood <[EMAIL PROTECTED]> + pg_dumplo - Dump large objects by Karel Zak <[EMAIL PROTECTED]> diff -Naur pgsql-7.4.7.orig/contrib/pg_buffercache/Makefile pgsql-7.4.7/contrib/pg_buffercache/Makefile --- pgsql-7.4.7.orig/contrib/pg_buffercache/MakefileThu Jan 1 12:00:00 1970 +++ pgsql-7.4.7/contrib/pg_buffercache/Makefile Fri Mar 18 10:55:55 2005 @@ -0,0 +1,17 @@ +# $PostgreSQL: pgsql/contrib/pg_buffercache/Makefile,v 1.1 2005/03/12 15:36:24 neilc Exp $ + +MODULE_big = pg_buffercache +OBJS = pg_buffercache_pages.o + +DATA_built = pg_buffercache.sql +DOCS = README.pg_buffercache + +ifdef USE_PGXS +PGXS = $(shell pg_config --pgxs) +include $(PGXS) +else +subdir = contrib/pg_buffercache +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff -Naur pgsql-7.4.7.orig/contrib/pg_buffercache/README.pg_buffercache pgsql-7.4.7/contrib/pg_buffercache/README.pg_buffercache --- pgsql-7.4.7.orig/contrib/pg_buffercache/README.pg_buffercache Thu Jan 1 12:00:00 1970 +++ pgsql-7.4.7/contrib/pg_buffercache/README.pg_buffercacheFri Mar 18 11:47:28 2005 @@ -0,0 +1,110 @@ +Pg_buffercache - Real time queries on the shared buffer cache. +-- + + This module consists of a C function 'pg_buffercache_pages()' that returns + a set of records, plus a view 'pg_buffercache' to wrapper the function. + + The intent is to do for the buffercache what pg_locks does for locks, i.e - + ability to examine what is happening at any given time without having to + restart or rebuild the server with debugging code added. + + By default public access is REVOKED from both of these, just in case there + are security issues lurking. + + +Installation + + + Build and install the main Postgresql source, then this contrib module: + + $ cd contrib/pg_buffercache + $ gmake + $ gmake install + + + To register the functions: + + $ psql -d -f pg_buffercache.sql + + +Notes +- + + The definition of the columns exposed in the view is: + + Column | references | Description + +--+ + bufferid | | Id, 1->shared_buffers. + relfilenode| pg_class.relfilenode | Refilenode of the relation. + reldatabase| pg_database.oid | Database for the relation. + relblocknumber | | Offset of the page in the relation. + isdirty| | Is the page dirty? + + + There is one row for each buffer in the shared cache. Unused buffers are + shown with all fields null except bufferid. + + Because the cache is shared by all the databases, there are pages from + relations not belonging to the current database. + + When the pg_buffercache view is accessed, internal buffer manager locks are + taken, and a copy of the buffer cache data is made for the view to display. + This ensures that the view produces a consistent set of results, while not + blocking normal buffer activity longer than necessary. Nonetheless there + could be some impact on database performance if this view is read often. + + +Sample output +- + + regression=# \d pg_buffercache; + View "public.pg_buffercache" + Column | Type | Modifiers + +-+--- + bufferid | integer | + relfilenode| oid | + reldatabase| oid | + relblocknumber | bigint | + isdirty| boolean | + View definition: + SELECT p.bufferid, p.relfilenode, p.reldatabase, + p.relblocknumber, p.isdirty + FROM pg_buffercache_p
Re: [PATCHES] [HACKERS] pg_buffercache causes assertion failure
Mark Kirkwood <[EMAIL PROTECTED]> writes: > This patch changes the use of numeric to int8 to represent the > relblocknumber column. Applied, thanks. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] [HACKERS] pg_buffercache causes assertion failure
Mark Kirkwood wrote: Mark Kirkwood wrote: I couldn't use int4 as the underlying datatype is unsigned int (not available as exposed Pg type). However, using int8 sounds promising (is int8 larger than unsigned int on 64-bit platforms?). Blocknumber is defined as uint32 in block.h - so should always be safe to represent as an int8 I am thinking. I will look at patching pg_buffercache, changing numeric -> int8 for the relblocknumber column. This seems a tidier solution than using numeric, and loses the numeric overhead. This patch changes the use of numeric to int8 to represent the relblocknumber column. regards Mark diff -Ncar pgsql.orig/contrib/pg_buffercache/README.pg_buffercache pgsql/contrib/pg_buffercache/README.pg_buffercache *** pgsql.orig/contrib/pg_buffercache/README.pg_buffercache Tue May 31 11:02:41 2005 --- pgsql/contrib/pg_buffercache/README.pg_buffercache Tue May 31 11:05:48 2005 *** *** 66,78 relfilenode| oid | reltablespace | oid | reldatabase| oid | !relblocknumber | numeric | isdirty| boolean | View definition: SELECT p.bufferid, p.relfilenode, p.reltablespace, p.reldatabase, p.relblocknumber, p.isdirty FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, ! reltablespace oid, reldatabase oid, relblocknumber numeric(10,0), isdirty boolean); regression=# SELECT c.relname, count(*) AS buffers --- 66,78 relfilenode| oid | reltablespace | oid | reldatabase| oid | !relblocknumber | bigint | isdirty| boolean | View definition: SELECT p.bufferid, p.relfilenode, p.reltablespace, p.reldatabase, p.relblocknumber, p.isdirty FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, ! reltablespace oid, reldatabase oid, relblocknumber bigint, isdirty boolean); regression=# SELECT c.relname, count(*) AS buffers diff -Ncar pgsql.orig/contrib/pg_buffercache/pg_buffercache.sql.in pgsql/contrib/pg_buffercache/pg_buffercache.sql.in *** pgsql.orig/contrib/pg_buffercache/pg_buffercache.sql.in Tue May 31 11:02:41 2005 --- pgsql/contrib/pg_buffercache/pg_buffercache.sql.in Tue May 31 09:15:03 2005 *** *** 11,17 CREATE VIEW pg_buffercache AS SELECT P.* FROM pg_buffercache_pages() AS P (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, !relblocknumber numeric(10), isdirty bool); -- Don't want these to be available at public. REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC; --- 11,17 CREATE VIEW pg_buffercache AS SELECT P.* FROM pg_buffercache_pages() AS P (bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, !relblocknumber int8, isdirty bool); -- Don't want these to be available at public. REVOKE ALL ON FUNCTION pg_buffercache_pages() FROM PUBLIC; diff -Ncar pgsql.orig/contrib/pg_buffercache/pg_buffercache_pages.c pgsql/contrib/pg_buffercache/pg_buffercache_pages.c *** pgsql.orig/contrib/pg_buffercache/pg_buffercache_pages.cTue May 31 11:02:41 2005 --- pgsql/contrib/pg_buffercache/pg_buffercache_pages.c Tue May 31 11:23:46 2005 *** *** 93,99 TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase", OIDOID, -1, 0); TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relblockbumber", ! NUMERICOID, -1, 0); TupleDescInitEntry(tupledesc, (AttrNumber) 6, "isdirty", BOOLOID, -1, 0); --- 93,99 TupleDescInitEntry(tupledesc, (AttrNumber) 4, "reldatabase", OIDOID, -1, 0); TupleDescInitEntry(tupledesc, (AttrNumber) 5, "relblockbumber", ! INT8OID, -1, 0); TupleDescInitEntry(tupledesc, (AttrNumber) 6, "isdirty", BOOLOID, -1, 0); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
