(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.kyot...@lab.ntt.co.jp> wrote in
<20130708.165905.118860769.horiguchi.kyot...@lab.ntt.co.jp>
I'll come again with the first implementation of it. And as for
pg_freespacemap, I'll keep the current direction - adding column
to present output records format of pg_freespace(). And
documentation, if possible.
pg_freespace_vm_v2.patch:
Interface has been changed from the first patch. The version of
pg_freespace() provided with vm information is named
pg_freespace_with_vminfo() and shows output like following.
| postgres=# select * from pg_freespace_with_vminfo('t'::regclass) limit 10;
| blkno | avail | is_all_visible
| -------+-------+----------------
| 0 | 64 | t
| 1 | 32 | t
| 2 | 96 | t
| 3 | 64 | t
| 4 | 96 | t
| 5 | 96 | t
| 6 | 128 | t
| 7 | 32 | t
| 8 | 96 | t
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 <sn...@uptime.jp>
Uptime Technologies, LLC. http://www.uptime.jp
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index d794df2..09d6ff8 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -6,6 +6,8 @@ OBJS = pg_freespacemap.o
EXTENSION = pg_freespacemap
DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql
pg_freespacemap--unpackaged--1.0.sql
+REGRESS = pg_freespacemap
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
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/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/expected/pgstattuple.out
b/contrib/pgstattuple/expected/pgstattuple.out
index 44c324d..ca08834 100644
--- a/contrib/pgstattuple/expected/pgstattuple.out
+++ b/contrib/pgstattuple/expected/pgstattuple.out
@@ -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/sql/pgstattuple.sql
b/contrib/pgstattuple/sql/pgstattuple.sql
index 8cb350d..90292f9 100644
--- a/contrib/pgstattuple/sql/pgstattuple.sql
+++ b/contrib/pgstattuple/sql/pgstattuple.sql
@@ -19,3 +19,12 @@ select pg_relpages('test_pkey');
create index test_ginidx on test using gin (b);
select * from pgstatginindex('test_ginidx');
+
+insert into test values ( 100, '{101,201}' );
+select * from pgstattuple('test');
+vacuum test;
+select * from pgstattuple('test');
+delete from test;
+select * from pgstattuple('test');
+vacuum test;
+select * from pgstattuple('test');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
