Skip to content

Commit 8970837

Browse files
tglsfdctglsfdc
committed
Fix pgstattuple to acquire a read lock on the target table. This
prevents embarassments such as having the table dropped or truncated partway through the scan. Also, fix free space calculation to include pages that currently contain no tuples.
1 parent 9aa2e7d commit 8970837

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 tellers tables includes 18.75% dead tuples.
18+
Above example shows tellers table includes 18.75% dead tuples.
1919

2020
physical length physical size of the table in MB
2121
live tuples information 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-
pgstattuple does not lock the target table at all. So concurrent
43+
pgstattuple acquires 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
HeapScanDesc scan;
4950
HeapTuple tuple;
5051
BlockNumber nblocks;
51-
BlockNumber block = InvalidBlockNumber;
52+
BlockNumber block = 0; /* next block to count free space in */
53+
BlockNumber tupblock;
54+
Buffer buffer;
5255
double table_len;
5356
uint64 tuple_len = 0;
5457
uint64 dead_tuple_len = 0;
55-
uint32 tuple_count = 0;
56-
uint32 dead_tuple_count = 0;
58+
uint64 tuple_count = 0;
59+
uint64 dead_tuple_count = 0;
5760
double tuple_percent;
5861
double dead_tuple_percent;
5962

60-
Buffer buffer = InvalidBuffer;
6163
uint64 free_space = 0; /* free/reusable space in bytes */
6264
double free_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)