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

Commite839c8e

Browse files
committed
Create functions pg_set_relation_stats, pg_clear_relation_stats.
These functions are used to tweak statistics on any relation, providedthat the user has MAINTAIN privilege on the relation, or is the databaseowner.Bump catalog version.Author: Corey HuinkerDiscussion:https://postgr.es/m/CADkLM=eErgzn7ECDpwFcptJKOk9SxZEk5Pot4d94eVTZsvj3gw@mail.gmail.com
1 parent6f782a2 commite839c8e

File tree

12 files changed

+700
-3
lines changed

12 files changed

+700
-3
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 94 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30135,6 +30135,100 @@ DETAIL: Make sure pg_wal_replay_wait() isn't called within a transaction with a
3013530135
</tgroup>
3013630136
</table>
3013730137

30138+
<para>
30139+
<xref linkend="functions-admin-statsmod"/> lists functions used to
30140+
manipulate statistics.
30141+
<warning>
30142+
<para>
30143+
Changes made by these statistics manipulation functions are likely to be
30144+
overwritten by <link linkend="autovacuum">autovacuum</link> (or manual
30145+
<command>VACUUM</command> or <command>ANALYZE</command>) and should be
30146+
considered temporary.
30147+
</para>
30148+
</warning>
30149+
</para>
30150+
30151+
<table id="functions-admin-statsmod">
30152+
<title>Database Object Statistics Manipulation Functions</title>
30153+
<tgroup cols="1">
30154+
<thead>
30155+
<row>
30156+
<entry role="func_table_entry"><para role="func_signature">
30157+
Function
30158+
</para>
30159+
<para>
30160+
Description
30161+
</para></entry>
30162+
</row>
30163+
</thead>
30164+
30165+
<tbody>
30166+
<row>
30167+
<entry role="func_table_entry">
30168+
<para role="func_signature">
30169+
<indexterm>
30170+
<primary>pg_set_relation_stats</primary>
30171+
</indexterm>
30172+
<function>pg_set_relation_stats</function> (
30173+
<parameter>relation</parameter> <type>regclass</type>
30174+
<optional>, <parameter>relpages</parameter> <type>integer</type></optional>
30175+
<optional>, <parameter>reltuples</parameter> <type>real</type></optional>
30176+
<optional>, <parameter>relallvisible</parameter> <type>integer</type></optional> )
30177+
<returnvalue>boolean</returnvalue>
30178+
</para>
30179+
<para>
30180+
Updates relation-level statistics for the given relation to the
30181+
specified values. The parameters correspond to columns in <link
30182+
linkend="catalog-pg-class"><structname>pg_class</structname></link>. Unspecified
30183+
or <literal>NULL</literal> values leave the setting
30184+
unchanged. Returns <literal>true</literal> if a change was made;
30185+
<literal>false</literal> otherwise.
30186+
</para>
30187+
<para>
30188+
Ordinarily, these statistics are collected automatically or updated
30189+
as a part of <xref linkend="sql-vacuum"/> or <xref
30190+
linkend="sql-analyze"/>, so it's not necessary to call this
30191+
function. However, it may be useful when testing the effects of
30192+
statistics on the planner to understand or anticipate plan changes.
30193+
</para>
30194+
<para>
30195+
The caller must have the <literal>MAINTAIN</literal> privilege on
30196+
the table or be the owner of the database.
30197+
</para>
30198+
<para>
30199+
The value of <structfield>relpages</structfield> must be greater than
30200+
or equal to <literal>0</literal>,
30201+
<structfield>reltuples</structfield> must be greater than or equal to
30202+
<literal>-1.0</literal>, and <structfield>relallvisible</structfield>
30203+
must be greater than or equal to <literal>0</literal>.
30204+
</para>
30205+
</entry>
30206+
</row>
30207+
30208+
<row>
30209+
<entry role="func_table_entry">
30210+
<para role="func_signature">
30211+
<indexterm>
30212+
<primary>pg_clear_relation_stats</primary>
30213+
</indexterm>
30214+
<function>pg_clear_relation_stats</function> ( <parameter>relation</parameter> <type>regclass</type> )
30215+
<returnvalue>boolean</returnvalue>
30216+
</para>
30217+
<para>
30218+
Clears table-level statistics for the given relation, as though the
30219+
table was newly created. Returns <literal>true</literal> if a change
30220+
was made; <literal>false</literal> otherwise.
30221+
</para>
30222+
<para>
30223+
The caller must have the <literal>MAINTAIN</literal> privilege on
30224+
the table or be the owner of the database.
30225+
</para>
30226+
</entry>
30227+
</row>
30228+
</tbody>
30229+
</tgroup>
30230+
</table>
30231+
3013830232
<para>
3013930233
<xref linkend="functions-info-partition"/> lists functions that provide
3014030234
information about the structure of partitioned tables.

‎src/backend/catalog/system_functions.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -639,6 +639,16 @@ LANGUAGE INTERNAL
639639
CALLEDONNULL INPUT VOLATILE PARALLEL SAFE
640640
AS'pg_stat_reset_slru';
641641

642+
CREATEOR REPLACE FUNCTION
643+
pg_set_relation_stats(relation regclass,
644+
relpagesinteger DEFAULTNULL,
645+
reltuplesreal DEFAULTNULL,
646+
relallvisibleinteger DEFAULTNULL)
647+
RETURNS bool
648+
LANGUAGE INTERNAL
649+
CALLEDONNULL INPUT VOLATILE
650+
AS'pg_set_relation_stats';
651+
642652
--
643653
-- The default permissions for functions mean that anyone can execute them.
644654
-- A number of functions shouldn't be executable by just anyone, but rather

‎src/backend/statistics/Makefile

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,8 @@ OBJS = \
1616
dependencies.o\
1717
extended_stats.o\
1818
mcv.o\
19-
mvdistinct.o
19+
mvdistinct.o\
20+
relation_stats.o\
21+
stat_utils.o
2022

2123
include$(top_srcdir)/src/backend/common.mk

‎src/backend/statistics/meson.build

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,4 +5,6 @@ backend_sources += files(
55
'extended_stats.c',
66
'mcv.c',
77
'mvdistinct.c',
8+
'relation_stats.c',
9+
'stat_utils.c'
810
)
Lines changed: 210 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,210 @@
1+
/*-------------------------------------------------------------------------
2+
* relation_stats.c
3+
*
4+
* PostgreSQL relation statistics manipulation
5+
*
6+
* Code supporting the direct import of relation statistics, similar to
7+
* what is done by the ANALYZE command.
8+
*
9+
* Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
10+
* Portions Copyright (c) 1994, Regents of the University of California
11+
*
12+
* IDENTIFICATION
13+
* src/backend/statistics/relation_stats.c
14+
*
15+
*-------------------------------------------------------------------------
16+
*/
17+
18+
#include"postgres.h"
19+
20+
#include"access/heapam.h"
21+
#include"catalog/indexing.h"
22+
#include"statistics/stat_utils.h"
23+
#include"utils/fmgrprotos.h"
24+
#include"utils/syscache.h"
25+
26+
#defineDEFAULT_RELPAGES Int32GetDatum(0)
27+
#defineDEFAULT_RELTUPLES Float4GetDatum(-1.0)
28+
#defineDEFAULT_RELALLVISIBLE Int32GetDatum(0)
29+
30+
/*
31+
* Positional argument numbers, names, and types for
32+
* relation_statistics_update().
33+
*/
34+
35+
enumrelation_stats_argnum
36+
{
37+
RELATION_ARG=0,
38+
RELPAGES_ARG,
39+
RELTUPLES_ARG,
40+
RELALLVISIBLE_ARG,
41+
NUM_RELATION_STATS_ARGS
42+
};
43+
44+
staticstructStatsArgInforelarginfo[]=
45+
{
46+
[RELATION_ARG]= {"relation",REGCLASSOID},
47+
[RELPAGES_ARG]= {"relpages",INT4OID},
48+
[RELTUPLES_ARG]= {"reltuples",FLOAT4OID},
49+
[RELALLVISIBLE_ARG]= {"relallvisible",INT4OID},
50+
[NUM_RELATION_STATS_ARGS]= {0}
51+
};
52+
53+
staticboolrelation_statistics_update(FunctionCallInfofcinfo,intelevel);
54+
55+
/*
56+
* Internal function for modifying statistics for a relation.
57+
*/
58+
staticbool
59+
relation_statistics_update(FunctionCallInfofcinfo,intelevel)
60+
{
61+
Oidreloid;
62+
Relationcrel;
63+
HeapTuplectup;
64+
Form_pg_classpgcform;
65+
intreplaces[3]= {0};
66+
Datumvalues[3]= {0};
67+
boolnulls[3]= {0};
68+
intncols=0;
69+
TupleDesctupdesc;
70+
HeapTuplenewtup;
71+
72+
73+
stats_check_required_arg(fcinfo,relarginfo,RELATION_ARG);
74+
reloid=PG_GETARG_OID(RELATION_ARG);
75+
76+
stats_lock_check_privileges(reloid);
77+
78+
/*
79+
* Take RowExclusiveLock on pg_class, consistent with
80+
* vac_update_relstats().
81+
*/
82+
crel=table_open(RelationRelationId,RowExclusiveLock);
83+
84+
tupdesc=RelationGetDescr(crel);
85+
ctup=SearchSysCacheCopy1(RELOID,ObjectIdGetDatum(reloid));
86+
if (!HeapTupleIsValid(ctup))
87+
{
88+
ereport(elevel,
89+
(errcode(ERRCODE_OBJECT_IN_USE),
90+
errmsg("pg_class entry for relid %u not found",reloid)));
91+
table_close(crel,RowExclusiveLock);
92+
return false;
93+
}
94+
95+
pgcform= (Form_pg_class)GETSTRUCT(ctup);
96+
97+
/* relpages */
98+
if (!PG_ARGISNULL(RELPAGES_ARG))
99+
{
100+
int32relpages=PG_GETARG_INT32(RELPAGES_ARG);
101+
102+
if (relpages<-1)
103+
{
104+
ereport(elevel,
105+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
106+
errmsg("relpages cannot be < -1")));
107+
table_close(crel,RowExclusiveLock);
108+
return false;
109+
}
110+
111+
if (relpages!=pgcform->relpages)
112+
{
113+
replaces[ncols]=Anum_pg_class_relpages;
114+
values[ncols]=Int32GetDatum(relpages);
115+
ncols++;
116+
}
117+
}
118+
119+
if (!PG_ARGISNULL(RELTUPLES_ARG))
120+
{
121+
floatreltuples=PG_GETARG_FLOAT4(RELTUPLES_ARG);
122+
123+
if (reltuples<-1.0)
124+
{
125+
ereport(elevel,
126+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
127+
errmsg("reltuples cannot be < -1.0")));
128+
table_close(crel,RowExclusiveLock);
129+
return false;
130+
}
131+
132+
if (reltuples!=pgcform->reltuples)
133+
{
134+
replaces[ncols]=Anum_pg_class_reltuples;
135+
values[ncols]=Float4GetDatum(reltuples);
136+
ncols++;
137+
}
138+
}
139+
140+
if (!PG_ARGISNULL(RELALLVISIBLE_ARG))
141+
{
142+
int32relallvisible=PG_GETARG_INT32(RELALLVISIBLE_ARG);
143+
144+
if (relallvisible<0)
145+
{
146+
ereport(elevel,
147+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
148+
errmsg("relallvisible cannot be < 0")));
149+
table_close(crel,RowExclusiveLock);
150+
return false;
151+
}
152+
153+
if (relallvisible!=pgcform->relallvisible)
154+
{
155+
replaces[ncols]=Anum_pg_class_relallvisible;
156+
values[ncols]=Int32GetDatum(relallvisible);
157+
ncols++;
158+
}
159+
}
160+
161+
/* only update pg_class if there is a meaningful change */
162+
if (ncols==0)
163+
{
164+
table_close(crel,RowExclusiveLock);
165+
return false;
166+
}
167+
168+
newtup=heap_modify_tuple_by_cols(ctup,tupdesc,ncols,replaces,values,
169+
nulls);
170+
171+
CatalogTupleUpdate(crel,&newtup->t_self,newtup);
172+
heap_freetuple(newtup);
173+
174+
/* release the lock, consistent with vac_update_relstats() */
175+
table_close(crel,RowExclusiveLock);
176+
177+
return true;
178+
}
179+
180+
/*
181+
* Set statistics for a given pg_class entry.
182+
*/
183+
Datum
184+
pg_set_relation_stats(PG_FUNCTION_ARGS)
185+
{
186+
PG_RETURN_BOOL(relation_statistics_update(fcinfo,ERROR));
187+
}
188+
189+
/*
190+
* Clear statistics for a given pg_class entry; that is, set back to initial
191+
* stats for a newly-created table.
192+
*/
193+
Datum
194+
pg_clear_relation_stats(PG_FUNCTION_ARGS)
195+
{
196+
LOCAL_FCINFO(newfcinfo,4);
197+
198+
InitFunctionCallInfoData(*newfcinfo,NULL,4,InvalidOid,NULL,NULL);
199+
200+
newfcinfo->args[0].value=PG_GETARG_OID(0);
201+
newfcinfo->args[0].isnull=PG_ARGISNULL(0);
202+
newfcinfo->args[1].value=DEFAULT_RELPAGES;
203+
newfcinfo->args[1].isnull= false;
204+
newfcinfo->args[2].value=DEFAULT_RELTUPLES;
205+
newfcinfo->args[2].isnull= false;
206+
newfcinfo->args[3].value=DEFAULT_RELALLVISIBLE;
207+
newfcinfo->args[3].isnull= false;
208+
209+
PG_RETURN_BOOL(relation_statistics_update(newfcinfo,ERROR));
210+
}

0 commit comments

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