Re: [HACKERS] Add visibility map information to pg_freespace.
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.
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/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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
[HACKERS] 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 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)
+ pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2, all_visible OUT bool)
RETURNS SETOF RECORD
AS $$
- 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);
Datum
pg_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
--
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.
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.
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.
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
