On Sun, Nov 6, 2011 at 5:38 AM, Magnus Hagander <[email protected]> wrote:
>
> Looks pretty useful.
>
thanks for the review, attached is a new version of it
> One quick stylistic comment - we don't generally use "* 1.0" to turn
> an int into a double - just use a cast.
>
ok
>
> Hooking into a ring buffer seems like an almost requirement before you
> can run this on a larger production system, wouldn't it? I don't know
> how hard that is code-wise, but it certainly seems worthwhile.
>
seems it wasn't too difficult... i just have to indicate the right
buffer access strategy so it's using a ring buffer now
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
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.sql b/contrib/pageinspect/pageinspect--1.0.sql
new file mode 100644
index 5613956..4502a13
*** a/contrib/pageinspect/pageinspect--1.0.sql
--- b/contrib/pageinspect/pageinspect--1.0.sql
*************** CREATE FUNCTION fsm_page_contents(IN pag
*** 105,107 ****
--- 105,115 ----
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--unpackaged--1.0.sql b/contrib/pageinspect/pageinspect--unpackaged--1.0.sql
new file mode 100644
index 13e2167..ccfe6e4
*** a/contrib/pageinspect/pageinspect--unpackaged--1.0.sql
--- b/contrib/pageinspect/pageinspect--unpackaged--1.0.sql
*************** ALTER EXTENSION pageinspect ADD function
*** 29,31 ****
--- 29,32 ----
ALTER EXTENSION pageinspect ADD function bt_page_stats(text,integer);
ALTER EXTENSION pageinspect ADD function bt_page_items(text,integer);
ALTER EXTENSION pageinspect ADD function fsm_page_contents(bytea);
+ ALTER EXTENSION pageinspect ADD function relation_free_space(integer);
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
