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

Reply via email to