Thank you for the worthwhile additions.
At Tue, 16 Jul 2013 16:04:43 +0900, Satoshi Nagayasu <sn...@uptime.jp> wrote in
<51e4f08b.3030...@uptime.jp>
> > | postgres=# select * from pg_freespace_with_vminfo('t'::regclass) limit
> > | 10;
..
> I think we can simply add is_all_viible column to the existing
> pg_freespace(), because adding column would not break
> backward-compatibility in general. Any other thoughts?
I agree to you. I cannot guess any 'ordinary' application which
uses this function, or someone's craft critically affected by
this change. This decision was merely a safe bet.
I'll remerge _with_vminfo function to pg_freespace() in the next
patch if no objection is raised.
> > pgstattuple_vm_v1.patch:
...
> It seems working fine.
>
> And I added a regression test for pg_freespacemap and additional
> test cases for pgstattuple. Please take a look.
Thank you. This seems fine. I felt a bit uneasy with the absense
of regtests in pg_freespacemap, but I took advantage of the
absense not to add new ones.
I have simply merged the two regtests separately into two
original patches. You will find the two attached files.
pg_freespace_vm_v3.patch :
new patch for pg_freespace with regtests and _with_vminfo
pgstattuple_vm_v2.patch :
new patch for gstattuple with regtests
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index b2e3ba3..09d6ff8 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,7 +4,9 @@ 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
+
+REGRESS = pg_freespacemap
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pg_freespacemap/expected/pg_freespacemap.out b/contrib/pg_freespacemap/expected/pg_freespacemap.out
new file mode 100644
index 0000000..cde954d
--- /dev/null
+++ b/contrib/pg_freespacemap/expected/pg_freespacemap.out
@@ -0,0 +1,100 @@
+create extension pg_freespacemap;
+create table t1 ( uid integer primary key, uname text not null );
+select * from pg_freespace('t1');
+ blkno | avail
+-------+-------
+(0 rows)
+
+select * from pg_freespace('t1'::regclass);
+ blkno | avail
+-------+-------
+(0 rows)
+
+select * from pg_freespace('t1', 1);
+ pg_freespace
+--------------
+ 0
+(1 row)
+
+select * from pg_freespace_with_vminfo('t1');
+ blkno | avail | is_all_visible
+-------+-------+----------------
+(0 rows)
+
+select * from pg_freespace_with_vminfo('t1'::regclass);
+ blkno | avail | is_all_visible
+-------+-------+----------------
+(0 rows)
+
+insert into t1 values ( 100, 'postgresql' );
+select * from pg_freespace('t1');
+ blkno | avail
+-------+-------
+ 0 | 0
+(1 row)
+
+select * from pg_freespace('t1', 1);
+ pg_freespace
+--------------
+ 0
+(1 row)
+
+select * from pg_freespace_with_vminfo('t1');
+ blkno | avail | is_all_visible
+-------+-------+----------------
+ 0 | 0 | f
+(1 row)
+
+select * from pg_freespace('t1_pkey');
+ blkno | avail
+-------+-------
+ 0 | 0
+ 1 | 0
+(2 rows)
+
+select * from pg_freespace('t1_pkey', 1);
+ pg_freespace
+--------------
+ 0
+(1 row)
+
+select * from pg_freespace('t1_pkey', 2);
+ pg_freespace
+--------------
+ 0
+(1 row)
+
+select * from pg_freespace_with_vminfo('t1_pkey');
+ blkno | avail | is_all_visible
+-------+-------+----------------
+ 0 | 0 | f
+ 1 | 0 | f
+(2 rows)
+
+vacuum t1;
+select * from pg_freespace('t1');
+ blkno | avail
+-------+-------
+ 0 | 8096
+(1 row)
+
+select * from pg_freespace_with_vminfo('t1');
+ blkno | avail | is_all_visible
+-------+-------+----------------
+ 0 | 8096 | t
+(1 row)
+
+select * from pg_freespace('t1_pkey');
+ blkno | avail
+-------+-------
+ 0 | 0
+ 1 | 0
+(2 rows)
+
+select * from pg_freespace_with_vminfo('t1_pkey');
+ blkno | avail | is_all_visible
+-------+-------+----------------
+ 0 | 0 | f
+ 1 | 0 | f
+(2 rows)
+
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/pg_freespacemap/sql/pg_freespacemap.sql b/contrib/pg_freespacemap/sql/pg_freespacemap.sql
new file mode 100644
index 0000000..79a458d
--- /dev/null
+++ b/contrib/pg_freespacemap/sql/pg_freespacemap.sql
@@ -0,0 +1,29 @@
+create extension pg_freespacemap;
+
+create table t1 ( uid integer primary key, uname text not null );
+
+select * from pg_freespace('t1');
+select * from pg_freespace('t1'::regclass);
+select * from pg_freespace('t1', 1);
+select * from pg_freespace_with_vminfo('t1');
+select * from pg_freespace_with_vminfo('t1'::regclass);
+
+insert into t1 values ( 100, 'postgresql' );
+
+select * from pg_freespace('t1');
+select * from pg_freespace('t1', 1);
+select * from pg_freespace_with_vminfo('t1');
+
+select * from pg_freespace('t1_pkey');
+select * from pg_freespace('t1_pkey', 1);
+select * from pg_freespace('t1_pkey', 2);
+select * from pg_freespace_with_vminfo('t1_pkey');
+
+vacuum t1;
+
+select * from pg_freespace('t1');
+select * from pg_freespace_with_vminfo('t1');
+select * from pg_freespace('t1_pkey');
+select * from pg_freespace_with_vminfo('t1_pkey');
+
+
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..ca08834 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');
@@ -42,3 +42,31 @@ select * from pgstatginindex('test_ginidx');
1 | 0 | 0
(1 row)
+insert into test values ( 100, '{101,201}' );
+select * from pgstattuple('test');
+ table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent | all_visible_percent
+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+---------------------
+ 8192 | 1 | 57 | 0.7 | 0 | 0 | 0 | 8096 | 98.83 | 0
+(1 row)
+
+vacuum test;
+select * from pgstattuple('test');
+ table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent | all_visible_percent
+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+---------------------
+ 8192 | 1 | 57 | 0.7 | 0 | 0 | 0 | 8096 | 98.83 | 100
+(1 row)
+
+delete from test;
+select * from pgstattuple('test');
+ table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent | all_visible_percent
+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+---------------------
+ 8192 | 0 | 0 | 0 | 1 | 57 | 0.7 | 8096 | 98.83 | 0
+(1 row)
+
+vacuum test;
+select * from pgstattuple('test');
+ 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)
+
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
diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql
index 8cb350d..90292f9 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -19,3 +19,12 @@ select pg_relpages('test_pkey');
create index test_ginidx on test using gin (b);
select * from pgstatginindex('test_ginidx');
+
+insert into test values ( 100, '{101,201}' );
+select * from pgstattuple('test');
+vacuum test;
+select * from pgstattuple('test');
+delete from test;
+select * from pgstattuple('test');
+vacuum test;
+select * from pgstattuple('test');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
