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

Commita97e0c3

Browse files
committed
Add pg_file_settings view and function
The function and view added here provide a way to look at all settingsin postgresql.conf, any #include'd files, and postgresql.auto.conf(which is what backs the ALTER SYSTEM command).The information returned includes the configuration file name, linenumber in that file, sequence number indicating when the parameter isloaded (useful to see if it is later masked by another definition of thesame parameter), parameter name, and what it is set to at that point.This information is updated on reload of the server.This is unfiltered, privileged, information and therefore access isrestricted to superusers through the GRANT system.Author: Sawada Masahiko, various improvements by me.Reviewers: David Steele
1 parentbab64ef commita97e0c3

File tree

7 files changed

+263
-0
lines changed

7 files changed

+263
-0
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7559,6 +7559,11 @@
75597559
<entry>parameter settings</entry>
75607560
</row>
75617561

7562+
<row>
7563+
<entry><link linkend="view-pg-file-settings"><structname>pg_file_settings</structname></link></entry>
7564+
<entry>file location of parameter settings</entry>
7565+
</row>
7566+
75627567
<row>
75637568
<entry><link linkend="view-pg-shadow"><structname>pg_shadow</structname></link></entry>
75647569
<entry>database users</entry>
@@ -9173,6 +9178,79 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
91739178

91749179
</sect1>
91759180

9181+
<sect1 id="view-pg-file-settings">
9182+
<title><structname>pg_file_settings</structname></title>
9183+
9184+
<indexterm zone="view-pg-file-settings">
9185+
<primary>pg_file_settings</primary>
9186+
</indexterm>
9187+
9188+
<para>
9189+
The view <structname>pg_file_settings</structname> provides the file
9190+
name, line number and value of all parameters which are set through
9191+
configuration files.
9192+
In contrast to <structname>pg_settings</structname>, a row is provided for
9193+
each occurrence of the parameter across all configuration files. This is helpful
9194+
for discovering why one value may have been used in preference to another
9195+
when the parameters were loaded.
9196+
</para>
9197+
9198+
<table>
9199+
<title><structname>pg_file_settings</> Columns</title>
9200+
9201+
<tgroup cols="3">
9202+
<thead>
9203+
<row>
9204+
<entry>Name</entry>
9205+
<entry>Type</entry>
9206+
<entry>Description</entry>
9207+
</row>
9208+
</thead>
9209+
<tbody>
9210+
<row>
9211+
<entry><structfield>sourcefile</structfield></entry>
9212+
<entry><structfield>text</structfield></entry>
9213+
<entry>Path to and name of the configration file</entry>
9214+
</row>
9215+
<row>
9216+
<entry><structfield>sourceline</structfield></entry>
9217+
<entry><structfield>integer</structfield></entry>
9218+
<entry>
9219+
Line number within the configuration file where the value was set
9220+
</entry>
9221+
</row>
9222+
<row>
9223+
<entry><structfield>seqno</structfield></entry>
9224+
<entry><structfield>integer</structfield></entry>
9225+
<entry>Order in which the setting was loaded</entry>
9226+
</row>
9227+
<row>
9228+
<entry><structfield>name</structfield></entry>
9229+
<entry><structfield>text</structfield></entry>
9230+
<entry>Run-time configuration parameter name</entry>
9231+
</row>
9232+
<row>
9233+
<entry><structfield>setting</structfield></entry>
9234+
<entry><structfield>text</structfield></entry>
9235+
<entry>value of the parameter</entry>
9236+
</row>
9237+
</tbody>
9238+
</tgroup>
9239+
</table>
9240+
9241+
<para>
9242+
See <xref linkend="config-setting"> for more information about the various
9243+
ways to change these parameters.
9244+
</para>
9245+
9246+
<para>
9247+
The <structname>pg_file_settings</structname> view cannot be modified
9248+
directly as it represents information, as read in at server start or
9249+
reload time, about all parameter settings across all configuration files.
9250+
</para>
9251+
9252+
</sect1>
9253+
91769254
<sect1 id="view-pg-shadow">
91779255
<title><structname>pg_shadow</structname></title>
91789256

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -411,6 +411,12 @@ CREATE RULE pg_settings_n AS
411411

412412
GRANTSELECT,UPDATEON pg_settings TO PUBLIC;
413413

414+
CREATEVIEWpg_file_settingsAS
415+
SELECT*FROM pg_show_all_file_settings()AS A;
416+
417+
REVOKE ALLon pg_file_settingsFROM PUBLIC;
418+
REVOKE EXECUTEON FUNCTION pg_show_all_file_settings()FROM PUBLIC;
419+
414420
CREATEVIEWpg_timezone_abbrevsAS
415421
SELECT*FROM pg_timezone_abbrevs();
416422

‎src/backend/utils/misc/guc-file.l

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -120,6 +120,7 @@ ProcessConfigFile(GucContext context)
120120
*head,
121121
*tail;
122122
inti;
123+
intfile_variables_count = 0;
123124
124125
/*
125126
* Config files are processed on startup (by the postmaster only)
@@ -255,6 +256,7 @@ ProcessConfigFile(GucContext context)
255256
error =true;
256257
ConfFileWithError = item->filename;
257258
}
259+
file_variables_count++;
258260
}
259261

260262
/*
@@ -341,6 +343,54 @@ ProcessConfigFile(GucContext context)
341343
PGC_BACKEND, PGC_S_DYNAMIC_DEFAULT);
342344
}
343345

346+
/*
347+
* Check if we have allocated the array yet.
348+
*
349+
* If not, allocate it based on the number of file variables we have seen.
350+
*/
351+
if (!guc_file_variables)
352+
{
353+
/* For the first call */
354+
num_guc_file_variables = file_variables_count;
355+
guc_file_variables = (ConfigFileVariable *)guc_malloc(FATAL,
356+
num_guc_file_variables *sizeof(structConfigFileVariable));
357+
}
358+
else
359+
{
360+
int i;
361+
362+
/* Free all of the previously allocated entries */
363+
for (i =0; i < num_guc_file_variables; i++)
364+
{
365+
free(guc_file_variables[i].name);
366+
free(guc_file_variables[i].value);
367+
free(guc_file_variables[i].filename);
368+
}
369+
370+
/* Update the global count and realloc based on the new size */
371+
num_guc_file_variables = file_variables_count;
372+
guc_file_variables = (ConfigFileVariable *)guc_realloc(FATAL,
373+
guc_file_variables,
374+
num_guc_file_variables *sizeof(structConfigFileVariable));
375+
}
376+
377+
/*
378+
* Copy the settings which came from the files read into the
379+
* guc_file_variables array which backs the pg_show_file_settings()
380+
* function.
381+
*/
382+
for (item = head, i =0; item && i < num_guc_file_variables;
383+
item = item->next, i++)
384+
{
385+
guc_file_variables[i].name =guc_strdup(FATAL, item->name);
386+
guc_file_variables[i].value =guc_strdup(FATAL, item->value);
387+
guc_file_variables[i].filename =guc_strdup(FATAL, item->filename);
388+
guc_file_variables[i].sourceline = item->sourceline;
389+
}
390+
391+
/* We had better have made it through the loop above to a clean ending. */
392+
Assert(!item && i == num_guc_file_variables);
393+
344394
/*
345395
* Now apply the values from the config file.
346396
*/

‎src/backend/utils/misc/guc.c

Lines changed: 120 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3678,6 +3678,22 @@ static struct config_generic **guc_variables;
36783678
/* Current number of variables contained in the vector */
36793679
staticintnum_guc_variables;
36803680

3681+
/*
3682+
* Lookup of variables for pg_file_settings view.
3683+
* guc_file_variables is an array of length num_guc_file_variables.
3684+
*/
3685+
typedefstructConfigFileVariable
3686+
{
3687+
char*name;
3688+
char*value;
3689+
char*filename;
3690+
intsourceline;
3691+
}ConfigFileVariable;
3692+
staticstructConfigFileVariable*guc_file_variables;
3693+
3694+
/* Number of file variables */
3695+
staticintnum_guc_file_variables;
3696+
36813697
/* Vector capacity */
36823698
staticintsize_guc_variables;
36833699

@@ -8148,6 +8164,110 @@ show_all_settings(PG_FUNCTION_ARGS)
81488164
}
81498165
}
81508166

8167+
/*
8168+
* show_all_file_settings
8169+
*
8170+
* returns a table of all parameter settings in all configuration files
8171+
* which includes the config file path/name, filename, a sequence number
8172+
* indicating when we loaded it, the parameter name, and the value it is
8173+
* set to.
8174+
*
8175+
* Note: no filtering is done here, instead we depend on the GRANT system
8176+
* to prevent unprivileged users from accessing this function or the view
8177+
* built on top of it.
8178+
*/
8179+
Datum
8180+
show_all_file_settings(PG_FUNCTION_ARGS)
8181+
{
8182+
#defineNUM_PG_FILE_SETTINGS_ATTS 5
8183+
FuncCallContext*funcctx;
8184+
TupleDesctupdesc;
8185+
intcall_cntr;
8186+
intmax_calls;
8187+
AttInMetadata*attinmeta;
8188+
MemoryContextoldcontext;
8189+
8190+
if (SRF_IS_FIRSTCALL())
8191+
{
8192+
funcctx=SRF_FIRSTCALL_INIT();
8193+
8194+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
8195+
8196+
/*
8197+
* need a tuple descriptor representing NUM_PG_SETTINGS_ATTS columns
8198+
* of the appropriate types
8199+
*/
8200+
8201+
tupdesc=CreateTemplateTupleDesc(NUM_PG_FILE_SETTINGS_ATTS, false);
8202+
TupleDescInitEntry(tupdesc, (AttrNumber)1,"sourcefile",
8203+
TEXTOID,-1,0);
8204+
TupleDescInitEntry(tupdesc, (AttrNumber)2,"sourceline",
8205+
INT4OID,-1,0);
8206+
TupleDescInitEntry(tupdesc, (AttrNumber)3,"seqno",
8207+
INT4OID,-1,0);
8208+
TupleDescInitEntry(tupdesc, (AttrNumber)4,"name",
8209+
TEXTOID,-1,0);
8210+
TupleDescInitEntry(tupdesc, (AttrNumber)5,"setting",
8211+
TEXTOID,-1,0);
8212+
8213+
attinmeta=TupleDescGetAttInMetadata(tupdesc);
8214+
funcctx->attinmeta=attinmeta;
8215+
funcctx->max_calls=num_guc_file_variables;
8216+
MemoryContextSwitchTo(oldcontext);
8217+
}
8218+
8219+
funcctx=SRF_PERCALL_SETUP();
8220+
8221+
call_cntr=funcctx->call_cntr;
8222+
max_calls=funcctx->max_calls;
8223+
attinmeta=funcctx->attinmeta;
8224+
8225+
if (call_cntr<max_calls)
8226+
{
8227+
char*values[NUM_PG_FILE_SETTINGS_ATTS];
8228+
HeapTupletuple;
8229+
Datumresult;
8230+
ConfigFileVariableconf;
8231+
charbuffer[12];/* must be at least 12, per pg_ltoa */
8232+
8233+
/* Check to avoid going past end of array */
8234+
if (call_cntr>num_guc_file_variables)
8235+
SRF_RETURN_DONE(funcctx);
8236+
8237+
conf=guc_file_variables[call_cntr];
8238+
8239+
/* sourcefile */
8240+
values[0]=conf.filename;
8241+
8242+
/* sourceline */
8243+
pg_ltoa(conf.sourceline,buffer);
8244+
values[1]=pstrdup(buffer);
8245+
8246+
/* seqno */
8247+
pg_ltoa(call_cntr+1,buffer);
8248+
values[2]=pstrdup(buffer);
8249+
8250+
/* name */
8251+
values[3]=conf.name;
8252+
8253+
/* setting */
8254+
values[4]=conf.value;
8255+
8256+
/* build a tuple */
8257+
tuple=BuildTupleFromCStrings(attinmeta,values);
8258+
8259+
/* make the tuple into a datum */
8260+
result=HeapTupleGetDatum(tuple);
8261+
8262+
SRF_RETURN_NEXT(funcctx,result);
8263+
}
8264+
else
8265+
{
8266+
SRF_RETURN_DONE(funcctx);
8267+
}
8268+
8269+
}
8270+
81518271
staticchar*
81528272
_ShowOption(structconfig_generic*record,booluse_units)
81538273
{

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3060,6 +3060,8 @@ DATA(insert OID = 2078 ( set_configPGNSP PGUID 12 1 0 0 0 f f f f f f v 3 0 2
30603060
DESCR("SET X as a function");
30613061
DATA(insert OID = 2084 ( pg_show_all_settingsPGNSP PGUID 12 1 1000 0 0 f f f f t t s 0 0 2249 "" "{25,25,25,25,25,25,25,25,25,25,25,1009,25,25,25,23}" "{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{name,setting,unit,category,short_desc,extra_desc,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline}" _null_ _null_ show_all_settings _null_ _null_ _null_ ));
30623062
DESCR("SHOW ALL as a function");
3063+
DATA(insert OID = 3329 ( pg_show_all_file_settingsPGNSP PGUID 12 1 1000 0 0 f f f f t t s 0 0 2249 "" "{25,23,23,25,25}" "{o,o,o,o,o}" "{sourcefile,sourceline,seqno,name,setting}" _null_ _null_ show_all_file_settings _null_ _null_ _null_ ));
3064+
DESCR("show config file settings");
30633065
DATA(insert OID = 1371 ( pg_lock_status PGNSP PGUID 12 1 1000 0 0 f f f f t t v 0 0 2249 "" "{25,26,26,23,21,25,28,26,26,21,25,23,25,16,16}" "{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid,virtualtransaction,pid,mode,granted,fastpath}" _null_ _null_ pg_lock_status _null_ _null_ _null_ ));
30643066
DESCR("view system lock information");
30653067
DATA(insert OID = 1065 ( pg_prepared_xact PGNSP PGUID 12 1 1000 0 0 f f f f t t v 0 0 2249 "" "{28,25,1184,26,26}" "{o,o,o,o,o}" "{transaction,gid,prepared,ownerid,dbid}" _null_ _null_ pg_prepared_xact _null_ _null_ _null_ ));

‎src/include/utils/builtins.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1100,6 +1100,7 @@ extern Datum quote_nullable(PG_FUNCTION_ARGS);
11001100
externDatumshow_config_by_name(PG_FUNCTION_ARGS);
11011101
externDatumset_config_by_name(PG_FUNCTION_ARGS);
11021102
externDatumshow_all_settings(PG_FUNCTION_ARGS);
1103+
externDatumshow_all_file_settings(PG_FUNCTION_ARGS);
11031104

11041105
/* lockfuncs.c */
11051106
externDatumpg_lock_status(PG_FUNCTION_ARGS);

‎src/test/regress/expected/rules.out

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1312,6 +1312,12 @@ pg_cursors| SELECT c.name,
13121312
c.is_scrollable,
13131313
c.creation_time
13141314
FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
1315+
pg_file_settings| SELECT a.sourcefile,
1316+
a.sourceline,
1317+
a.seqno,
1318+
a.name,
1319+
a.setting
1320+
FROM pg_show_all_file_settings() a(sourcefile, sourceline, seqno, name, setting);
13151321
pg_group| SELECT pg_authid.rolname AS groname,
13161322
pg_authid.oid AS grosysid,
13171323
ARRAY( SELECT pg_auth_members.member

0 commit comments

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