Re: Add visibility map information to pg_freespace.

Thank you for the worthwhile additions.

At Tue, 16 Jul 2013 16:04:43 +0900, Satoshi Nagayasu <snaga@uptime.jp> wrote in <51E4F08B.3030307@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_freespacemapOBJS = pg_freespacemap.oEXTENSION = 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_freespacemapifdef USE_PGXSPG_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);Datumpg_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    = pgstattupleOBJS        = pgstattuple.o pgstatindex.oEXTENSION = 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.sqlREGRESS= 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
Datumbuild_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 extensioncomment = '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');