[PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer state
Dear Hackers.
I'm studied PostgreSQL buffers for the development of new patches.
In particular, using pg_buffercache, is can easily check the status of actual buffer.
Bur there was one inconvenience.
Pg_buffercache was also to check only the dirty state of the buffer.
State of the buffer currently represents 10 states.
Therefore, it seems impossible to check remaining 9 state.
So I add a state column to pg_buffercache view so that I could print a value indicating the state of the buffer.
This is outpu as an unit32 type, and examples are shown below.
-----
postgres=# select * from pg_buffercache where bufferid = 1;
-[ RECORD 1 ]----+-----------
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0
buffer_state | 2203320320 <- it's a new column
-----
With the patches, user can check status values and check the status of the buffers.
However, if do not know source code, or do not know hex values,
It's difficult or impossible to check the actual state even using this patch.
Therefore, add a new function to improve readability when checking state.
When you input a value for state, this function prints out what the actual state.
Examples of actual use are as follows.
-----
postgres=# SELECT bufferid, relfilenode, state_text FROM pg_buffercache,
LATERAL pg_buffercache_state_print(buffer_state) M(state_text)
WHERE bufferid < 10;
bufferid | relfilenode | state_text
----------+-------------+-------------------------------------------------------
1 | 1262 | {LOCKED,VALID,TAG_VALID,PERMANENT}
2 | 1260 | {LOCKED,VALID,TAG_VALID,PERMANENT}
3 | 1259 | {LOCKED,DIRTY,VALID,TAG_VALID,JUST_DIRTIED,PERMANENT}
4 | 1259 | {LOCKED,VALID,TAG_VALID,PERMANENT}
5 | 1259 | {LOCKED,VALID,TAG_VALID,PERMANENT}
6 | 1249 | {LOCKED,VALID,TAG_VALID,PERMANENT}
7 | 1249 | {LOCKED,VALID,TAG_VALID,PERMANENT}
8 | 1249 | {LOCKED,VALID,TAG_VALID,PERMANENT}
9 | 1249 | {LOCKED,VALID,TAG_VALID,PERMANENT}
(9 rows)
-----
If you use this patch, I think that you can easily judge the state of the buffer.
Regards.
Moon.
Attachments:
pg_buffercache add a buffer state column and Add function to decode buffer state_V1.diffapplication/octet-stream; name="pg_buffercache add a buffer state column and Add function to decode buffer state_V1.diff"Download+175-13
Hi,
On 2017-11-14 17:57:00 +0900, Moon Insung wrote:
So I add a state column to pg_buffercache view so that I could print a value indicating the state of the buffer.
This is outpu as an unit32 type, and examples are shown below.
-----
postgres=# select * from pg_buffercache where bufferid = 1;
-[ RECORD 1 ]----+-----------
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0
buffer_state | 2203320320 <- it's a new column
-----
I'm disinclined to exposing state that way. It's an internal
representation that's not unlikely to change. Sure, pg_buffercache is
more of a debugging / investigatory tool, but I nevertheless see no
reason to expose it that way.
If we shared those flags more in a manner like you did below:
1 | 1262 | {LOCKED,VALID,TAG_VALID,PERMANENT}
that'd be more acceptable. However doing that by default would have
some performance downsides, because we'd need to create these arrays for
every row.
One way around that would be to create a buffer_state type that's
returned by pg_buffercache and then only decoded when outputting. Doing
that + having a cast to an array seems like it'd provide most of the
needed functionality?
Greetings,
Andres Freund
# I add [hacker] to the mail subject.
Dear Andres Freund.
Thank you for review!
I'm disinclined to exposing state that way. It's an internal representation that's not unlikely to change. Sure,
pg_buffercache is more of a debugging / investigatory tool, but I nevertheless see no reason to expose it that way.
Okay!
I'll not print(or add) the internal value directly.
(and I'll be careful when create another patch).
Thank you
One way around that would be to create a buffer_state type that's returned by pg_buffercache and then only decoded when
outputting. Doing that + having a cast to an array seems like it'd provide most of the needed functionality?
It's it better to output the decode state value from pg_buffercache view?
For example to following output
-----
postgres=# select * from pg_buffercache where bufferid = 1;
-[ RECORD 1 ]----+-----------
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0
buffer_state | {LOCKED,VALID,TAG_VALID,PERMANENT}
-----
It's right?
If it is correct, I'll modify patch ASAP.
Regards.
Moon.
Show quoted text
-----Original Message-----
From: Andres Freund [mailto:andres@anarazel.de]
Sent: Tuesday, November 14, 2017 6:07 PM
To: Moon Insung
Cc: 'PostgreSQL Hackers'
Subject: Re: [PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer stateHi,
On 2017-11-14 17:57:00 +0900, Moon Insung wrote:
So I add a state column to pg_buffercache view so that I could print a value indicating the state of the buffer.
This is outpu as an unit32 type, and examples are shown below.-----
postgres=# select * from pg_buffercache where bufferid = 1; -[ RECORD
1 ]----+-----------
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0
buffer_state | 2203320320 <- it's a new column
-----I'm disinclined to exposing state that way. It's an internal representation that's not unlikely to change. Sure,
pg_buffercache is more of a debugging / investigatory tool, but I nevertheless see no reason to expose it that way.If we shared those flags more in a manner like you did below:
1 | 1262 | {LOCKED,VALID,TAG_VALID,PERMANENT}
that'd be more acceptable. However doing that by default would have some performance downsides, because we'd need to
create these arrays for every row.One way around that would be to create a buffer_state type that's returned by pg_buffercache and then only decoded when
outputting. Doing that + having a cast to an array seems like it'd provide most of the needed functionality?Greetings,
Andres Freund
Import Notes
Resolved by subject fallback
On Tue, Nov 14, 2017 at 6:36 PM, Moon Insung
<Moon_Insung_i3@lab.ntt.co.jp> wrote:
# I add [hacker] to the mail subject.
You should avoid top-posting.
Dear Andres Freund.
Thank you for review!
I'm disinclined to exposing state that way. It's an internal representation that's not unlikely to change. Sure,
pg_buffercache is more of a debugging / investigatory tool, but I nevertheless see no reason to expose it that way.Okay!
I'll not print(or add) the internal value directly.
(and I'll be careful when create another patch).
Thank youOne way around that would be to create a buffer_state type that's returned by pg_buffercache and then only decoded when
outputting. Doing that + having a cast to an array seems like it'd provide most of the needed functionality?
+1
It's it better to output the decode state value from pg_buffercache view?
For example to following output-----
postgres=# select * from pg_buffercache where bufferid = 1;
-[ RECORD 1 ]----+-----------
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0
buffer_state | {LOCKED,VALID,TAG_VALID,PERMANENT}
-----It's right?
If it is correct, I'll modify patch ASAP.
I think it's better to register this patch to the next commit fest so
as not to forget.
-----Original Message-----
From: Andres Freund [mailto:andres@anarazel.de]
Sent: Tuesday, November 14, 2017 6:07 PM
To: Moon Insung
Cc: 'PostgreSQL Hackers'
Subject: Re: [PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer stateHi,
On 2017-11-14 17:57:00 +0900, Moon Insung wrote:
So I add a state column to pg_buffercache view so that I could print a value indicating the state of the buffer.
This is outpu as an unit32 type, and examples are shown below.-----
postgres=# select * from pg_buffercache where bufferid = 1; -[ RECORD
1 ]----+-----------
bufferid | 1
relfilenode | 1262
reltablespace | 1664
reldatabase | 0
relforknumber | 0
relblocknumber | 0
isdirty | f
usagecount | 5
pinning_backends | 0
buffer_state | 2203320320 <- it's a new column
-----I'm disinclined to exposing state that way. It's an internal representation that's not unlikely to change. Sure,
pg_buffercache is more of a debugging / investigatory tool, but I nevertheless see no reason to expose it that way.If we shared those flags more in a manner like you did below:
1 | 1262 | {LOCKED,VALID,TAG_VALID,PERMANENT}
that'd be more acceptable. However doing that by default would have some performance downsides, because we'd need to
create these arrays for every row.One way around that would be to create a buffer_state type that's returned by pg_buffercache and then only decoded when
outputting. Doing that + having a cast to an array seems like it'd provide most of the needed functionality?
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center