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

Commit8970837

Browse files
committed
Fix pgstattuple to acquire a read lock on the target table. This
prevents embarassments such as having the table dropped or truncatedpartway through the scan. Also, fix free space calculation to includepages that currently contain no tuples.
1 parent9aa2e7d commit8970837

File tree

2 files changed

+38
-20
lines changed

2 files changed

+38
-20
lines changed

‎contrib/pgstattuple/README.pgstattuple‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ NOTICE: physical length: 0.08MB live tuples: 20 (0.00MB, 1.17%) dead tuples: 32
1515
18.75
1616
(1 row)
1717

18-
Above example shows tellerstables includes 18.75% dead tuples.
18+
Above example shows tellerstable includes 18.75% dead tuples.
1919

2020
physical lengthphysical size of the table in MB
2121
live tuplesinformation on the live tuples
@@ -40,7 +40,7 @@ NOTICE: physical length: 0.08MB live tuples: 20 (0.00MB, 1.17%) dead tuples: 32
4040

4141
4. Notes
4242

43-
pgstattupledoes not lock the target table at all. So concurrent
43+
pgstattupleacquires only a read lock on the table. So concurrent
4444
update may affect the result.
4545

4646
pgstattuple judges a tuple is "dead" if HeapTupleSatisfiesNow()

‎contrib/pgstattuple/pgstattuple.c‎

Lines changed: 36 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $Header: /cvsroot/pgsql/contrib/pgstattuple/pgstattuple.c,v 1.2 2001/10/25 05:49:20 momjian Exp $
2+
* $Header: /cvsroot/pgsql/contrib/pgstattuple/pgstattuple.c,v 1.3 2001/12/19 20:28:41 tgl Exp $
33
*
44
* Copyright (c) 2001 Tatsuo Ishii
55
*
@@ -23,6 +23,7 @@
2323
*/
2424

2525
#include"postgres.h"
26+
2627
#include"fmgr.h"
2728
#include"access/heapam.h"
2829
#include"access/transam.h"
@@ -48,20 +49,21 @@ pgstattuple(PG_FUNCTION_ARGS)
4849
HeapScanDescscan;
4950
HeapTupletuple;
5051
BlockNumbernblocks;
51-
BlockNumberblock=InvalidBlockNumber;
52+
BlockNumberblock=0;/* next block to count free space in */
53+
BlockNumbertupblock;
54+
Bufferbuffer;
5255
doubletable_len;
5356
uint64tuple_len=0;
5457
uint64dead_tuple_len=0;
55-
uint32tuple_count=0;
56-
uint32dead_tuple_count=0;
58+
uint64tuple_count=0;
59+
uint64dead_tuple_count=0;
5760
doubletuple_percent;
5861
doubledead_tuple_percent;
5962

60-
Bufferbuffer=InvalidBuffer;
6163
uint64free_space=0;/* free/reusable space in bytes */
6264
doublefree_percent;/* free/reusable space in % */
6365

64-
rel=heap_openr(NameStr(*p),NoLock);
66+
rel=heap_openr(NameStr(*p),AccessShareLock);
6567
nblocks=RelationGetNumberOfBlocks(rel);
6668
scan=heap_beginscan(rel, false,SnapshotAny,0,NULL);
6769

@@ -78,17 +80,33 @@ pgstattuple(PG_FUNCTION_ARGS)
7880
dead_tuple_count++;
7981
}
8082

81-
if (!BlockNumberIsValid(block)||
82-
block!=BlockIdGetBlockNumber(&tuple->t_self.ip_blkid))
83+
/*
84+
* To avoid physically reading the table twice, try to do the
85+
* free-space scan in parallel with the heap scan. However,
86+
* heap_getnext may find no tuples on a given page, so we cannot
87+
* simply examine the pages returned by the heap scan.
88+
*/
89+
tupblock=BlockIdGetBlockNumber(&tuple->t_self.ip_blkid);
90+
91+
while (block <=tupblock)
8392
{
84-
block=BlockIdGetBlockNumber(&tuple->t_self.ip_blkid);
8593
buffer=ReadBuffer(rel,block);
8694
free_space+=PageGetFreeSpace((Page)BufferGetPage(buffer));
8795
ReleaseBuffer(buffer);
96+
block++;
8897
}
8998
}
9099
heap_endscan(scan);
91-
heap_close(rel,NoLock);
100+
101+
while (block<nblocks)
102+
{
103+
buffer=ReadBuffer(rel,block);
104+
free_space+=PageGetFreeSpace((Page)BufferGetPage(buffer));
105+
ReleaseBuffer(buffer);
106+
block++;
107+
}
108+
109+
heap_close(rel,AccessShareLock);
92110

93111
table_len= (double)nblocks*BLCKSZ;
94112

@@ -105,20 +123,20 @@ pgstattuple(PG_FUNCTION_ARGS)
105123
free_percent= (double)free_space*100.0 /table_len;
106124
}
107125

108-
elog(NOTICE,"physical length: %.2fMB live tuples: %u (%.2fMB, %.2f%%) dead tuples: %u (%.2fMB, %.2f%%) free/reusable space: %.2fMB (%.2f%%) overhead: %.2f%%",
126+
elog(NOTICE,"physical length: %.2fMB live tuples: %.0f (%.2fMB, %.2f%%) dead tuples: %.0f (%.2fMB, %.2f%%) free/reusable space: %.2fMB (%.2f%%) overhead: %.2f%%",
109127

110-
table_len /1024/1024,/*phsical length in MB */
128+
table_len /(1024*1024),/*physical length in MB */
111129

112-
tuple_count,/* number of live tuples */
113-
(double)tuple_len /1024/1024,/* live tuples in MB */
130+
(double)tuple_count,/* number of live tuples */
131+
(double)tuple_len /(1024*1024),/* live tuples in MB */
114132
tuple_percent,/* live tuples in % */
115133

116-
dead_tuple_count,/* number of dead tuples */
117-
(double)dead_tuple_len /1024/1024,/* dead tuples in MB */
134+
(double)dead_tuple_count,/* number of dead tuples */
135+
(double)dead_tuple_len /(1024*1024),/* dead tuples in MB */
118136
dead_tuple_percent,/* dead tuples in % */
119137

120-
(double)free_space /1024/1024,/* free/available space in
121-
* MB */
138+
(double)free_space /(1024*1024),/* free/available space in
139+
* MB */
122140

123141
free_percent,/* free/available space in % */
124142

0 commit comments

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