On Wed, Nov 9, 2011 at 7:58 AM, Alvaro Herrera <alvhe...@commandprompt.com> wrote: > > Excerpts from Jaime Casanova's message of mar nov 08 18:12:25 -0300 2011: >> On Sun, Nov 6, 2011 at 5:38 AM, Magnus Hagander <mag...@hagander.net> wrote: >> > >> > Looks pretty useful. >> >> thanks for the review, attached is a new version of it > > Note that AFAIK you shouldn't update the 1.0 extension script ... you > have to create a 1.1 version (or whatever), update the default version > in the control file, and create an 1.0--1.1 script to upgrade from the > original version to 1.1. >
good point... fixed that... a question i have is: are we supposed to let the old script (1.0) around? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile new file mode 100644 index 13ba6d3..63fab95 *** a/contrib/pageinspect/Makefile --- b/contrib/pageinspect/Makefile *************** MODULE_big = pageinspect *** 4,10 **** OBJS = rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o EXTENSION = pageinspect ! DATA = pageinspect--1.0.sql pageinspect--unpackaged--1.0.sql ifdef USE_PGXS PG_CONFIG = pg_config --- 4,12 ---- OBJS = rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o EXTENSION = pageinspect ! DATA = pageinspect--1.0.sql pageinspect--1.1.sql \ ! pageinspect--1.0--1.1.sql \ ! pageinspect--unpackaged--1.0.sql ifdef USE_PGXS PG_CONFIG = pg_config diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c new file mode 100644 index dbb2158..8be21ed *** a/contrib/pageinspect/btreefuncs.c --- b/contrib/pageinspect/btreefuncs.c *************** *** 34,39 **** --- 34,40 ---- #include "utils/builtins.h" #include "utils/rel.h" + #include "btreefuncs.h" extern Datum bt_metap(PG_FUNCTION_ARGS); extern Datum bt_page_items(PG_FUNCTION_ARGS); *************** GetBTPageStatistics(BlockNumber blkno, B *** 155,160 **** --- 156,204 ---- stat->avg_item_size = 0; } + /*------------------------------------------------ + * GetBTRelationFreeSpace + * + * Get the free space for a btree index. + * This is a helper function for relation_free_space() + *------------------------------------------------ + */ + float4 + GetBTRelationFreeSpace(Relation rel) + { + BTPageStat stat; + + Buffer buffer; + BlockNumber blkno; + BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel); + Size free_space = 0; + double free_percent = 0; + + BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD); + + /* Skip page 0 because it is a metapage */ + for (blkno = 1; blkno < totalBlcksInRelation; blkno++) + { + buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy); + /* + * get the statistics of the indexes and use that info + * to determine free space on the page + */ + GetBTPageStatistics(blkno, buffer, &stat); + if (stat.type == 'd') + free_space += stat.page_size; + else + free_space += stat.free_size; + + ReleaseBuffer(buffer); + } + + if (totalBlcksInRelation > 1) + free_percent = ((float4) free_space) / ((totalBlcksInRelation - 1) * BLCKSZ); + return free_percent; + } + + /* ----------------------------------------------- * bt_page() * diff --git a/contrib/pageinspect/btreefuncs.h b/contrib/pageinspect/btreefuncs.h new file mode 100644 index ...549f878 *** a/contrib/pageinspect/btreefuncs.h --- b/contrib/pageinspect/btreefuncs.h *************** *** 0 **** --- 1,5 ---- + /* + * contrib/pageinspect/btreefuncs.h + */ + + float4 GetBTRelationFreeSpace(Relation); diff --git a/contrib/pageinspect/heapfuncs.c b/contrib/pageinspect/heapfuncs.c new file mode 100644 index fa50655..e7436fb *** a/contrib/pageinspect/heapfuncs.c --- b/contrib/pageinspect/heapfuncs.c *************** *** 28,33 **** --- 28,36 ---- #include "funcapi.h" #include "utils/builtins.h" #include "miscadmin.h" + #include "storage/bufmgr.h" + + #include "heapfuncs.h" Datum heap_page_items(PG_FUNCTION_ARGS); *************** bits_to_text(bits8 *bits, int len) *** 55,60 **** --- 58,96 ---- } + /* + * GetHeapRelationFreeSpace() + * + * Get the free space for a heap relation. + * This is a helper function for relation_free_space() + */ + float4 + GetHeapRelationFreeSpace(Relation rel) + { + Buffer buffer; + Page page; + BlockNumber blkno; + BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel); + Size free_space = 0; + double free_percent = 0; + + BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD); + + for (blkno = 0; blkno < totalBlcksInRelation; blkno++) + { + buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy); + page = BufferGetPage(buffer); + free_space += PageGetHeapFreeSpace(page); + + ReleaseBuffer(buffer); + } + + if (totalBlcksInRelation > 0) + free_percent = ((float4) free_space) / (totalBlcksInRelation * BLCKSZ); + return free_percent; + } + + /* * heap_page_items * diff --git a/contrib/pageinspect/heapfuncs.h b/contrib/pageinspect/heapfuncs.h new file mode 100644 index ...17b7cb3 *** a/contrib/pageinspect/heapfuncs.h --- b/contrib/pageinspect/heapfuncs.h *************** *** 0 **** --- 1,5 ---- + /* + * contrib/pageinspect/heapfuncs.h + */ + + float4 GetHeapRelationFreeSpace(Relation); diff --git a/contrib/pageinspect/pageinspect--1.0--1.1.sql b/contrib/pageinspect/pageinspect--1.0--1.1.sql new file mode 100644 index ...c97aeba *** a/contrib/pageinspect/pageinspect--1.0--1.1.sql --- b/contrib/pageinspect/pageinspect--1.0--1.1.sql *************** *** 0 **** --- 1,12 ---- + /* contrib/pageinspect/pageinspect--1.0--1.1.sql */ + + -- complain if script is sourced in psql, rather than via CREATE EXTENSION + \echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.1'" to load this file. \quit + + -- + -- relation_free_space() + -- + CREATE FUNCTION relation_free_space(IN relname text) + RETURNS real + AS 'MODULE_PATHNAME', 'relation_free_space' + LANGUAGE C STRICT; diff --git a/contrib/pageinspect/pageinspect--1.1.sql b/contrib/pageinspect/pageinspect--1.1.sql new file mode 100644 index ...035195b *** a/contrib/pageinspect/pageinspect--1.1.sql --- b/contrib/pageinspect/pageinspect--1.1.sql *************** *** 0 **** --- 1,115 ---- + /* contrib/pageinspect/pageinspect--1.1.sql */ + + -- complain if script is sourced in psql, rather than via CREATE EXTENSION + \echo Use "CREATE EXTENSION pageinspect" to load this file. \quit + + -- + -- get_raw_page() + -- + CREATE FUNCTION get_raw_page(text, int4) + RETURNS bytea + AS 'MODULE_PATHNAME', 'get_raw_page' + LANGUAGE C STRICT; + + CREATE FUNCTION get_raw_page(text, text, int4) + RETURNS bytea + AS 'MODULE_PATHNAME', 'get_raw_page_fork' + LANGUAGE C STRICT; + + -- + -- page_header() + -- + CREATE FUNCTION page_header(IN page bytea, + OUT lsn text, + OUT tli smallint, + OUT flags smallint, + OUT lower smallint, + OUT upper smallint, + OUT special smallint, + OUT pagesize smallint, + OUT version smallint, + OUT prune_xid xid) + AS 'MODULE_PATHNAME', 'page_header' + LANGUAGE C STRICT; + + -- + -- heap_page_items() + -- + CREATE FUNCTION heap_page_items(IN page bytea, + OUT lp smallint, + OUT lp_off smallint, + OUT lp_flags smallint, + OUT lp_len smallint, + OUT t_xmin xid, + OUT t_xmax xid, + OUT t_field3 int4, + OUT t_ctid tid, + OUT t_infomask2 integer, + OUT t_infomask integer, + OUT t_hoff smallint, + OUT t_bits text, + OUT t_oid oid) + RETURNS SETOF record + AS 'MODULE_PATHNAME', 'heap_page_items' + LANGUAGE C STRICT; + + -- + -- bt_metap() + -- + CREATE FUNCTION bt_metap(IN relname text, + OUT magic int4, + OUT version int4, + OUT root int4, + OUT level int4, + OUT fastroot int4, + OUT fastlevel int4) + AS 'MODULE_PATHNAME', 'bt_metap' + LANGUAGE C STRICT; + + -- + -- bt_page_stats() + -- + CREATE FUNCTION bt_page_stats(IN relname text, IN blkno int4, + OUT blkno int4, + OUT type "char", + OUT live_items int4, + OUT dead_items int4, + OUT avg_item_size int4, + OUT page_size int4, + OUT free_size int4, + OUT btpo_prev int4, + OUT btpo_next int4, + OUT btpo int4, + OUT btpo_flags int4) + AS 'MODULE_PATHNAME', 'bt_page_stats' + LANGUAGE C STRICT; + + -- + -- bt_page_items() + -- + CREATE FUNCTION bt_page_items(IN relname text, IN blkno int4, + OUT itemoffset smallint, + OUT ctid tid, + OUT itemlen smallint, + OUT nulls bool, + OUT vars bool, + OUT data text) + RETURNS SETOF record + AS 'MODULE_PATHNAME', 'bt_page_items' + LANGUAGE C STRICT; + + -- + -- fsm_page_contents() + -- + CREATE FUNCTION fsm_page_contents(IN page bytea) + RETURNS text + AS 'MODULE_PATHNAME', 'fsm_page_contents' + LANGUAGE C STRICT; + + -- + -- relation_free_space() + -- + CREATE FUNCTION relation_free_space(IN relname text) + RETURNS real + AS 'MODULE_PATHNAME', 'relation_free_space' + LANGUAGE C STRICT; diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control new file mode 100644 index f9da0e8..a412cf1 *** a/contrib/pageinspect/pageinspect.control --- b/contrib/pageinspect/pageinspect.control *************** *** 1,5 **** # pageinspect extension comment = 'inspect the contents of database pages at a low level' ! default_version = '1.0' module_pathname = '$libdir/pageinspect' relocatable = true --- 1,5 ---- # pageinspect extension comment = 'inspect the contents of database pages at a low level' ! default_version = '1.1' module_pathname = '$libdir/pageinspect' relocatable = true diff --git a/contrib/pageinspect/rawpage.c b/contrib/pageinspect/rawpage.c new file mode 100644 index 362ad84..988d1e3 *** a/contrib/pageinspect/rawpage.c --- b/contrib/pageinspect/rawpage.c *************** *** 23,33 **** --- 23,37 ---- #include "utils/builtins.h" #include "utils/rel.h" + #include "btreefuncs.h" + #include "heapfuncs.h" + PG_MODULE_MAGIC; Datum get_raw_page(PG_FUNCTION_ARGS); Datum get_raw_page_fork(PG_FUNCTION_ARGS); Datum page_header(PG_FUNCTION_ARGS); + Datum relation_free_space(PG_FUNCTION_ARGS); static bytea *get_raw_page_internal(text *relname, ForkNumber forknum, BlockNumber blkno); *************** page_header(PG_FUNCTION_ARGS) *** 227,229 **** --- 231,285 ---- PG_RETURN_DATUM(result); } + + + /* + * relation_free_space + * + * Returns the percentage of free space for a given relation. + * Supported relation types are tables and indexes. TOAST is not + * considered/handled. + */ + PG_FUNCTION_INFO_V1(relation_free_space); + + Datum + relation_free_space(PG_FUNCTION_ARGS) + { + text *relname = PG_GETARG_TEXT_P(0); + RangeVar *relrv; + Relation rel; + float4 free_space = 0; + + relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname)); + rel = relation_openrv(relrv, AccessShareLock); + + /* Only allow tables or indexes */ + if (rel->rd_rel->relkind != RELKIND_INDEX && rel->rd_rel->relkind != RELKIND_RELATION) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot get free space from object \"%s\"", + RelationGetRelationName(rel)))); + + /* + * Reject attempts to read non-local temporary relations. We would be + * likely to get wrong data, since we have no visibility into the owning + * session's local buffers. + */ + if (RELATION_IS_OTHER_TEMP(rel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot access temporary tables of other sessions"))); + + switch (rel->rd_rel->relkind) + { + case RELKIND_RELATION: + free_space = GetHeapRelationFreeSpace(rel); + break; + case RELKIND_INDEX: + free_space = GetBTRelationFreeSpace(rel); + break; + } + relation_close(rel, AccessShareLock); + + PG_RETURN_FLOAT4(free_space); + } diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgml new file mode 100644 index acbb05b..49708c3 *** a/doc/src/sgml/pageinspect.sgml --- b/doc/src/sgml/pageinspect.sgml *************** test=# SELECT * FROM bt_page_items('pg_c *** 196,201 **** --- 196,216 ---- </para> </listitem> </varlistentry> + + <varlistentry> + <term> + <function>relation_free_space(relname text) returns float</function> + </term> + + <listitem> + <para> + <function>relation_free_space</function> returns the total amount of free + free space in a table or index. The entire relation is scanned in order + to compute this amount, which may generate a large amount of disk reads. + Information stored in <acronym>TOAST</> tables is not included. + </para> + </listitem> + </varlistentry> </variablelist> </sect2>
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers