Re: [HACKERS] Add visibility map information to pg_freespace.

2013-07-18 Thread Kyotaro HORIGUCHI
Thank you for the worthwhile additions.

At Tue, 16 Jul 2013 16:04:43 +0900, Satoshi Nagayasu sn...@uptime.jp wrote in 
51e4f08b.3030...@uptime.jp
  | postgres=# select * from pg_freespace_with_vminfo('t'::regclass) limit
  | 10;
..
 I think we can simply add is_all_viible column to the existing
 pg_freespace(), because adding column would not break
 backward-compatibility in general. Any other thoughts?

I agree to you. I cannot guess any 'ordinary' application which
uses this function, or someone's craft critically affected by
this change. This decision was merely a safe bet.

I'll remerge _with_vminfo function to pg_freespace() in the next
patch if no objection is raised.

  pgstattuple_vm_v1.patch:
...
 It seems working fine.
 
 And I added a regression test for pg_freespacemap and additional
 test cases for pgstattuple. Please take a look.

Thank you. This seems fine. I felt a bit uneasy with the absense
of regtests in pg_freespacemap, but I took advantage of the
absense not to add new ones.

I have simply merged the two regtests separately into two
original patches.  You will find the two attached files.

pg_freespace_vm_v3.patch :
   new patch for pg_freespace with regtests and _with_vminfo

pgstattuple_vm_v2.patch  :
   new patch for gstattuple with regtests


regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index b2e3ba3..09d6ff8 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -4,7 +4,9 @@ MODULE_big = pg_freespacemap
 OBJS = pg_freespacemap.o
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.0.sql pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
+
+REGRESS = pg_freespacemap
 
 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/contrib/pg_freespacemap/expected/pg_freespacemap.out b/contrib/pg_freespacemap/expected/pg_freespacemap.out
new file mode 100644
index 000..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 000..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, 

Re: [HACKERS] Add visibility map information to pg_freespace.

2013-07-18 Thread Kyotaro HORIGUCHI
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-07-16 Thread Satoshi Nagayasu



(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 000..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 000..79a458d
--- /dev/null
+++ 

Re: [HACKERS] Add visibility map information to pg_freespace.

2013-07-09 Thread Kyotaro HORIGUCHI
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
   
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_freespacemap
 OBJS = pg_freespacemap.o
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.0.sql pg_freespacemap--unpackaged--1.0.sql
+DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.0--1.1.sql pg_freespacemap--unpackaged--1.0.sql
 
 ifdef USE_PGXS
 PG_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 000..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..000
--- 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 000..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;
+

Re: [HACKERS] Add visibility map information to pg_freespace.

2013-07-08 Thread Kyotaro HORIGUCHI
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-07-08 Thread Josh Berkus
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-26 Thread Kyotaro HORIGUCHI
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 compatibility
  view despite the loss of the pertinence to stay in this
  extension. However, it is too small to be a standalone
  extension.

  On the other hand the newly-added-column-to-the-tail could be
  said to be harmless for the most cases considering the usage of
  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
  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:-).


 - 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  | 61
   | tuple_len| 26400044
   | tuple_percent| 29.76
   | dead_tuple_count | 39
   | 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-26 Thread Simon Riggs
On 26 June 2013 09:09, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jpwrote:


  - 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  | 61
| tuple_len| 26400044
| tuple_percent| 29.76
| dead_tuple_count | 39
| 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


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-21 Thread Robert Haas
On Wed, Jun 19, 2013 at 11:26 PM, Satoshi Nagayasu sn...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-20 Thread Simon Riggs
On 20 June 2013 04:26, Satoshi Nagayasu sn...@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.kyot...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-19 Thread Kyotaro HORIGUCHI
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 
| ---
|  : 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 +
|  : 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-19 Thread Simon Riggs
On 19 June 2013 09:19, Kyotaro HORIGUCHI
horiguchi.kyot...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-19 Thread Andres Freund
On 2013-06-19 10:03:40 +0100, Simon Riggs wrote:
 On 19 June 2013 09:19, Kyotaro HORIGUCHI
 horiguchi.kyot...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-19 Thread Simon Riggs
On 19 June 2013 10:15, Andres Freund and...@2ndquadrant.com wrote:
 On 2013-06-19 10:03:40 +0100, Simon Riggs wrote:
 On 19 June 2013 09:19, Kyotaro HORIGUCHI
 horiguchi.kyot...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-19 Thread Satoshi Nagayasu

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.kyot...@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 sn...@uptime.jp
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-15 Thread Simon Riggs
On 14 June 2013 15:22, Alvaro Herrera alvhe...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-14 Thread Alvaro Herrera
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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-14 Thread Andres Freund
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Add visibility map information to pg_freespace.

2013-06-14 Thread Peter Geoghegan
On Fri, Jun 14, 2013 at 7:23 AM, Andres Freund and...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers