Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd5dd3d4

Browse files
committed
Add contrib/pg_freespacemap to display free space map information.
Mark Kirkwood
1 parent6c0d4aa commitd5dd3d4

File tree

8 files changed

+481
-98
lines changed

8 files changed

+481
-98
lines changed

‎contrib/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# $PostgreSQL: pgsql/contrib/Makefile,v 1.61 2005/09/01 22:02:44 tgl Exp $
1+
# $PostgreSQL: pgsql/contrib/Makefile,v 1.62 2006/02/12 03:55:52 momjian Exp $
22

33
subdir = contrib
44
top_builddir = ..
@@ -21,6 +21,7 @@ WANTED_DIRS = \
2121
ltree\
2222
oid2name\
2323
pg_buffercache\
24+
pg_freespacemap\
2425
pg_trgm\
2526
pgbench\
2627
pgcrypto\

‎contrib/README

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -106,6 +106,10 @@ pg_buffercache -
106106
Real time queries on the shared buffer cache
107107
by Mark Kirkwood <markir@paradise.net.nz>
108108

109+
pg_freespacemap -
110+
Displays the contents of the free space map (FSM)
111+
by Mark Kirkwood <markir@paradise.net.nz>
112+
109113
pg_trgm -
110114
Functions for determining the similarity of text based on trigram
111115
matching.

‎contrib/pg_freespacemap/Makefile

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
# $PostgreSQL: pgsql/contrib/pg_freespacemap/Makefile,v 1.1 2006/02/12 03:55:53 momjian Exp $
2+
3+
MODULE_big = pg_freespacemap
4+
OBJS= pg_freespacemap.o
5+
6+
DATA_built = pg_freespacemap.sql
7+
DOCS = README.pg_freespacemap
8+
9+
ifdefUSE_PGXS
10+
PGXS :=$(shell pg_config --pgxs)
11+
include$(PGXS)
12+
else
13+
subdir = contrib/pg_freespacemap
14+
top_builddir = ../..
15+
include$(top_builddir)/src/Makefile.global
16+
include$(top_srcdir)/contrib/contrib-global.mk
17+
endif
Lines changed: 98 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,98 @@
1+
Pg_freespacemap - Real time queries on the free space map (FSM).
2+
---------------
3+
4+
This module consists of a C function 'pg_freespacemap()' that returns
5+
a set of records, and a view 'pg_freespacemap' to wrapper the function.
6+
7+
The module provides the ability to examine the contents of the free space
8+
map, without having to restart or rebuild the server with additional
9+
debugging code.
10+
11+
By default public access is REVOKED from both of these, just in case there
12+
are security issues lurking.
13+
14+
15+
Installation
16+
------------
17+
18+
Build and install the main Postgresql source, then this contrib module:
19+
20+
$ cd contrib/pg_freespacemap
21+
$ gmake
22+
$ gmake install
23+
24+
25+
To register the functions:
26+
27+
$ psql -d <database> -f pg_freespacemap.sql
28+
29+
30+
Notes
31+
-----
32+
33+
The definition of the columns exposed in the view is:
34+
35+
Column | references | Description
36+
----------------+----------------------+------------------------------------
37+
blockid | | Id, 1.. max_fsm_pages
38+
relfilenode | pg_class.relfilenode | Refilenode of the relation.
39+
reltablespace | pg_tablespace.oid | Tablespace oid of the relation.
40+
reldatabase | pg_database.oid | Database for the relation.
41+
relblocknumber | | Offset of the page in the relation.
42+
blockfreebytes | | Free bytes in the block/page.
43+
44+
45+
There is one row for each page in the free space map.
46+
47+
Because the map is shared by all the databases, there are pages from
48+
relations not belonging to the current database.
49+
50+
When the pg_freespacemap view is accessed, internal free space map locks are
51+
taken, and a copy of the map data is made for the view to display.
52+
This ensures that the view produces a consistent set of results, while not
53+
blocking normal activity longer than necessary. Nonetheless there
54+
could be some impact on database performance if this view is read often.
55+
56+
57+
Sample output
58+
-------------
59+
60+
regression=# \d pg_freespacemap
61+
View "public.pg_freespacemap"
62+
Column | Type | Modifiers
63+
---------------+---------+-----------
64+
blockid | integer |
65+
relfilenode | oid |
66+
reltablespace | oid |
67+
reldatabase | oid |
68+
relblocknumber | bigint |
69+
blockfreebytes | integer |
70+
View definition:
71+
SELECT p.blockid, p.relfilenode, p.reltablespace, p.reldatabase, p.relblocknumber, p.blockfreebytes
72+
FROM pg_freespacemap() p(blockid integer, relfilenode oid, reltablespace oid, reldatabase oid, relblocknumber bigint, blockfreebytes integer);
73+
74+
regression=# SELECT c.relname, m.relblocknumber, m.blockfreebytes
75+
FROM pg_freespacemap m INNER JOIN pg_class c
76+
ON c.relfilenode = m.relfilenode LIMIT 10;
77+
relname | relblocknumber | blockfreebytes
78+
------------------------+----------------+----------------
79+
sql_features | 5 | 2696
80+
sql_implementation_info | 0 | 7104
81+
sql_languages | 0 | 8016
82+
sql_packages | 0 | 7376
83+
sql_sizing | 0 | 6032
84+
pg_authid | 0 | 7424
85+
pg_toast_2618 | 13 | 4588
86+
pg_toast_2618 | 12 | 1680
87+
pg_toast_2618 | 10 | 1436
88+
pg_toast_2618 | 7 | 1136
89+
(10 rows)
90+
91+
regression=#
92+
93+
94+
Author
95+
------
96+
97+
* Mark Kirkwood <markir@paradise.net.nz>
98+
Lines changed: 231 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,231 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* pg_freespacemap.c
4+
* display some contents of the free space map.
5+
*
6+
* $PostgreSQL: pgsql/contrib/pg_freespacemap/pg_freespacemap.c,v 1.1 2006/02/12 03:55:53 momjian Exp $
7+
*-------------------------------------------------------------------------
8+
*/
9+
#include"postgres.h"
10+
#include"funcapi.h"
11+
#include"catalog/pg_type.h"
12+
#include"storage/freespace.h"
13+
#include"utils/relcache.h"
14+
15+
#defineNUM_FREESPACE_PAGES_ELEM 6
16+
17+
#if defined(WIN32)|| defined(__CYGWIN__)
18+
externDLLIMPORTvolatileuint32InterruptHoldoffCount;
19+
#endif
20+
21+
Datumpg_freespacemap(PG_FUNCTION_ARGS);
22+
23+
24+
/*
25+
* Record structure holding the to be exposed free space data.
26+
*/
27+
typedefstruct
28+
{
29+
30+
uint32blockid;
31+
uint32relfilenode;
32+
uint32reltablespace;
33+
uint32reldatabase;
34+
uint32relblocknumber;
35+
uint32blockfreebytes;
36+
37+
}FreeSpacePagesRec;
38+
39+
40+
/*
41+
* Function context for data persisting over repeated calls.
42+
*/
43+
typedefstruct
44+
{
45+
46+
AttInMetadata*attinmeta;
47+
FreeSpacePagesRec*record;
48+
char*values[NUM_FREESPACE_PAGES_ELEM];
49+
50+
}FreeSpacePagesContext;
51+
52+
53+
/*
54+
* Function returning data from the Free Space Map (FSM).
55+
*/
56+
PG_FUNCTION_INFO_V1(pg_freespacemap);
57+
Datum
58+
pg_freespacemap(PG_FUNCTION_ARGS)
59+
{
60+
61+
FuncCallContext*funcctx;
62+
Datumresult;
63+
MemoryContextoldcontext;
64+
FreeSpacePagesContext*fctx;/* User function context. */
65+
TupleDesctupledesc;
66+
HeapTupletuple;
67+
68+
FSMHeader*FreeSpaceMap;/* FSM main structure. */
69+
FSMRelation*fsmrel;/* Individual relation. */
70+
71+
72+
if (SRF_IS_FIRSTCALL())
73+
{
74+
uint32i;
75+
uint32numPages;/* Max possible no. of pages in map. */
76+
intnPages;/* Mapped pages for a relation. */
77+
78+
/*
79+
* Get the free space map data structure.
80+
*/
81+
FreeSpaceMap=GetFreeSpaceMap();
82+
83+
numPages=MaxFSMPages;
84+
85+
funcctx=SRF_FIRSTCALL_INIT();
86+
87+
/* Switch context when allocating stuff to be used in later calls */
88+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
89+
90+
/* Construct a tuple to return. */
91+
tupledesc=CreateTemplateTupleDesc(NUM_FREESPACE_PAGES_ELEM, false);
92+
TupleDescInitEntry(tupledesc, (AttrNumber)1,"blockid",
93+
INT4OID,-1,0);
94+
TupleDescInitEntry(tupledesc, (AttrNumber)2,"relfilenode",
95+
OIDOID,-1,0);
96+
TupleDescInitEntry(tupledesc, (AttrNumber)3,"reltablespace",
97+
OIDOID,-1,0);
98+
TupleDescInitEntry(tupledesc, (AttrNumber)4,"reldatabase",
99+
OIDOID,-1,0);
100+
TupleDescInitEntry(tupledesc, (AttrNumber)5,"relblocknumber",
101+
INT8OID,-1,0);
102+
TupleDescInitEntry(tupledesc, (AttrNumber)6,"blockfreebytes",
103+
INT4OID,-1,0);
104+
105+
/* Generate attribute metadata needed later to produce tuples */
106+
funcctx->attinmeta=TupleDescGetAttInMetadata(tupledesc);
107+
108+
/*
109+
* Create a function context for cross-call persistence and initialize
110+
* the counters.
111+
*/
112+
fctx= (FreeSpacePagesContext*)palloc(sizeof(FreeSpacePagesContext));
113+
funcctx->user_fctx=fctx;
114+
115+
/* Set an upper bound on the calls */
116+
funcctx->max_calls=numPages;
117+
118+
119+
/* Allocate numPages worth of FreeSpacePagesRec records, this is also
120+
* an upper bound.
121+
*/
122+
fctx->record= (FreeSpacePagesRec*)palloc(sizeof(FreeSpacePagesRec)*numPages);
123+
124+
/* allocate the strings for tuple formation */
125+
fctx->values[0]= (char*)palloc(3*sizeof(uint32)+1);
126+
fctx->values[1]= (char*)palloc(3*sizeof(uint32)+1);
127+
fctx->values[2]= (char*)palloc(3*sizeof(uint32)+1);
128+
fctx->values[3]= (char*)palloc(3*sizeof(uint32)+1);
129+
fctx->values[4]= (char*)palloc(3*sizeof(uint32)+1);
130+
fctx->values[5]= (char*)palloc(3*sizeof(uint32)+1);
131+
132+
133+
/* Return to original context when allocating transient memory */
134+
MemoryContextSwitchTo(oldcontext);
135+
136+
137+
/*
138+
* Lock free space map and scan though all the relations,
139+
* for each relation, gets all its mapped pages.
140+
*/
141+
LWLockAcquire(FreeSpaceLock,LW_EXCLUSIVE);
142+
143+
144+
i=0;
145+
146+
for (fsmrel=FreeSpaceMap->usageList;fsmrel;fsmrel=fsmrel->nextUsage)
147+
{
148+
149+
if (fsmrel->isIndex)
150+
{/* Index relation. */
151+
IndexFSMPageData*page;
152+
153+
page= (IndexFSMPageData*)
154+
(FreeSpaceMap->arena+fsmrel->firstChunk*CHUNKBYTES);
155+
156+
for (nPages=0;nPages<fsmrel->storedPages;nPages++)
157+
{
158+
159+
fctx->record[i].blockid=i;
160+
fctx->record[i].relfilenode=fsmrel->key.relNode;
161+
fctx->record[i].reltablespace=fsmrel->key.spcNode;
162+
fctx->record[i].reldatabase=fsmrel->key.dbNode;
163+
fctx->record[i].relblocknumber=IndexFSMPageGetPageNum(page);
164+
fctx->record[i].blockfreebytes=0;/* index.*/
165+
166+
page++;
167+
i++;
168+
}
169+
}
170+
else
171+
{/* Heap relation. */
172+
FSMPageData*page;
173+
174+
page= (FSMPageData*)
175+
(FreeSpaceMap->arena+fsmrel->firstChunk*CHUNKBYTES);
176+
177+
for (nPages=0;nPages<fsmrel->storedPages;nPages++)
178+
{
179+
fctx->record[i].blockid=i;
180+
fctx->record[i].relfilenode=fsmrel->key.relNode;
181+
fctx->record[i].reltablespace=fsmrel->key.spcNode;
182+
fctx->record[i].reldatabase=fsmrel->key.dbNode;
183+
fctx->record[i].relblocknumber=FSMPageGetPageNum(page);
184+
fctx->record[i].blockfreebytes=FSMPageGetSpace(page);
185+
186+
page++;
187+
i++;
188+
}
189+
190+
}
191+
192+
}
193+
194+
/* Set the real no. of calls as we know it now! */
195+
funcctx->max_calls=i;
196+
197+
/* Release free space map. */
198+
LWLockRelease(FreeSpaceLock);
199+
}
200+
201+
funcctx=SRF_PERCALL_SETUP();
202+
203+
/* Get the saved state */
204+
fctx=funcctx->user_fctx;
205+
206+
207+
if (funcctx->call_cntr<funcctx->max_calls)
208+
{
209+
uint32i=funcctx->call_cntr;
210+
211+
212+
sprintf(fctx->values[0],"%u",fctx->record[i].blockid);
213+
sprintf(fctx->values[1],"%u",fctx->record[i].relfilenode);
214+
sprintf(fctx->values[2],"%u",fctx->record[i].reltablespace);
215+
sprintf(fctx->values[3],"%u",fctx->record[i].reldatabase);
216+
sprintf(fctx->values[4],"%u",fctx->record[i].relblocknumber);
217+
sprintf(fctx->values[5],"%u",fctx->record[i].blockfreebytes);
218+
219+
220+
221+
/* Build and return the tuple. */
222+
tuple=BuildTupleFromCStrings(funcctx->attinmeta,fctx->values);
223+
result=HeapTupleGetDatum(tuple);
224+
225+
226+
SRF_RETURN_NEXT(funcctx,result);
227+
}
228+
else
229+
SRF_RETURN_DONE(funcctx);
230+
231+
}
Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
-- Adjust this setting to control where the objects get created.
2+
BEGIN;
3+
SET search_path = public;
4+
5+
-- Register the function.
6+
CREATE OR REPLACE FUNCTION pg_freespacemap()
7+
RETURNS SETOF RECORD
8+
AS 'MODULE_PATHNAME', 'pg_freespacemap'
9+
LANGUAGE 'C';
10+
11+
-- Create a view for convenient access.
12+
CREATE VIEW pg_freespacemap AS
13+
SELECT P.* FROM pg_freespacemap() AS P
14+
(blockid int4, relfilenode oid, reltablespace oid, reldatabase oid, relblocknumber int8, blockfreebytes int4);
15+
16+
-- Don't want these to be available at public.
17+
REVOKE ALL ON FUNCTION pg_freespacemap() FROM PUBLIC;
18+
REVOKE ALL ON pg_freespacemap FROM PUBLIC;
19+
COMMIT;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp