[PATCHES] TODO Item - Add system view to show free space map contents
This patch implements a view to display the free space map contents - e.g : regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes FROM pg_freespacemap m INNER JOIN pg_class c ON c.relfilenode = m.relfilenode LIMIT 10; relname | relblocknumber | blockfreebytes ++ sql_features| 5 | 2696 sql_implementation_info | 0 | 7104 sql_languages | 0 | 8016 sql_packages| 0 | 7376 sql_sizing | 0 | 6032 pg_authid | 0 | 7424 pg_toast_2618 | 13 | 4588 pg_toast_2618 | 12 | 1680 pg_toast_2618 | 10 | 1436 pg_toast_2618 | 7 | 1136 (10 rows) [I found being able to display the FSM pretty cool, even if I say so myself]. It is written as a contrib module (similar to pg_buffercache) so as to make any revisions non-initdb requiring. The code needs to know about several of the (currently) internal data structures in freespace.c, so I moved these into freespace.h. Similarly for the handy macros to actually compute the free space. Let me know if this was the wrong way to proceed! Additionally access to the FSM pointer itself is required, I added a function in freespace.c to return this, rather than making it globally visible, again if the latter is a better approach, it is easily changed. cheers Mark P.s : Currently don't have access to a windows box, so had to just 'take a stab' at what DLLIMPORTs were required. diff -Ncar pgsql.orig/contrib/pg_freespacemap/Makefile pgsql/contrib/pg_freespacemap/Makefile *** pgsql.orig/contrib/pg_freespacemap/Makefile Thu Jan 1 12:00:00 1970 --- pgsql/contrib/pg_freespacemap/Makefile Thu Oct 27 17:52:10 2005 *** *** 0 --- 1,17 + # $PostgreSQL$ + + MODULE_big = pg_freespacemap + OBJS = pg_freespacemap.o + + DATA_built = pg_freespacemap.sql + DOCS = README.pg_freespacemap + + ifdef USE_PGXS + PGXS := $(shell pg_config --pgxs) + include $(PGXS) + else + subdir = contrib/pg_freespacemap + top_builddir = ../.. + include $(top_builddir)/src/Makefile.global + include $(top_srcdir)/contrib/contrib-global.mk + endif diff -Ncar pgsql.orig/contrib/pg_freespacemap/README.pg_freespacemap pgsql/contrib/pg_freespacemap/README.pg_freespacemap *** pgsql.orig/contrib/pg_freespacemap/README.pg_freespacemap Thu Jan 1 12:00:00 1970 --- pgsql/contrib/pg_freespacemap/README.pg_freespacemapThu Oct 27 18:06:20 2005 *** *** 0 --- 1,98 + Pg_freespacemap - Real time queries on the free space map (FSM). + --- + + This module consists of a C function 'pg_freespacemap()' that returns + a set of records, and a view 'pg_freespacemap' to wrapper the function. + + The module provides the ability to examine the contents of the free space + map, without having to restart or rebuild the server with additional + debugging code. + + 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_freespacemap + $ gmake + $ gmake install + + + To register the functions: + + $ psql -d -f pg_freespacemap.sql + + + Notes + - + + The definition of the columns exposed in the view is: + +Column | references | Description + +--+ +blockid| | Id, 1.. max_fsm_pages +relfilenode| pg_class.relfilenode | Refilenode of the relation. +reltablespace | pg_tablespace.oid| Tablespace oid of the relation. +reldatabase| pg_database.oid | Database for the relation. +relblocknumber | | Offset of the page in the relation. +blockfreebytes | | Free bytes in the block/page. + + + There is one row for each page in the free space map. + + Because the map is shared by all the databases, there are pages from + relations not belonging to the current database. + + When the pg_freespacemap view is accessed, internal free space map locks are + taken, and a copy of the map data is made for the view to display. + This ensures that the view produces a consistent set of results, while not + blocking normal activity longer than necessary. Nonetheless there + could be some impact on database performance if this view is read often. + + + Sample output + - + + regression=# \d pg_freespacemap + View "public.pg_freespacemap" +
Re: [PATCHES] TODO Item - Add system view to show free space map
Jim C. Nasby wrote: Shouldn't the DDL in pg_freespacemap.sql.in be wrapped in a transaction? Specifically I'm considering the case of the script stopping before the REVOKEs. That's nice, (probably should have done it in pg_buffercache )! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] TODO Item - Add system view to show free space map
Want to host it on pgfoundry until 8.2 is released? Mark Kirkwood wrote: This patch implements a view to display the free space map contents - e.g : regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes FROM pg_freespacemap m INNER JOIN pg_class c ON c.relfilenode = m.relfilenode LIMIT 10; relname | relblocknumber | blockfreebytes ++ sql_features| 5 | 2696 sql_implementation_info | 0 | 7104 sql_languages | 0 | 8016 sql_packages| 0 | 7376 sql_sizing | 0 | 6032 pg_authid | 0 | 7424 pg_toast_2618 | 13 | 4588 pg_toast_2618 | 12 | 1680 pg_toast_2618 | 10 | 1436 pg_toast_2618 | 7 | 1136 (10 rows) [I found being able to display the FSM pretty cool, even if I say so myself]. It is written as a contrib module (similar to pg_buffercache) so as to make any revisions non-initdb requiring. The code needs to know about several of the (currently) internal data structures in freespace.c, so I moved these into freespace.h. Similarly for the handy macros to actually compute the free space. Let me know if this was the wrong way to proceed! Additionally access to the FSM pointer itself is required, I added a function in freespace.c to return this, rather than making it globally visible, again if the latter is a better approach, it is easily changed. cheers Mark P.s : Currently don't have access to a windows box, so had to just 'take a stab' at what DLLIMPORTs were required. diff -Ncar pgsql.orig/contrib/pg_freespacemap/Makefile pgsql/contrib/pg_freespacemap/Makefile *** pgsql.orig/contrib/pg_freespacemap/Makefile Thu Jan 1 12:00:00 1970 --- pgsql/contrib/pg_freespacemap/Makefile Thu Oct 27 17:52:10 2005 *** *** 0 --- 1,17 + # $PostgreSQL$ + + MODULE_big = pg_freespacemap + OBJS = pg_freespacemap.o + + DATA_built = pg_freespacemap.sql + DOCS = README.pg_freespacemap + + ifdef USE_PGXS + PGXS := $(shell pg_config --pgxs) + include $(PGXS) + else + subdir = contrib/pg_freespacemap + top_builddir = ../.. + include $(top_builddir)/src/Makefile.global + include $(top_srcdir)/contrib/contrib-global.mk + endif diff -Ncar pgsql.orig/contrib/pg_freespacemap/README.pg_freespacemap pgsql/contrib/pg_freespacemap/README.pg_freespacemap *** pgsql.orig/contrib/pg_freespacemap/README.pg_freespacemap Thu Jan 1 12:00:00 1970 --- pgsql/contrib/pg_freespacemap/README.pg_freespacemapThu Oct 27 18:06:20 2005 *** *** 0 --- 1,98 + Pg_freespacemap - Real time queries on the free space map (FSM). + --- + + This module consists of a C function 'pg_freespacemap()' that returns + a set of records, and a view 'pg_freespacemap' to wrapper the function. + + The module provides the ability to examine the contents of the free space + map, without having to restart or rebuild the server with additional + debugging code. + + 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_freespacemap + $ gmake + $ gmake install + + + To register the functions: + + $ psql -d -f pg_freespacemap.sql + + + Notes + - + + The definition of the columns exposed in the view is: + +Column | references | Description + +--+ +blockid| | Id, 1.. max_fsm_pages +relfilenode| pg_class.relfilenode | Refilenode of the relation. +reltablespace | pg_tablespace.oid| Tablespace oid of the relation. +reldatabase| pg_database.oid | Database for the relation. +relblocknumber | | Offset of the page in the relation. +blockfreebytes | | Free bytes in the block/page. + + + There is one row for each page in the free space map. + + Because the map is shared by all the databases, there are pages from + relations not belonging to the current database. + + When the pg_freespacemap view is accessed, internal free space map locks are + taken, and a copy of the map data is made for the view to display. + This ensures that the view produces a consistent set of results, while not + blocking normal activity longer than necessary. Nonetheless there + could be some impact on database
Re: [PATCHES] TODO Item - Add system view to show free space map
Christopher Kings-Lynne wrote: Want to host it on pgfoundry until 8.2 is released? Absolutely - I'll let it run the gauntlet of freedback to fix the silly mistakes I put in :-), then do patches for 8.1 and 8.0 (maybe 7.4 and 7.3 as well - if it rains a lot). cheers Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] TODO Item - Add system view to show free space map
On Fri, 2005-10-28 at 13:21 +1300, Mark Kirkwood wrote: > regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes > FROM pg_freespacemap m INNER JOIN pg_class c > ON c.relfilenode = m.relfilenode LIMIT 10; > relname | relblocknumber | blockfreebytes > ++ > sql_features| 5 | 2696 > sql_implementation_info | 0 | 7104 > sql_languages | 0 | 8016 > sql_packages| 0 | 7376 > sql_sizing | 0 | 6032 > pg_authid | 0 | 7424 > pg_toast_2618 | 13 | 4588 > pg_toast_2618 | 12 | 1680 > pg_toast_2618 | 10 | 1436 > pg_toast_2618 | 7 | 1136 > (10 rows) > > [I found being able to display the FSM pretty cool, even if I say so > myself]. I like this, but not because I want to read it myself, but because I want to make autovacuum responsible for re-allocating free space when it runs out. This way we can have an autoFSM feature in 8.2 Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] TODO Item - Add system view to show free space map
Simon Riggs wrote: > On Fri, 2005-10-28 at 13:21 +1300, Mark Kirkwood wrote: > > > regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes > > FROM pg_freespacemap m INNER JOIN pg_class c > > ON c.relfilenode = m.relfilenode LIMIT 10; > > > I like this, but not because I want to read it myself, but because I > want to make autovacuum responsible for re-allocating free space when it > runs out. This way we can have an autoFSM feature in 8.2 What do you mean, re-allocating free space? I don't understand what you are proposing. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "Use it up, wear it out, make it do, or do without" ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] TODO Item - Add system view to show free space map
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Simon Riggs wrote: >> I like this, but not because I want to read it myself, but because I >> want to make autovacuum responsible for re-allocating free space when it >> runs out. This way we can have an autoFSM feature in 8.2 > What do you mean, re-allocating free space? I don't understand what you > are proposing. And even less why autovacuum would go through a view to get at the info. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] TODO Item - Add system view to show free space map
Simon Riggs wrote: I like this, but not because I want to read it myself, but because I want to make autovacuum responsible for re-allocating free space when it runs out. This way we can have an autoFSM feature in 8.2 Not wanting to denigrate value of the interesting but slightly OT direction this thread has taken - but does anybody want to comment/review the patch itself :-) ? Cheers Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] TODO Item - Add system view to show free space map
Mark Kirkwood wrote: > Simon Riggs wrote: > > > > > > I like this, but not because I want to read it myself, but because I > > want to make autovacuum responsible for re-allocating free space when it > > runs out. This way we can have an autoFSM feature in 8.2 > > > > > > Not wanting to denigrate value of the interesting but slightly OT > direction this thread has taken - but does anybody want to > comment/review the patch itself :-) ? I saw this question about a transaction block and your reply: http://archives.postgresql.org/pgsql-patches/2005-10/msg00226.php but no new patch. I know someone suggested pgfoundry but it seems most natural in /contrib. Do you want to update the patch? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] TODO Item - Add system view to show free space map
Bruce Momjian wrote: Mark Kirkwood wrote: Simon Riggs wrote: I like this, but not because I want to read it myself, but because I want to make autovacuum responsible for re-allocating free space when it runs out. This way we can have an autoFSM feature in 8.2 Not wanting to denigrate value of the interesting but slightly OT direction this thread has taken - but does anybody want to comment/review the patch itself :-) ? I saw this question about a transaction block and your reply: http://archives.postgresql.org/pgsql-patches/2005-10/msg00226.php but no new patch. I know someone suggested pgfoundry but it seems most natural in /contrib. Do you want to update the patch? In the expectation of further revisions, I was going to batch that one in with the 'rest' - given that there have not been any, I'll submit a revised patch. Cheers Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] TODO Item - Add system view to show free space map
Mark Kirkwood wrote: Bruce Momjian wrote: Mark Kirkwood wrote: Simon Riggs wrote: I like this, but not because I want to read it myself, but because I want to make autovacuum responsible for re-allocating free space when it runs out. This way we can have an autoFSM feature in 8.2 Not wanting to denigrate value of the interesting but slightly OT direction this thread has taken - but does anybody want to comment/review the patch itself :-) ? I saw this question about a transaction block and your reply: http://archives.postgresql.org/pgsql-patches/2005-10/msg00226.php but no new patch. I know someone suggested pgfoundry but it seems most natural in /contrib. Do you want to update the patch? In the expectation of further revisions, I was going to batch that one in with the 'rest' - given that there have not been any, I'll submit a revised patch. Here it is - I seem to have had trouble sending any attachments to this list recently. Bruce the patch (sent privately), so its in the patches queue, but thought I would have another go at getting it to -patches so others can review it more easily! cheers Mark contrib-freespacemap.patch.gz Description: application/gzip ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TODO Item - Add system view to show free space map
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Mark Kirkwood wrote: > Mark Kirkwood wrote: > > Bruce Momjian wrote: > > > >> Mark Kirkwood wrote: > >> > >>> Simon Riggs wrote: > >>> > > I like this, but not because I want to read it myself, but because I > want to make autovacuum responsible for re-allocating free space > when it > runs out. This way we can have an autoFSM feature in 8.2 > > > >>> > >>> Not wanting to denigrate value of the interesting but slightly OT > >>> direction this thread has taken - but does anybody want to > >>> comment/review the patch itself :-) ? > >> > >> > >> > >> I saw this question about a transaction block and your reply: > >> > >> http://archives.postgresql.org/pgsql-patches/2005-10/msg00226.php > >> > >> but no new patch. I know someone suggested pgfoundry but it seems most > >> natural in /contrib. Do you want to update the patch? > >> > > > > In the expectation of further revisions, I was going to batch that one > > in with the 'rest' - given that there have not been any, I'll submit a > > revised patch. > > > > Here it is - I seem to have had trouble sending any attachments to this > list recently. Bruce the patch (sent privately), so its in the patches > queue, but thought I would have another go at getting it to -patches so > others can review it more easily! > > cheers > > Mark [ application/gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] TODO Item - Add system view to show free space map
Patch applied. Thanks. --- Mark Kirkwood wrote: > Mark Kirkwood wrote: > > Bruce Momjian wrote: > > > >> Mark Kirkwood wrote: > >> > >>> Simon Riggs wrote: > >>> > > I like this, but not because I want to read it myself, but because I > want to make autovacuum responsible for re-allocating free space > when it > runs out. This way we can have an autoFSM feature in 8.2 > > > >>> > >>> Not wanting to denigrate value of the interesting but slightly OT > >>> direction this thread has taken - but does anybody want to > >>> comment/review the patch itself :-) ? > >> > >> > >> > >> I saw this question about a transaction block and your reply: > >> > >> http://archives.postgresql.org/pgsql-patches/2005-10/msg00226.php > >> > >> but no new patch. I know someone suggested pgfoundry but it seems most > >> natural in /contrib. Do you want to update the patch? > >> > > > > In the expectation of further revisions, I was going to batch that one > > in with the 'rest' - given that there have not been any, I'll submit a > > revised patch. > > > > Here it is - I seem to have had trouble sending any attachments to this > list recently. Bruce the patch (sent privately), so its in the patches > queue, but thought I would have another go at getting it to -patches so > others can review it more easily! > > cheers > > Mark [ application/gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] TODO Item - Add system view to show free space map contents
Shouldn't the DDL in pg_freespacemap.sql.in be wrapped in a transaction? Specifically I'm considering the case of the script stopping before the REVOKEs. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
