Hello, I've brought visibilitymap extentions for pg_freespacemap
and pgstattuple.
At Mon, 08 Jul 2013 16:59:05 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI
<horiguchi.kyot...@lab.ntt.co.jp> wrote in
<20130708.165905.118860769.horiguchi.kyot...@lab.ntt.co.jp>
> I'll come again with the first implementation of it. And as for
> pg_freespacemap, I'll keep the current direction - adding column
> to present output records format of pg_freespace(). And
> documentation, if possible.
pg_freespace_vm_v2.patch:
Interface has been changed from the first patch. The version of
pg_freespace() provided with vm information is named
pg_freespace_with_vminfo() and shows output like following.
| postgres=# select * from pg_freespace_with_vminfo('t'::regclass) limit 10;
| blkno | avail | is_all_visible
| -------+-------+----------------
| 0 | 64 | t
| 1 | 32 | t
| 2 | 96 | t
| 3 | 64 | t
| 4 | 96 | t
| 5 | 96 | t
| 6 | 128 | t
| 7 | 32 | t
| 8 | 96 | t
pgstattuple_vm_v1.patch:
The first version of VM extension for pgstattuple. According to
the previous discussion, the added column is named
'all_visible_percent'.
| postgres=# select * from pgstattuple('t');
| -[ RECORD 1 ]-------+---------
| table_len | 71770112
| tuple_count | 989859
| tuple_len | 31675488
| tuple_percent | 44.13
| dead_tuple_count | 99
| dead_tuple_len | 3168
| dead_tuple_percent | 0
| free_space | 31886052
| free_percent | 44.43
| all_visible_percent | 99.98
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index b2e3ba3..d794df2 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,7 +4,7 @@ MODULE_big = pg_freespacemap
OBJS = pg_freespacemap.o
EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.0.sql pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.0--1.1.sql b/contrib/pg_freespacemap/pg_freespacemap--1.0--1.1.sql
new file mode 100644
index 0000000..e7b25bd
--- /dev/null
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.0--1.1.sql
@@ -0,0 +1,21 @@
+/* contrib/pg_freespacemap/pg_freespacemap--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "ALTER EXTENSION pg_freespacemap UPDATE TO '1.1'" to load this file. \quit
+
+CREATE FUNCTION pg_is_all_visible(regclass, bigint)
+RETURNS bool
+AS 'MODULE_PATHNAME', 'pg_is_all_visible'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION
+ pg_freespace_with_vminfo(rel regclass, blkno OUT bigint, avail OUT int2, is_all_visible OUT boolean)
+RETURNS SETOF RECORD
+AS $$
+ SELECT blkno, pg_freespace($1, blkno) AS avail, pg_is_all_visible($1, blkno) as is_all_visible
+ FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
+$$
+LANGUAGE SQL;
+
+-- Don't want these to be available to public.
+REVOKE ALL ON FUNCTION pg_freespace_with_vminfo(regclass) FROM PUBLIC;
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.0.sql b/contrib/pg_freespacemap/pg_freespacemap--1.0.sql
deleted file mode 100644
index 2adb52a..0000000
--- a/contrib/pg_freespacemap/pg_freespacemap--1.0.sql
+++ /dev/null
@@ -1,25 +0,0 @@
-/* contrib/pg_freespacemap/pg_freespacemap--1.0.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION pg_freespacemap" to load this file. \quit
-
--- Register the C function.
-CREATE FUNCTION pg_freespace(regclass, bigint)
-RETURNS int2
-AS 'MODULE_PATHNAME', 'pg_freespace'
-LANGUAGE C STRICT;
-
--- pg_freespace shows the recorded space avail at each block in a relation
-CREATE FUNCTION
- pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2)
-RETURNS SETOF RECORD
-AS $$
- SELECT blkno, pg_freespace($1, blkno) AS avail
- FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
-$$
-LANGUAGE SQL;
-
-
--- Don't want these to be available to public.
-REVOKE ALL ON FUNCTION pg_freespace(regclass, bigint) FROM PUBLIC;
-REVOKE ALL ON FUNCTION pg_freespace(regclass) FROM PUBLIC;
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.1.sql b/contrib/pg_freespacemap/pg_freespacemap--1.1.sql
new file mode 100644
index 0000000..7d2c2fe
--- /dev/null
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.1.sql
@@ -0,0 +1,41 @@
+/* contrib/pg_freespacemap/pg_freespacemap--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_freespacemap" to load this file. \quit
+
+-- Register the C function.
+CREATE FUNCTION pg_freespace(regclass, bigint)
+RETURNS int2
+AS 'MODULE_PATHNAME', 'pg_freespace'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_is_all_visible(regclass, bigint)
+RETURNS bool
+AS 'MODULE_PATHNAME', 'pg_is_all_visible'
+LANGUAGE C STRICT;
+
+-- pg_freespace shows the recorded space avail at each block in a relation
+CREATE FUNCTION
+ pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2)
+RETURNS SETOF RECORD
+AS $$
+ SELECT blkno, pg_freespace($1, blkno) AS avail
+ FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
+$$
+LANGUAGE SQL;
+
+CREATE FUNCTION
+ pg_freespace_with_vminfo(rel regclass, blkno OUT bigint, avail OUT int2, is_all_visible OUT boolean)
+RETURNS SETOF RECORD
+AS $$
+ SELECT blkno, pg_freespace($1, blkno) AS avail, pg_is_all_visible($1, blkno) as is_all_visible
+ FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
+$$
+LANGUAGE SQL;
+
+
+
+-- Don't want these to be available to public.
+REVOKE ALL ON FUNCTION pg_freespace(regclass, bigint) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_freespace(regclass) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_freespace_with_vminfo(regclass) FROM PUBLIC;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.c b/contrib/pg_freespacemap/pg_freespacemap.c
index f6f7d2e..61b1a04 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.c
+++ b/contrib/pg_freespacemap/pg_freespacemap.c
@@ -10,17 +10,20 @@
#include "funcapi.h"
#include "storage/freespace.h"
-
+#include "access/visibilitymap.h"
+#include "storage/bufmgr.h"
PG_MODULE_MAGIC;
Datum pg_freespace(PG_FUNCTION_ARGS);
+Datum pg_is_all_visible(PG_FUNCTION_ARGS);
/*
* Returns the amount of free space on a given page, according to the
* free space map.
*/
PG_FUNCTION_INFO_V1(pg_freespace);
+PG_FUNCTION_INFO_V1(pg_is_all_visible);
Datum
pg_freespace(PG_FUNCTION_ARGS)
@@ -38,7 +41,32 @@ pg_freespace(PG_FUNCTION_ARGS)
errmsg("invalid block number")));
freespace = GetRecordedFreeSpace(rel, blkno);
-
relation_close(rel, AccessShareLock);
PG_RETURN_INT16(freespace);
}
+
+Datum
+pg_is_all_visible(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ int64 blkno = PG_GETARG_INT64(1);
+ Buffer vmbuffer = InvalidBuffer;
+ int all_visible;
+ Relation rel;
+
+ rel = relation_open(relid, AccessShareLock);
+
+ if (blkno < 0 || blkno > MaxBlockNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid block number")));
+
+ all_visible = visibilitymap_test(rel, blkno, &vmbuffer);
+ if (vmbuffer != InvalidBuffer)
+ {
+ ReleaseBuffer(vmbuffer);
+ vmbuffer = InvalidBuffer;
+ }
+ relation_close(rel, AccessShareLock);
+ PG_RETURN_BOOL(all_visible);
+}
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control b/contrib/pg_freespacemap/pg_freespacemap.control
index 34b695f..7d1e62a 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
# pg_freespacemap extension
-comment = 'examine the free space map (FSM)'
-default_version = '1.0'
+comment = 'examine the free space map (FSM) and visibility map (VM)'
+default_version = '1.1'
module_pathname = '$libdir/pg_freespacemap'
relocatable = true
diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile
index fc893d8..e842afc 100644
--- a/contrib/pgstattuple/Makefile
+++ b/contrib/pgstattuple/Makefile
@@ -4,7 +4,7 @@ MODULE_big = pgstattuple
OBJS = pgstattuple.o pgstatindex.o
EXTENSION = pgstattuple
-DATA = pgstattuple--1.1.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
+DATA = pgstattuple--1.2.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.2.sql pgstattuple--unpackaged--1.0.sql
REGRESS = pgstattuple
diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out
index ab28f50..44c324d 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -6,15 +6,15 @@ CREATE EXTENSION pgstattuple;
--
create table test (a int primary key, b int[]);
select * from pgstattuple('test'::text);
- table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
- 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
+ table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent | all_visible_percent
+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+---------------------
+ 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
select * from pgstattuple('test'::regclass);
- table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
- 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
+ table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent | all_visible_percent
+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+---------------------
+ 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
select * from pgstatindex('test_pkey');
diff --git a/contrib/pgstattuple/pgstattuple--1.0--1.1.sql b/contrib/pgstattuple/pgstattuple--1.0--1.1.sql
deleted file mode 100644
index cf582a0..0000000
--- a/contrib/pgstattuple/pgstattuple--1.0--1.1.sql
+++ /dev/null
@@ -1,11 +0,0 @@
-/* contrib/pgstattuple/pgstattuple--1.0--1.1.sql */
-
--- complain if script is sourced in psql, rather than via ALTER EXTENSION
-\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.1'" to load this file. \quit
-
-CREATE FUNCTION pgstatginindex(IN relname regclass,
- OUT version INT4,
- OUT pending_pages INT4,
- OUT pending_tuples BIGINT)
-AS 'MODULE_PATHNAME', 'pgstatginindex'
-LANGUAGE C STRICT;
diff --git a/contrib/pgstattuple/pgstattuple--1.0--1.2.sql b/contrib/pgstattuple/pgstattuple--1.0--1.2.sql
new file mode 100644
index 0000000..051a04b
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.0--1.2.sql
@@ -0,0 +1,41 @@
+/* contrib/pgstattuple/pgstattuple--1.0--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.2'" to load this file. \quit
+
+CREATE FUNCTION pgstatginindex(IN relname regclass,
+ OUT version INT4,
+ OUT pending_pages INT4,
+ OUT pending_tuples BIGINT)
+AS 'MODULE_PATHNAME', 'pgstatginindex'
+LANGUAGE C STRICT;
+
+DROP FUNCTION pgstattuple(text);
+CREATE OR REPLACE FUNCTION pgstattuple(IN relname text,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8, -- free space in %
+ OUT all_visible_percent FLOAT8) -- all visible blocks in %
+AS 'MODULE_PATHNAME', 'pgstattuple'
+LANGUAGE C STRICT;
+
+DROP FUNCTION pgstattuple(oid);
+CREATE OR REPLACE FUNCTION pgstattuple(IN reloid oid,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8, -- free space in %
+ OUT all_visible_percent FLOAT8) -- all visible blocks in %
+AS 'MODULE_PATHNAME', 'pgstattuplebyid'
+LANGUAGE C STRICT;
diff --git a/contrib/pgstattuple/pgstattuple--1.1--1.2.sql b/contrib/pgstattuple/pgstattuple--1.1--1.2.sql
new file mode 100644
index 0000000..124ae3e
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.1--1.2.sql
@@ -0,0 +1,34 @@
+/* contrib/pgstattuple/pgstattuple--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.2'" to load this file. \quit
+
+DROP FUNCTION pgstattuple(text);
+CREATE OR REPLACE FUNCTION pgstattuple(IN relname text,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8, -- free space in %
+ OUT all_visible_percent FLOAT8) -- all visible blocks in %
+AS 'MODULE_PATHNAME', 'pgstattuple'
+LANGUAGE C STRICT;
+
+DROP FUNCTION pgstattuple(oid);
+CREATE OR REPLACE FUNCTION pgstattuple(IN reloid oid,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8, -- free space in %
+ OUT all_visible_percent FLOAT8) -- all visible blocks in %
+AS 'MODULE_PATHNAME', 'pgstattuplebyid'
+LANGUAGE C STRICT;
diff --git a/contrib/pgstattuple/pgstattuple--1.1.sql b/contrib/pgstattuple/pgstattuple--1.1.sql
deleted file mode 100644
index b21fbf8..0000000
--- a/contrib/pgstattuple/pgstattuple--1.1.sql
+++ /dev/null
@@ -1,58 +0,0 @@
-/* contrib/pgstattuple/pgstattuple--1.1.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
-
-CREATE FUNCTION pgstattuple(IN relname text,
- OUT table_len BIGINT, -- physical table length in bytes
- OUT tuple_count BIGINT, -- number of live tuples
- OUT tuple_len BIGINT, -- total tuples length in bytes
- OUT tuple_percent FLOAT8, -- live tuples in %
- OUT dead_tuple_count BIGINT, -- number of dead tuples
- OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
- OUT dead_tuple_percent FLOAT8, -- dead tuples in %
- OUT free_space BIGINT, -- free space in bytes
- OUT free_percent FLOAT8) -- free space in %
-AS 'MODULE_PATHNAME', 'pgstattuple'
-LANGUAGE C STRICT;
-
-CREATE FUNCTION pgstattuple(IN reloid oid,
- OUT table_len BIGINT, -- physical table length in bytes
- OUT tuple_count BIGINT, -- number of live tuples
- OUT tuple_len BIGINT, -- total tuples length in bytes
- OUT tuple_percent FLOAT8, -- live tuples in %
- OUT dead_tuple_count BIGINT, -- number of dead tuples
- OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
- OUT dead_tuple_percent FLOAT8, -- dead tuples in %
- OUT free_space BIGINT, -- free space in bytes
- OUT free_percent FLOAT8) -- free space in %
-AS 'MODULE_PATHNAME', 'pgstattuplebyid'
-LANGUAGE C STRICT;
-
-CREATE FUNCTION pgstatindex(IN relname text,
- OUT version INT,
- OUT tree_level INT,
- OUT index_size BIGINT,
- OUT root_block_no BIGINT,
- OUT internal_pages BIGINT,
- OUT leaf_pages BIGINT,
- OUT empty_pages BIGINT,
- OUT deleted_pages BIGINT,
- OUT avg_leaf_density FLOAT8,
- OUT leaf_fragmentation FLOAT8)
-AS 'MODULE_PATHNAME', 'pgstatindex'
-LANGUAGE C STRICT;
-
-CREATE FUNCTION pg_relpages(IN relname text)
-RETURNS BIGINT
-AS 'MODULE_PATHNAME', 'pg_relpages'
-LANGUAGE C STRICT;
-
-/* New stuff in 1.1 begins here */
-
-CREATE FUNCTION pgstatginindex(IN relname regclass,
- OUT version INT4,
- OUT pending_pages INT4,
- OUT pending_tuples BIGINT)
-AS 'MODULE_PATHNAME', 'pgstatginindex'
-LANGUAGE C STRICT;
diff --git a/contrib/pgstattuple/pgstattuple--1.2.sql b/contrib/pgstattuple/pgstattuple--1.2.sql
new file mode 100644
index 0000000..51fed69
--- /dev/null
+++ b/contrib/pgstattuple/pgstattuple--1.2.sql
@@ -0,0 +1,58 @@
+/* contrib/pgstattuple/pgstattuple--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit
+
+CREATE FUNCTION pgstattuple(IN relname text,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8, -- free space in %
+ OUT all_visible_percent FLOAT8) -- all visible blocks in %
+AS 'MODULE_PATHNAME', 'pgstattuple'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pgstattuple(IN reloid oid,
+ OUT table_len BIGINT, -- physical table length in bytes
+ OUT tuple_count BIGINT, -- number of live tuples
+ OUT tuple_len BIGINT, -- total tuples length in bytes
+ OUT tuple_percent FLOAT8, -- live tuples in %
+ OUT dead_tuple_count BIGINT, -- number of dead tuples
+ OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes
+ OUT dead_tuple_percent FLOAT8, -- dead tuples in %
+ OUT free_space BIGINT, -- free space in bytes
+ OUT free_percent FLOAT8, -- free space in %
+ OUT all_visible_percent FLOAT8) -- all visible blocks in %
+AS 'MODULE_PATHNAME', 'pgstattuplebyid'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pgstatindex(IN relname text,
+ OUT version INT,
+ OUT tree_level INT,
+ OUT index_size BIGINT,
+ OUT root_block_no BIGINT,
+ OUT internal_pages BIGINT,
+ OUT leaf_pages BIGINT,
+ OUT empty_pages BIGINT,
+ OUT deleted_pages BIGINT,
+ OUT avg_leaf_density FLOAT8,
+ OUT leaf_fragmentation FLOAT8)
+AS 'MODULE_PATHNAME', 'pgstatindex'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pg_relpages(IN relname text)
+RETURNS BIGINT
+AS 'MODULE_PATHNAME', 'pg_relpages'
+LANGUAGE C STRICT;
+
+CREATE FUNCTION pgstatginindex(IN relname regclass,
+ OUT version INT4,
+ OUT pending_pages INT4,
+ OUT pending_tuples BIGINT)
+AS 'MODULE_PATHNAME', 'pgstatginindex'
+LANGUAGE C STRICT;
diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c
index 7f41ec3..1e5d7a5 100644
--- a/contrib/pgstattuple/pgstattuple.c
+++ b/contrib/pgstattuple/pgstattuple.c
@@ -35,6 +35,7 @@
#include "storage/lmgr.h"
#include "utils/builtins.h"
#include "utils/tqual.h"
+#include "access/visibilitymap.h"
PG_MODULE_MAGIC;
@@ -59,6 +60,7 @@ typedef struct pgstattuple_type
uint64 dead_tuple_count;
uint64 dead_tuple_len;
uint64 free_space; /* free/reusable space in bytes */
+ double all_visible_ratio;
} pgstattuple_type;
typedef void (*pgstat_page) (pgstattuple_type *, Relation, BlockNumber,
@@ -88,7 +90,7 @@ static void pgstat_index_page(pgstattuple_type *stat, Page page,
static Datum
build_pgstattuple_type(pgstattuple_type *stat, FunctionCallInfo fcinfo)
{
-#define NCOLUMNS 9
+#define NCOLUMNS 10
#define NCHARS 32
HeapTuple tuple;
@@ -97,7 +99,7 @@ build_pgstattuple_type(pgstattuple_type *stat, FunctionCallInfo fcinfo)
int i;
double tuple_percent;
double dead_tuple_percent;
- double free_percent; /* free/reusable space in % */
+ double free_percent; /* free/reusable space in % */
TupleDesc tupdesc;
AttInMetadata *attinmeta;
@@ -141,6 +143,7 @@ build_pgstattuple_type(pgstattuple_type *stat, FunctionCallInfo fcinfo)
snprintf(values[i++], NCHARS, "%.2f", dead_tuple_percent);
snprintf(values[i++], NCHARS, INT64_FORMAT, stat->free_space);
snprintf(values[i++], NCHARS, "%.2f", free_percent);
+ snprintf(values[i++], NCHARS, "%.2f", stat->all_visible_ratio * 100.0);
/* build a tuple */
tuple = BuildTupleFromCStrings(attinmeta, values);
@@ -275,7 +278,9 @@ pgstat_heap(Relation rel, FunctionCallInfo fcinfo)
BlockNumber nblocks;
BlockNumber block = 0; /* next block to count free space in */
BlockNumber tupblock;
+ BlockNumber all_visible_pages = 0;
Buffer buffer;
+ Buffer vmbuffer = InvalidBuffer;
pgstattuple_type stat = {0};
BufferAccessStrategy bstrategy;
@@ -341,6 +346,26 @@ pgstat_heap(Relation rel, FunctionCallInfo fcinfo)
block++;
}
+ /*
+ * The only reason to check for visibilitymap here is the consistency of
+ * all_visible_pages against other stats.
+ */
+ for (block = 0 ; block < nblocks ; block++)
+ {
+ if (visibilitymap_test(rel, block, &vmbuffer))
+ all_visible_pages++;
+ }
+ if (vmbuffer != InvalidBuffer)
+ {
+ ReleaseBuffer(vmbuffer);
+ vmbuffer = InvalidBuffer;
+ }
+
+ if (nblocks > 0)
+ stat.all_visible_ratio = (double)all_visible_pages / nblocks;
+ else
+ stat.all_visible_ratio = 0.0;
+
relation_close(rel, AccessShareLock);
stat.table_len = (uint64) nblocks *BLCKSZ;
diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control
index fcfd36f..a7cf47f 100644
--- a/contrib/pgstattuple/pgstattuple.control
+++ b/contrib/pgstattuple/pgstattuple.control
@@ -1,5 +1,5 @@
# pgstattuple extension
comment = 'show tuple-level statistics'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/pgstattuple'
relocatable = true
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
