Обсуждение: [PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer state
[PATCH]pg_buffercache add a buffer state column, Add fuction to decode buffer state
От
"Moon Insung"
Дата:
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.
Re: [PATCH]pg_buffercache add a buffer state column, Add fuction todecode buffer state
От
Andres Freund
Дата:
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