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

Commit1dc1186

Browse files
committed
Fix pgstattuple functions to use regclass-type as the argument.
This allows us to specify the target relation with several expressions,'relname', 'schemaname.relname' and OID in all pgstattuple functions.pgstatindex() and pg_relpages() could not accept OID as the argumentso far.Per discussion on -hackers, we decided to keep two types of interfaces,with regclass-type and TEXT-type argument, for each pgstattuplefunction because of the backward-compatibility issue. The functionswhich have TEXT-type argument will be deprecated in the future release.Patch by Satoshi Nagayasu, reviewed by Rushabh Lathia and Fujii Masao.
1 parentd26888b commit1dc1186

File tree

8 files changed

+280
-29
lines changed

8 files changed

+280
-29
lines changed

‎contrib/pgstattuple/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@ MODULE_big= pgstattuple
44
OBJS= pgstattuple.o pgstatindex.o
55

66
EXTENSION = pgstattuple
7-
DATA = pgstattuple--1.1.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
7+
DATA = pgstattuple--1.2.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql
88

99
REGRESS = pgstattuple
1010

‎contrib/pgstattuple/expected/pgstattuple.out

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,24 +5,78 @@ CREATE EXTENSION pgstattuple;
55
-- indexes should be that.
66
--
77
create table test (a int primary key, b int[]);
8+
select * from pgstattuple('test');
9+
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
10+
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
11+
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
12+
(1 row)
13+
814
select * from pgstattuple('test'::text);
915
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
1016
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
1117
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
1218
(1 row)
1319

20+
select * from pgstattuple('test'::name);
21+
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
22+
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
23+
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
24+
(1 row)
25+
1426
select * from pgstattuple('test'::regclass);
1527
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
1628
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
1729
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
1830
(1 row)
1931

32+
select pgstattuple(oid) from pg_class where relname = 'test';
33+
pgstattuple
34+
---------------------
35+
(0,0,0,0,0,0,0,0,0)
36+
(1 row)
37+
38+
select pgstattuple(relname) from pg_class where relname = 'test';
39+
pgstattuple
40+
---------------------
41+
(0,0,0,0,0,0,0,0,0)
42+
(1 row)
43+
2044
select * from pgstatindex('test_pkey');
2145
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
2246
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2347
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
2448
(1 row)
2549

50+
select * from pgstatindex('test_pkey'::text);
51+
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
52+
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
53+
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
54+
(1 row)
55+
56+
select * from pgstatindex('test_pkey'::name);
57+
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
58+
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
59+
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
60+
(1 row)
61+
62+
select * from pgstatindex('test_pkey'::regclass);
63+
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
64+
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
65+
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
66+
(1 row)
67+
68+
select pgstatindex(oid) from pg_class where relname = 'test_pkey';
69+
pgstatindex
70+
---------------------------
71+
(2,0,0,0,0,0,0,0,NaN,NaN)
72+
(1 row)
73+
74+
select pgstatindex(relname) from pg_class where relname = 'test_pkey';
75+
pgstatindex
76+
---------------------------
77+
(2,0,0,0,0,0,0,0,NaN,NaN)
78+
(1 row)
79+
2680
select pg_relpages('test');
2781
pg_relpages
2882
-------------
@@ -35,6 +89,36 @@ select pg_relpages('test_pkey');
3589
1
3690
(1 row)
3791

92+
select pg_relpages('test_pkey'::text);
93+
pg_relpages
94+
-------------
95+
1
96+
(1 row)
97+
98+
select pg_relpages('test_pkey'::name);
99+
pg_relpages
100+
-------------
101+
1
102+
(1 row)
103+
104+
select pg_relpages('test_pkey'::regclass);
105+
pg_relpages
106+
-------------
107+
1
108+
(1 row)
109+
110+
select pg_relpages(oid) from pg_class where relname = 'test_pkey';
111+
pg_relpages
112+
-------------
113+
1
114+
(1 row)
115+
116+
select pg_relpages(relname) from pg_class where relname = 'test_pkey';
117+
pg_relpages
118+
-------------
119+
1
120+
(1 row)
121+
38122
create index test_ginidx on test using gin (b);
39123
select * from pgstatginindex('test_ginidx');
40124
version | pending_pages | pending_tuples

‎contrib/pgstattuple/pgstatindex.c

Lines changed: 66 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -39,12 +39,24 @@
3939
#include"utils/rel.h"
4040

4141

42+
/*
43+
* Because of backward-compatibility issue, we have decided to have
44+
* two types of interfaces, with regclass-type input arg and text-type
45+
* input arg, for each function.
46+
*
47+
* Those functions which have text-type input arg will be deprecated
48+
* in the future release.
49+
*/
4250
externDatumpgstatindex(PG_FUNCTION_ARGS);
51+
externDatumpgstatindexbyid(PG_FUNCTION_ARGS);
4352
externDatumpg_relpages(PG_FUNCTION_ARGS);
53+
externDatumpg_relpagesbyid(PG_FUNCTION_ARGS);
4454
externDatumpgstatginindex(PG_FUNCTION_ARGS);
4555

4656
PG_FUNCTION_INFO_V1(pgstatindex);
57+
PG_FUNCTION_INFO_V1(pgstatindexbyid);
4758
PG_FUNCTION_INFO_V1(pg_relpages);
59+
PG_FUNCTION_INFO_V1(pg_relpagesbyid);
4860
PG_FUNCTION_INFO_V1(pgstatginindex);
4961

5062
#defineIS_INDEX(r) ((r)->rd_rel->relkind == RELKIND_INDEX)
@@ -97,6 +109,8 @@ typedef struct GinIndexStat
97109
int64pending_tuples;
98110
}GinIndexStat;
99111

112+
staticDatumpgstatindex_impl(Relationrel,FunctionCallInfofcinfo);
113+
100114
/* ------------------------------------------------------
101115
* pgstatindex()
102116
*
@@ -109,11 +123,6 @@ pgstatindex(PG_FUNCTION_ARGS)
109123
text*relname=PG_GETARG_TEXT_P(0);
110124
Relationrel;
111125
RangeVar*relrv;
112-
Datumresult;
113-
BlockNumbernblocks;
114-
BlockNumberblkno;
115-
BTIndexStatindexStat;
116-
BufferAccessStrategybstrategy=GetAccessStrategy(BAS_BULKREAD);
117126

118127
if (!superuser())
119128
ereport(ERROR,
@@ -123,6 +132,34 @@ pgstatindex(PG_FUNCTION_ARGS)
123132
relrv=makeRangeVarFromNameList(textToQualifiedNameList(relname));
124133
rel=relation_openrv(relrv,AccessShareLock);
125134

135+
PG_RETURN_DATUM(pgstatindex_impl(rel,fcinfo));
136+
}
137+
138+
Datum
139+
pgstatindexbyid(PG_FUNCTION_ARGS)
140+
{
141+
Oidrelid=PG_GETARG_OID(0);
142+
Relationrel;
143+
144+
if (!superuser())
145+
ereport(ERROR,
146+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
147+
(errmsg("must be superuser to use pgstattuple functions"))));
148+
149+
rel=relation_open(relid,AccessShareLock);
150+
151+
PG_RETURN_DATUM(pgstatindex_impl(rel,fcinfo));
152+
}
153+
154+
staticDatum
155+
pgstatindex_impl(Relationrel,FunctionCallInfofcinfo)
156+
{
157+
Datumresult;
158+
BlockNumbernblocks;
159+
BlockNumberblkno;
160+
BTIndexStatindexStat;
161+
BufferAccessStrategybstrategy=GetAccessStrategy(BAS_BULKREAD);
162+
126163
if (!IS_INDEX(rel)|| !IS_BTREE(rel))
127164
elog(ERROR,"relation \"%s\" is not a btree index",
128165
RelationGetRelationName(rel));
@@ -274,7 +311,7 @@ pgstatindex(PG_FUNCTION_ARGS)
274311
result=HeapTupleGetDatum(tuple);
275312
}
276313

277-
PG_RETURN_DATUM(result);
314+
returnresult;
278315
}
279316

280317
/* --------------------------------------------------------
@@ -311,6 +348,29 @@ pg_relpages(PG_FUNCTION_ARGS)
311348
PG_RETURN_INT64(relpages);
312349
}
313350

351+
Datum
352+
pg_relpagesbyid(PG_FUNCTION_ARGS)
353+
{
354+
Oidrelid=PG_GETARG_OID(0);
355+
int64relpages;
356+
Relationrel;
357+
358+
if (!superuser())
359+
ereport(ERROR,
360+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
361+
(errmsg("must be superuser to use pgstattuple functions"))));
362+
363+
rel=relation_open(relid,AccessShareLock);
364+
365+
/* note: this will work OK on non-local temp tables */
366+
367+
relpages=RelationGetNumberOfBlocks(rel);
368+
369+
relation_close(rel,AccessShareLock);
370+
371+
PG_RETURN_INT64(relpages);
372+
}
373+
314374
/* ------------------------------------------------------
315375
* pgstatginindex()
316376
*
Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
/* contrib/pgstattuple/pgstattuple--1.1--1.2.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pgstattuple UPDATE TO '1.2'" to load this file. \quit
5+
6+
ALTER EXTENSION pgstattuple DROP FUNCTION pgstattuple(oid);
7+
DROPFUNCTION pgstattuple(oid);
8+
9+
CREATEFUNCTIONpgstattuple(IN reloid regclass,
10+
OUT table_lenBIGINT,-- physical table length in bytes
11+
OUT tuple_countBIGINT,-- number of live tuples
12+
OUT tuple_lenBIGINT,-- total tuples length in bytes
13+
OUT tuple_percent FLOAT8,-- live tuples in %
14+
OUT dead_tuple_countBIGINT,-- number of dead tuples
15+
OUT dead_tuple_lenBIGINT,-- total dead tuples length in bytes
16+
OUT dead_tuple_percent FLOAT8,-- dead tuples in %
17+
OUT free_spaceBIGINT,-- free space in bytes
18+
OUT free_percent FLOAT8)-- free space in %
19+
AS'MODULE_PATHNAME','pgstattuplebyid'
20+
LANGUAGE C STRICT;
21+
22+
CREATEFUNCTIONpgstatindex(IN relname regclass,
23+
OUT versionINT,
24+
OUT tree_levelINT,
25+
OUT index_sizeBIGINT,
26+
OUT root_block_noBIGINT,
27+
OUT internal_pagesBIGINT,
28+
OUT leaf_pagesBIGINT,
29+
OUT empty_pagesBIGINT,
30+
OUT deleted_pagesBIGINT,
31+
OUT avg_leaf_density FLOAT8,
32+
OUT leaf_fragmentation FLOAT8)
33+
AS'MODULE_PATHNAME','pgstatindexbyid'
34+
LANGUAGE C STRICT;
35+
36+
CREATEFUNCTIONpg_relpages(IN relname regclass)
37+
RETURNSBIGINT
38+
AS'MODULE_PATHNAME','pg_relpagesbyid'
39+
LANGUAGE C STRICT;

‎contrib/pgstattuple/pgstattuple--1.1.sqlrenamed to‎contrib/pgstattuple/pgstattuple--1.2.sql

Lines changed: 35 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* contrib/pgstattuple/pgstattuple--1.1.sql*/
1+
/* contrib/pgstattuple/pgstattuple--1.2.sql*/
22

33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use"CREATE EXTENSION pgstattuple" to load this file. \quit
@@ -16,19 +16,6 @@ CREATE FUNCTION pgstattuple(IN relname text,
1616
AS'MODULE_PATHNAME','pgstattuple'
1717
LANGUAGE C STRICT;
1818

19-
CREATEFUNCTIONpgstattuple(IN reloidoid,
20-
OUT table_lenBIGINT,-- physical table length in bytes
21-
OUT tuple_countBIGINT,-- number of live tuples
22-
OUT tuple_lenBIGINT,-- total tuples length in bytes
23-
OUT tuple_percent FLOAT8,-- live tuples in %
24-
OUT dead_tuple_countBIGINT,-- number of dead tuples
25-
OUT dead_tuple_lenBIGINT,-- total dead tuples length in bytes
26-
OUT dead_tuple_percent FLOAT8,-- dead tuples in %
27-
OUT free_spaceBIGINT,-- free space in bytes
28-
OUT free_percent FLOAT8)-- free space in %
29-
AS'MODULE_PATHNAME','pgstattuplebyid'
30-
LANGUAGE C STRICT;
31-
3219
CREATEFUNCTIONpgstatindex(IN relnametext,
3320
OUT versionINT,
3421
OUT tree_levelINT,
@@ -56,3 +43,37 @@ CREATE FUNCTION pgstatginindex(IN relname regclass,
5643
OUT pending_tuplesBIGINT)
5744
AS'MODULE_PATHNAME','pgstatginindex'
5845
LANGUAGE C STRICT;
46+
47+
/* New stuff in 1.2 begins here*/
48+
49+
CREATEFUNCTIONpgstattuple(IN reloid regclass,
50+
OUT table_lenBIGINT,-- physical table length in bytes
51+
OUT tuple_countBIGINT,-- number of live tuples
52+
OUT tuple_lenBIGINT,-- total tuples length in bytes
53+
OUT tuple_percent FLOAT8,-- live tuples in %
54+
OUT dead_tuple_countBIGINT,-- number of dead tuples
55+
OUT dead_tuple_lenBIGINT,-- total dead tuples length in bytes
56+
OUT dead_tuple_percent FLOAT8,-- dead tuples in %
57+
OUT free_spaceBIGINT,-- free space in bytes
58+
OUT free_percent FLOAT8)-- free space in %
59+
AS'MODULE_PATHNAME','pgstattuplebyid'
60+
LANGUAGE C STRICT;
61+
62+
CREATEFUNCTIONpgstatindex(IN relname regclass,
63+
OUT versionINT,
64+
OUT tree_levelINT,
65+
OUT index_sizeBIGINT,
66+
OUT root_block_noBIGINT,
67+
OUT internal_pagesBIGINT,
68+
OUT leaf_pagesBIGINT,
69+
OUT empty_pagesBIGINT,
70+
OUT deleted_pagesBIGINT,
71+
OUT avg_leaf_density FLOAT8,
72+
OUT leaf_fragmentation FLOAT8)
73+
AS'MODULE_PATHNAME','pgstatindexbyid'
74+
LANGUAGE C STRICT;
75+
76+
CREATEFUNCTIONpg_relpages(IN relname regclass)
77+
RETURNSBIGINT
78+
AS'MODULE_PATHNAME','pg_relpagesbyid'
79+
LANGUAGE C STRICT;
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pgstattuple extension
22
comment = 'show tuple-level statistics'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/pgstattuple'
55
relocatable = true

‎contrib/pgstattuple/sql/pgstattuple.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,13 +8,27 @@ CREATE EXTENSION pgstattuple;
88

99
createtabletest (aintprimary key, bint[]);
1010

11+
select*from pgstattuple('test');
1112
select*from pgstattuple('test'::text);
13+
select*from pgstattuple('test'::name);
1214
select*from pgstattuple('test'::regclass);
15+
select pgstattuple(oid)from pg_classwhere relname='test';
16+
select pgstattuple(relname)from pg_classwhere relname='test';
1317

1418
select*from pgstatindex('test_pkey');
19+
select*from pgstatindex('test_pkey'::text);
20+
select*from pgstatindex('test_pkey'::name);
21+
select*from pgstatindex('test_pkey'::regclass);
22+
select pgstatindex(oid)from pg_classwhere relname='test_pkey';
23+
select pgstatindex(relname)from pg_classwhere relname='test_pkey';
1524

1625
select pg_relpages('test');
1726
select pg_relpages('test_pkey');
27+
select pg_relpages('test_pkey'::text);
28+
select pg_relpages('test_pkey'::name);
29+
select pg_relpages('test_pkey'::regclass);
30+
select pg_relpages(oid)from pg_classwhere relname='test_pkey';
31+
select pg_relpages(relname)from pg_classwhere relname='test_pkey';
1832

1933
createindextest_ginidxon test using gin (b);
2034

0 commit comments

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