Re: Add visibility map information to pg_freespace.

(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(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <20130708(dot)165905(dot)118860769(dot)horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)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(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp