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
