Thread: Add visibility map information to pg_freespace.
Helle,
I've added visibility map information to pg_freespace for my
utility.
This looks like this,
postgres=# select * from pg_freespace('t'::regclass);blkno | avail | all_visible
-------+-------+------------- 0 | 7424 | t 1 | 7424 | t 2 | 7424 | t 3 | 7424 | t 4 | 7424 | t 5
| 7424 | t 6 | 7424 | t 7 | 7424 | t
...
What do you think about this?
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.0.sql b/contrib/pg_freespacemap/pg_freespacemap--1.0.sql
index 2adb52a..e38b466 100644
--- a/contrib/pg_freespacemap/pg_freespacemap--1.0.sql
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.0.sql
@@ -9,12 +9,17 @@ RETURNS int2AS '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 relationCREATE FUNCTION
- pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2)
+ pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2, all_visible OUT bool)RETURNS SETOF RECORDAS $$
- SELECT blkno, pg_freespace($1, blkno) AS avail
+ SELECT blkno, pg_freespace($1, blkno) AS avail, pg_is_all_visible($1, blkno) AS all_visible FROM generate_series(0,
pg_relation_size($1)/ current_setting('block_size')::bigint - 1) AS blkno;$$LANGUAGE SQL;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.c b/contrib/pg_freespacemap/pg_freespacemap.c
index f6f7d2e..de4eff7 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"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..395350a 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)'
+comment = 'examine the free space map (FSM) and visibility map (VM)'default_version = '1.0'module_pathname =
'$libdir/pg_freespacemap'relocatable= true
Kyotaro HORIGUCHI wrote: > Helle, > > I've added visibility map information to pg_freespace for my > utility. This makes sense to me. I only lament the fact that this makes the module a misnomer. Do we want to 1) rename the module (how inconvenient), 2) create a separate module for this (surely not warranted), or 3) accept it and move on? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 2013-06-14 10:22:19 -0400, Alvaro Herrera wrote: > Kyotaro HORIGUCHI wrote: > > Helle, > > > > I've added visibility map information to pg_freespace for my > > utility. > > This makes sense to me. +1 > I only lament the fact that this makes the > module a misnomer. Do we want to 1) rename the module (how > inconvenient), 2) create a separate module for this (surely not > warranted), or 3) accept it and move on? 3). All the others seem to inflict unneccesary pain for not all that much gain. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Jun 14, 2013 at 7:23 AM, Andres Freund <andres@2ndquadrant.com> wrote: > 3). All the others seem to inflict unneccesary pain for not all that > much gain. +1. You might want to add a "historical note" about the name to the pg_freespace documentation, though. -- Peter Geoghegan
On 14 June 2013 15:22, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Kyotaro HORIGUCHI wrote: >> Helle, >> >> I've added visibility map information to pg_freespace for my >> utility. > > This makes sense to me. I only lament the fact that this makes the > module a misnomer. Do we want to 1) rename the module (how > inconvenient), 2) create a separate module for this (surely not > warranted), or 3) accept it and move on? I'm not sure why this is suggested as being part of pg_freespace and not part of pageinspect? (Which is where all the other inspection tools live). If I wanted to see the vismap (and I do...) then I'd like to see the whole vismap, not just the part that relates to blocks currently in cache. If you do want that, you can just join the two things together (function to see vismap joined to pg_freespace). (Having said that, I don't have a major objection to it being in pg_freespace as well). --Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Thank you.
> > This makes sense to me. I only lament the fact that this makes the
> > module a misnomer. Do we want to 1) rename the module (how
> > inconvenient), 2) create a separate module for this (surely not
> > warranted), or 3) accept it and move on?
Although I also feel uneasy with the module name, I suppose this
is not so major change as changing the module name.
> I'm not sure why this is suggested as being part of pg_freespace and
> not part of pageinspect? (Which is where all the other inspection
> tools live).
I'm afraid I wasn't aware of that. I think the following
operation shows the info of fsm.
| =# select fsm_page_contents(get_raw_page('t', 'fsm', 0));
| fsm_page_contents
| -------------------
| 0: 147 +
| 1: 147 +
...
| 2047: 147 +
| 4095: 147 +
| fp_next_slot: 0 +
If this is the only way to inspect fsm info with this module, I
can't say it is consise enough just to know the fsm info
corresponds to certain heap block. pg_freespace seems preferable
for such a purpose.
Following the manner shown above, I'll provide vm_page_contents
then command and it'll show result as following.
| =# select vm_page_contents(get_raw_page('t', 'vm', 0));
| v_page_contents
| -------------------
| 0: t +
| 1: f +
...
| 65343: t +
# Too long...
It should useful in other aspects but it seems a bit complicated
just to know about visibility bits for certain blocks.
> If I wanted to see the vismap (and I do...) then I'd like to see the
> whole vismap, not just the part that relates to blocks currently in
> cache.
> If you do want that, you can just join the two things together
> (function to see vismap joined to pg_freespace).
From the aspect of interface, thay look to be separate
functions.
On the other hand there's no problem to add vm_page_contents to
pageinspect, although in another output format. It'll look like,
| v_page_contents
| -------------------
| 0000: 0 0 1 1 1 0 0 0 0 0 1 1 0 0 1 0 +
| 0001: 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0 +
| ffff: 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0 +
...
| ff30: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
# 't' and 'f' are too confusing beeing shown in a line...
Any suggestions for the output format?
> (Having said that, I don't have a major objection to it being in
> pg_freespace as well).
I prefer to leave that issue this time for anyone - including me :-p
--
Kyotaro Horiguchi
NTT Open Source Software Center
On 19 June 2013 09:19, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > It should useful in other aspects but it seems a bit complicated > just to know about visibility bits for certain blocks. With your current patch you can only see the visibility info for blocks in cache, not for all blocks. So while you may think it is useful, it is also unnecessarily limited in scope. Let's just have something that is easy to use that lets us see the visibility state for a block, not just blocks in freespace. --Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 2013-06-19 10:03:40 +0100, Simon Riggs wrote: > On 19 June 2013 09:19, Kyotaro HORIGUCHI > <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > > > It should useful in other aspects but it seems a bit complicated > > just to know about visibility bits for certain blocks. > > With your current patch you can only see the visibility info for > blocks in cache, not for all blocks. So while you may think it is > useful, it is also unnecessarily limited in scope. pg_freespace should do more than that? Are you thinking of pg_buffercache? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 19 June 2013 10:15, Andres Freund <andres@2ndquadrant.com> wrote: > On 2013-06-19 10:03:40 +0100, Simon Riggs wrote: >> On 19 June 2013 09:19, Kyotaro HORIGUCHI >> <horiguchi.kyotaro@lab.ntt.co.jp> wrote: >> >> > It should useful in other aspects but it seems a bit complicated >> > just to know about visibility bits for certain blocks. >> >> With your current patch you can only see the visibility info for >> blocks in cache, not for all blocks. So while you may think it is >> useful, it is also unnecessarily limited in scope. > > pg_freespace should do more than that? Are you thinking of > pg_buffercache? I was... my mistake. Please continue Kyotaro. --Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
I'm looking into this patch as a reviewer. (2013/06/19 18:03), Simon Riggs wrote: > On 19 June 2013 09:19, Kyotaro HORIGUCHI > <horiguchi.kyotaro@lab.ntt.co.jp> wrote: > >> It should useful in other aspects but it seems a bit complicated >> just to know about visibility bits for certain blocks. > > With your current patch you can only see the visibility info for > blocks in cache, not for all blocks. So while you may think it is > useful, it is also unnecessarily limited in scope. > > Let's just have something that is easy to use that lets us see the > visibility state for a block, not just blocks in freespace. I think we can have this visibility map statistics also in pgstattuple function (as a new column) for this purpose. IMHO, we have several modules for different purposes. - pageinspect provies several functions for debugging purpose. - pg_freespace provies a view for monitoring purpose. - pgstattuple provies several functions for collecting specific table/index statistics. So, we can have similar feature in different modules. Any comments? Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
On 20 June 2013 04:26, Satoshi Nagayasu <snaga@uptime.jp> wrote: > I'm looking into this patch as a reviewer. > > > (2013/06/19 18:03), Simon Riggs wrote: >> >> On 19 June 2013 09:19, Kyotaro HORIGUCHI >> <horiguchi.kyotaro@lab.ntt.co.jp> wrote: >> >>> It should useful in other aspects but it seems a bit complicated >>> just to know about visibility bits for certain blocks. >> >> >> With your current patch you can only see the visibility info for >> blocks in cache, not for all blocks. So while you may think it is >> useful, it is also unnecessarily limited in scope. >> >> Let's just have something that is easy to use that lets us see the >> visibility state for a block, not just blocks in freespace. > > > I think we can have this visibility map statistics also > in pgstattuple function (as a new column) for this purpose. > > IMHO, we have several modules for different purposes. > > - pageinspect provies several functions for debugging purpose. > - pg_freespace provies a view for monitoring purpose. > - pgstattuple provies several functions for collecting > specific table/index statistics. > > So, we can have similar feature in different modules. > > Any comments? +1 --Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Jun 19, 2013 at 11:26 PM, Satoshi Nagayasu <snaga@uptime.jp> wrote: > - pageinspect provies several functions for debugging purpose. > - pg_freespace provies a view for monitoring purpose. > - pgstattuple provies several functions for collecting > specific table/index statistics. I think we should be careful to think about upgrade considerations when adding this functionality. Bumping the module version number to add new functions is less likely to break things for users than changing the return value of an existing SRF. Maybe that's too far down in the weeds to worry about, but it's a thought - especially for pg_freespace, where there's no real efficiency benefit to have the same function look at the FSM and the VM anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hello,
> I'm looking into this patch as a reviewer.
I'd appreciate your time to review.
I've had some suggestions so far,
- I should be cautious in changing existing interface.
You're right. It was somehow gone out of my mind. It might be better to provide a separate function from the
compatibilityview despite the loss of the pertinence to stay in this extension. However, it is too small to be a
standaloneextension.
On the other hand the newly-added-column-to-the-tail could be said to be harmless for the most cases considering the
usageof this extension, I suppose.
- Historical note is needed in pg_freespace doc.
Agreed, I'll provide documents not only for freespace, but for other modules I'll touch in this patch later.
- How about pageinspect?
I proposed a simple representation format as a basis for discussion. Nevertheless, the VM pages has no more structure
thana simple bit string. Given the VM info in pg_freespacemap, I've come in doubt of the necessity of
vm_page_contnets()for the reason besides the orthogonality in the this extension's interface (which paid no attention
before:-).
- How about pgstattuple?
It could even be said to be meaningful to add the number of not-all-visible pages or the ratio of it in the total
pages..
| postgres=# select * from pgstattuple('t'); | -[ RECORD 1 ]----------------+--------- | table_len
| 88711168 | tuple_count | 600001 | tuple_len | 26400044 | tuple_percent
| 29.76 | dead_tuple_count | 399999 | dead_tuple_len | 17599956 |
dead_tuple_percent | 19.84 | free_space | 33607960 | free_percent | 37.88
+not_all_visible_page_percent | 23.54
# This column name looks too long, though.
In addition, the discussion above about the stability of the interface is also applicable to this.
Any suggestions?
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
On 26 June 2013 09:09, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> wrote:
- How about pageinspect?
I proposed a simple representation format as a basis for
discussion. Nevertheless, the VM pages has no more structure
than a simple bit string. Given the VM info in pg_freespacemap,
I've come in doubt of the necessity of vm_page_contnets() for
the reason besides the orthogonality in the this extension's
interface (which paid no attention before:-).
I don't think that will be needed, now I understand.
- How about pgstattuple?
It could even be said to be meaningful to add the number of
not-all-visible pages or the ratio of it in the total pages..
| postgres=# select * from pgstattuple('t');
| -[ RECORD 1 ]----------------+---------
| table_len | 88711168
| tuple_count | 600001
| tuple_len | 26400044
| tuple_percent | 29.76
| dead_tuple_count | 399999
| dead_tuple_len | 17599956
| dead_tuple_percent | 19.84
| free_space | 33607960
| free_percent | 37.88
+ not_all_visible_page_percent | 23.54
# This column name looks too long, though.
Yes, please.
But name should be all_visible_percent.
Anybody that wants not_all_visible_percent can do the math.
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Hello, > > - How about pageinspect? > > > > I proposed a simple representation format as a basis for > > discussion. Nevertheless, the VM pages has no more structure > > than a simple bit string. Given the VM info in pg_freespacemap, > > I've come in doubt of the necessity of vm_page_contnets() for > > the reason besides the orthogonality in the this extension's > > interface (which paid no attention before:-). > > I don't think that will be needed, now I understand. Ok, I'll drop it from the list. > > - How about pgstattuple? > > > > It could even be said to be meaningful to add the number of > > not-all-visible pages or the ratio of it in the total pages.. .. > > | free_percent | 37.88 > > + not_all_visible_page_percent | 23.54 > > > > # This column name looks too long, though. > > > > Yes, please. > > But name should be all_visible_percent. > Anybody that wants not_all_visible_percent can do the math. You're quite right, plus, negative terms are a bit confusing. 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. regards, -- Kyotaro Horiguchi NTT Open Source Software Center
On 07/08/2013 12:59 AM, Kyotaro HORIGUCHI wrote: > 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. Do you think you'll be fixing these things in the next couple days? Otherwise, I would like to mark this returned with feedback, and you can submit an updated patch to the next CommitFest. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
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.kyotaro@lab.ntt.co.jp> wrote in
<20130708.165905.118860769.horiguchi.kyotaro@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_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.sqlifdef
USE_PGXSPG_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);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/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..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
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
(2013/07/09 19:55), Kyotaro HORIGUCHI wrote:
> 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.kyotaro@lab.ntt.co.jp> wrote
in<20130708.165905.118860769.horiguchi.kyotaro@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
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?
> 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
It seems working fine.
And I added a regression test for pg_freespacemap and additional
test cases for pgstattuple. Please take a look.
Regards,
--
Satoshi Nagayasu <snaga@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
Attachment
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');
Hmm. I'm sorry to find that this patch is already marked as 'Return with Feedback' on the CF page around the same time when the preveous review comment has sent. Is it somewhat crossing? Anyway, I'll take a rain check for this. > 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