Обсуждение: Re: [HACKERS] allowed user/db variables
(moved to PATCHES) Tom Lane wrote: > I agree with this plan also. I'm not sure if the RH guys had intended > to get around to this or not --- it's not on their shortlist of stuff > they need for their tools. > > The proposed patch from RH includes addition of descriptions to the > variables' table entries in guc.c. It might make sense to include these > as a column in pg_settings as well; but if we do then changing the view > would have to wait till that patch is submitted and accepted. (I was > offline yesterday but it doesn't look like anything's been done; I will > remind 'em that feature freeze is hard upon us.) Here is a patch to expand pg_settings. I included more than discussed because it was easy and I thought it might be useful. Let me know if you want some of them removed. Passes all regression tests. I'll send in a documentation patch once I'm sure what new columns we are actually going to add (i.e. this patch, descriptions, or some combination thereof). If there are no objections, please apply. Joe p.s. here is some sample (expanded) output: regression=# select * from pg_settings; -[ RECORD 1 ]----+-------------------------------------- name | add_missing_from setting | on context | user vartype | bool reset_source | default session_source | default tentative_source | default source | default boot_val | reset_val | on session_val | on tentative_val | off min_val | max_val | [ ... ] -[ RECORD 14 ]---+-------------------------------------- name | DateStyle setting | ISO with US (NonEuropean) conventions context | user vartype | string reset_source | default session_source | default tentative_source | default source | default boot_val | ISO, US reset_val | ISO, US session_val | ISO, US tentative_val | min_val | max_val | -[ RECORD 15 ]---+-------------------------------------- name | db_user_namespace setting | off context | sighup vartype | bool reset_source | default session_source | default tentative_source | default source | default boot_val | reset_val | off session_val | off tentative_val | off min_val | max_val | [ ... ] -[ RECORD 26 ]---+-------------------------------------- name | effective_cache_size setting | 1000 context | user vartype | real reset_source | default session_source | default tentative_source | default source | default boot_val | reset_val | 1000 session_val | 1000 tentative_val | 0 min_val | 0 max_val | 1.79769e+308 [ ... ] -[ RECORD 55 ]---+-------------------------------------- name | log_duration setting | off context | super-user vartype | bool reset_source | default session_source | default tentative_source | default source | default boot_val | reset_val | off session_val | off tentative_val | off min_val | max_val | [ ... ] -[ RECORD 83 ]---+-------------------------------------- name | shared_buffers setting | 64 context | postmaster vartype | integer reset_source | default session_source | default tentative_source | default source | default boot_val | reset_val | 64 session_val | 64 tentative_val | 0 min_val | 16 max_val | 2147483647 Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v retrieving revision 1.131 diff -c -r1.131 guc.c *** src/backend/utils/misc/guc.c 11 Jun 2003 22:13:22 -0000 1.131 --- src/backend/utils/misc/guc.c 25 Jun 2003 23:25:31 -0000 *************** *** 159,168 **** */ enum config_type { ! PGC_BOOL, ! PGC_INT, ! PGC_REAL, ! PGC_STRING }; /* Generic fields applicable to all types of variables */ --- 159,208 ---- */ enum config_type { ! PGC_BOOL = 0, ! PGC_INT = 1, ! PGC_REAL = 2, ! PGC_STRING = 3 ! }; ! ! /* ! * Used for pg_settings. Keep in sync with config_type enum above ! */ ! static char *config_type_name[] = ! { ! "bool", ! "integer", ! "real", ! "string" ! }; ! ! /* ! * Used for pg_settings. Keep in sync with GucContext enum in guc.h ! */ ! static char *GucContextName[] = ! { ! "internal", ! "postmaster", ! "sighup", ! "backend", ! "super-user", ! "user" ! }; ! ! /* ! * Used for pg_settings. Keep in sync with GucSource enum in guc.h ! */ ! static char *GucSourceName[] = ! { ! "default", ! "environment variable", ! "configuration file", ! "command line", ! "database", ! "user", ! "client", ! "override", ! "session" }; /* Generic fields applicable to all types of variables */ *************** *** 2617,2639 **** * Return GUC variable value by variable number; optionally return canonical * form of name. Return value is palloc'd. */ ! char * ! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow) { ! struct config_generic *conf; /* check requested variable number valid */ Assert((varnum >= 0) && (varnum < num_guc_variables)); conf = guc_variables[varnum]; - if (varname) - *varname = conf->name; - if (noshow) *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; ! return _ShowOption(conf); } /* --- 2657,2826 ---- * Return GUC variable value by variable number; optionally return canonical * form of name. Return value is palloc'd. */ ! void ! GetConfigOptionByNum(int varnum, const char **values, bool *noshow) { ! char buffer[256]; ! struct config_generic *conf; /* check requested variable number valid */ Assert((varnum >= 0) && (varnum < num_guc_variables)); conf = guc_variables[varnum]; if (noshow) *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; ! /* first get the generic attributes */ ! ! /* name */ ! values[0] = conf->name; ! ! /* setting : use _ShowOption in order to avoid duplicating the logic */ ! values[1] = _ShowOption(conf); ! ! /* context */ ! values[2] = GucContextName[conf->context]; ! ! /* vartype */ ! values[3] = config_type_name[conf->vartype]; ! ! /* reset_source */ ! values[4] = GucSourceName[conf->reset_source]; ! ! /* session_source */ ! values[5] = GucSourceName[conf->session_source]; ! ! /* tentative_source */ ! values[6] = GucSourceName[conf->tentative_source]; ! ! /* source */ ! values[7] = GucSourceName[conf->source]; ! ! /* now get the type specifc attributes */ ! switch (conf->vartype) ! { ! case PGC_BOOL: ! { ! struct config_bool *lconf = (struct config_bool *) conf; ! ! /* boot_val */ ! values[8] = NULL; ! ! /* reset_val */ ! values[9] = lconf->reset_val ? "on" : "off"; ! ! /* session_val */ ! values[10] = lconf->session_val ? "on" : "off"; ! ! /* tentative_val */ ! values[11] = lconf->tentative_val ? "on" : "off"; ! ! /* min_val */ ! values[12] = NULL; ! ! /* max_val */ ! values[13] = NULL; ! } ! break; ! ! case PGC_INT: ! { ! struct config_int *lconf = (struct config_int *) conf; ! ! /* boot_val */ ! values[8] = NULL; ! ! /* reset_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->reset_val); ! values[9] = pstrdup(buffer); ! ! /* session_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->session_val); ! values[10] = pstrdup(buffer); ! ! /* tentative_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->tentative_val); ! values[11] = pstrdup(buffer); ! ! /* min_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->min); ! values[12] = pstrdup(buffer); ! ! /* max_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->max); ! values[13] = pstrdup(buffer); ! } ! break; ! ! case PGC_REAL: ! { ! struct config_real *lconf = (struct config_real *) conf; ! ! /* boot_val */ ! values[8] = NULL; ! ! /* reset_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->reset_val); ! values[9] = pstrdup(buffer); ! ! /* session_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->session_val); ! values[10] = pstrdup(buffer); ! ! /* tentative_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->tentative_val); ! values[11] = pstrdup(buffer); ! ! /* min_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->min); ! values[12] = pstrdup(buffer); ! ! /* max_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->max); ! values[13] = pstrdup(buffer); ! } ! break; ! ! case PGC_STRING: ! { ! struct config_string *lconf = (struct config_string *) conf; ! ! /* boot_val */ ! values[8] = lconf->boot_val; ! ! /* reset_val */ ! values[9] = lconf->reset_val; ! ! /* session_val */ ! values[10] = lconf->session_val; ! ! /* tentative_val */ ! values[11] = lconf->tentative_val; ! ! /* min_val */ ! values[12] = NULL; ! ! /* max_val */ ! values[13] = NULL; ! } ! break; ! ! default: ! { ! /* ! * should never get here, but in case we do, set 'em ! * all to NULL ! */ ! values[8] = NULL; ! values[9] = NULL; ! values[10] = NULL; ! values[11] = NULL; ! values[12] = NULL; ! values[13] = NULL; ! } ! break; ! } } /* *************** *** 2673,2678 **** --- 2860,2867 ---- * show_all_settings - equiv to SHOW ALL command but implemented as * a Table Function. */ + #define NUM_PG_SETTINGS_ATTS 14 + Datum show_all_settings(PG_FUNCTION_ARGS) { *************** *** 2696,2707 **** */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! /* need a tuple descriptor representing two TEXT columns */ ! tupdesc = CreateTemplateTupleDesc(2, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0, false); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", TEXTOID, -1, 0, false); /* allocate a slot for a tuple with this tupdesc */ slot = TupleDescGetSlot(tupdesc); --- 2885,2923 ---- */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! /* ! * need a tuple descriptor representing NUM_PG_SETTINGS_ATTS columns ! * of the appropriate types ! */ ! tupdesc = CreateTemplateTupleDesc(NUM_PG_SETTINGS_ATTS, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0, false); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "context", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "vartype", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "reset_source", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "session_source", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "tentative_source", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 8, "source", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 9, "boot_val", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 10, "reset_val", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 11, "session_val", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 12, "tentative_val", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 13, "min_val", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 14, "max_val", + TEXTOID, -1, 0, false); /* allocate a slot for a tuple with this tupdesc */ slot = TupleDescGetSlot(tupdesc); *************** *** 2732,2740 **** if (call_cntr < max_calls) /* do when there is more left to send */ { ! char *values[2]; ! char *varname; ! char *varval; bool noshow; HeapTuple tuple; Datum result; --- 2948,2954 ---- if (call_cntr < max_calls) /* do when there is more left to send */ { ! char *values[NUM_PG_SETTINGS_ATTS]; bool noshow; HeapTuple tuple; Datum result; *************** *** 2744,2758 **** */ do { ! varval = GetConfigOptionByNum(call_cntr, ! (const char **) &varname, ! &noshow); if (noshow) { - /* varval is a palloc'd copy, so free it */ - if (varval != NULL) - pfree(varval); - /* bump the counter and get the next config setting */ call_cntr = ++funcctx->call_cntr; --- 2958,2966 ---- */ do { ! GetConfigOptionByNum(call_cntr, (const char **) values, &noshow); if (noshow) { /* bump the counter and get the next config setting */ call_cntr = ++funcctx->call_cntr; *************** *** 2762,2784 **** } } while (noshow); - /* - * Prepare a values array for storage in our slot. This should be - * an array of C strings which will be processed later by the - * appropriate "in" functions. - */ - values[0] = varname; - values[1] = varval; - /* build a tuple */ tuple = BuildTupleFromCStrings(attinmeta, values); /* make the tuple into a datum */ result = TupleGetDatum(slot, tuple); - - /* Clean up */ - if (varval != NULL) - pfree(varval); SRF_RETURN_NEXT(funcctx, result); } --- 2970,2980 ---- Index: src/bin/initdb/initdb.sh =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v retrieving revision 1.192 diff -c -r1.192 initdb.sh *** src/bin/initdb/initdb.sh 2 Jun 2003 19:00:29 -0000 1.192 --- src/bin/initdb/initdb.sh 25 Jun 2003 23:34:45 -0000 *************** *** 971,977 **** CREATE VIEW pg_settings AS \ SELECT * \ ! FROM pg_show_all_settings() AS A(name text, setting text); CREATE RULE pg_settings_u AS \ ON UPDATE TO pg_settings \ --- 971,981 ---- CREATE VIEW pg_settings AS \ SELECT * \ ! FROM pg_show_all_settings() AS A \ ! (name text, setting text, context text, vartype text, reset_source text, \ ! session_source text, tentative_source text, source text, boot_val text, \ ! reset_val text, session_val text, tentative_val text, min_val text, \ ! max_val text); CREATE RULE pg_settings_u AS \ ON UPDATE TO pg_settings \ Index: src/include/utils/guc.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc.h,v retrieving revision 1.32 diff -c -r1.32 guc.h *** src/include/utils/guc.h 11 Jun 2003 18:01:14 -0000 1.32 --- src/include/utils/guc.h 25 Jun 2003 21:03:26 -0000 *************** *** 49,63 **** * we don't yet know if the user is a superuser. * * USERSET options can be set by anyone any time. */ typedef enum { ! PGC_INTERNAL, ! PGC_POSTMASTER, ! PGC_SIGHUP, ! PGC_BACKEND, ! PGC_SUSET, ! PGC_USERSET } GucContext; /* --- 49,65 ---- * we don't yet know if the user is a superuser. * * USERSET options can be set by anyone any time. + * + * Keep in sync with GucContextName in guc.c */ typedef enum { ! PGC_INTERNAL = 0, ! PGC_POSTMASTER = 1, ! PGC_SIGHUP = 2, ! PGC_BACKEND = 3, ! PGC_SUSET = 4, ! PGC_USERSET = 5 } GucContext; /* *************** *** 69,74 **** --- 71,78 ---- * Sources <= PGC_S_OVERRIDE will set the default used by RESET, as well * as the current value. Note that source == PGC_S_OVERRIDE should be * used when setting a PGC_INTERNAL option. + * + * Keep in sync with GucSourceName in guc.c */ typedef enum { *************** *** 83,89 **** PGC_S_SESSION = 8 /* SET command */ } GucSource; - /* GUC vars that are actually declared in guc.c, rather than elsewhere */ extern bool log_statement; extern bool log_duration; --- 87,92 ---- *************** *** 123,129 **** extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); extern void ShowAllGUCConfig(DestReceiver *dest); extern char *GetConfigOptionByName(const char *name, const char **varname); ! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow); extern int GetNumConfigOptions(void); extern void SetPGVariable(const char *name, List *args, bool is_local); --- 126,132 ---- extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); extern void ShowAllGUCConfig(DestReceiver *dest); extern char *GetConfigOptionByName(const char *name, const char **varname); ! extern void GetConfigOptionByNum(int varnum, const char **values, bool *noshow); extern int GetNumConfigOptions(void); extern void SetPGVariable(const char *name, List *args, bool is_local); Index: src/test/regress/expected/rangefuncs.out =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v retrieving revision 1.7 diff -c -r1.7 rangefuncs.out *** src/test/regress/expected/rangefuncs.out 13 Feb 2003 20:45:22 -0000 1.7 --- src/test/regress/expected/rangefuncs.out 25 Jun 2003 23:32:50 -0000 *************** *** 1,4 **** ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; name | setting ------------------+--------- enable_hashagg | on --- 1,4 ---- ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; name | setting ------------------+--------- enable_hashagg | on Index: src/test/regress/expected/rules.out =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v retrieving revision 1.75 diff -c -r1.75 rules.out *** src/test/regress/expected/rules.out 26 May 2003 00:11:28 -0000 1.75 --- src/test/regress/expected/rules.out 25 Jun 2003 23:38:24 -0000 *************** *** 1273,1279 **** pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); ! pg_settings | SELECT a.name, a.setting FROM pg_show_all_settings() a(name text, setting text); pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; --- 1273,1279 ---- pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); ! pg_settings | SELECT a.name, a.setting, a.context, a.vartype, a.reset_source, a.session_source, a.tentative_source,a.source, a.boot_val, a.reset_val, a.session_val, a.tentative_val, a.min_val, a.max_val FROM pg_show_all_settings()a(name text, setting text, context text, vartype text, reset_source text, session_source text, tentative_sourcetext, source text, boot_val text, reset_val text, session_val text, tentative_val text, min_val text, max_valtext); pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; Index: src/test/regress/sql/rangefuncs.sql =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/rangefuncs.sql,v retrieving revision 1.4 diff -c -r1.4 rangefuncs.sql *** src/test/regress/sql/rangefuncs.sql 30 Aug 2002 19:56:49 -0000 1.4 --- src/test/regress/sql/rangefuncs.sql 25 Jun 2003 23:32:34 -0000 *************** *** 1,4 **** ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11); --- 1,4 ---- ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11);
Joe Conway <mail@joeconway.com> writes: > Here is a patch to expand pg_settings. I included more than discussed > because it was easy and I thought it might be useful. Let me know if you > want some of them removed. Much of what you've included is part of the internal implementation of GUC, and I think it's unwise to expose it; any future changes in GUC might break the view (or more accurately break apps that are expecting the view to look a particular way). I agree with adding context, vartype, min_val, and max_val. Not sure about boot_val or reset_val. The RH guys do want to expose boot_val in their tool, since it's concerned with helping people set up postgresql.conf, but is it really useful for clients to see it? reset_val might be okay to expose ... not sure if we'd ever want to remove that concept from the implementation. > name | DateStyle > setting | ISO with US (NonEuropean) conventions This reminds me, someone (Barry?) was griping that SHOW DATESTYLE doesn't produce a value that SET DATESTYLE will take. Did we agree that it was OK to change the output to look like "ISO, US" etc? regards, tom lane
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: >>Here is a patch to expand pg_settings. I included more than discussed >>because it was easy and I thought it might be useful. Let me know if you >>want some of them removed. > > Much of what you've included is part of the internal implementation of > GUC, and I think it's unwise to expose it; any future changes in GUC > might break the view (or more accurately break apps that are expecting > the view to look a particular way). > > I agree with adding context, vartype, min_val, and max_val. Not sure > about boot_val or reset_val. The RH guys do want to expose boot_val > in their tool, since it's concerned with helping people set up > postgresql.conf, but is it really useful for clients to see it? > reset_val might be okay to expose ... not sure if we'd ever want to > remove that concept from the implementation. I thought you might say that. What about this list: name setting context vartype source min_val max_val ISTM that "source" is worth knowing. >>name | DateStyle >>setting | ISO with US (NonEuropean) conventions > > This reminds me, someone (Barry?) was griping that SHOW DATESTYLE > doesn't produce a value that SET DATESTYLE will take. Did we agree > that it was OK to change the output to look like "ISO, US" etc? I vaguely remember the thread: http://archives.postgresql.org/pgsql-hackers/2003-05/msg00190.php I don't see any followup (agreements or disagreements). Do you want me to change that while I'm at it? Joe
Joe Conway <mail@joeconway.com> writes: > ISTM that "source" is worth knowing. Hm, possibly. Any other opinions? >> This reminds me, someone (Barry?) was griping that SHOW DATESTYLE >> doesn't produce a value that SET DATESTYLE will take. Did we agree >> that it was OK to change the output to look like "ISO, US" etc? > I vaguely remember the thread: > http://archives.postgresql.org/pgsql-hackers/2003-05/msg00190.php > I don't see any followup (agreements or disagreements). Do you want me > to change that while I'm at it? No, I can handle it (and it should be a separate commit anyway). regards, tom lane
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: >>ISTM that "source" is worth knowing. > > Hm, possibly. Any other opinions? This version has the seven fields I proposed, including "source". Here's an example that shows why I think it's valuable: regression=# \x Expanded display is on. regression=# select * from pg_settings where name = 'enable_seqscan'; -[ RECORD 1 ]----------- name | enable_seqscan setting | on context | user vartype | bool source | default min_val | max_val | regression=# update pg_settings set setting = 'off' where name = 'enable_seqscan'; -[ RECORD 1 ]--- set_config | off regression=# select * from pg_settings where name = 'enable_seqscan'; -[ RECORD 1 ]----------- name | enable_seqscan setting | off context | user vartype | bool source | session min_val | max_val | regression=# alter user postgres set enable_seqscan to 'off'; ALTER USER (log out and then back in again) regression=# \x Expanded display is on. regression=# select * from pg_settings where name = 'enable_seqscan'; -[ RECORD 1 ]----------- name | enable_seqscan setting | off context | user vartype | bool source | user min_val | max_val | In the first case, enable_seqscan is set to its default value. After setting it to off, it is obvious that the value has been changed for the session only. In the third case, you can see that the value has been set specifically for the user. This version of the patch also includes documentation changes. Passes all regression tests. Please apply. Joe Index: doc/src/sgml/runtime.sgml =================================================================== RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.187 diff -c -r1.187 runtime.sgml *** doc/src/sgml/runtime.sgml 25 Jun 2003 01:13:24 -0000 1.187 --- doc/src/sgml/runtime.sgml 26 Jun 2003 05:01:56 -0000 *************** *** 571,584 **** <row> <entry><literal>name</literal></entry> <entry><type>text</type></entry> ! <entry>The name of the run-time configuration parameter</entry> </row> <row> <entry><literal>setting</literal></entry> <entry><type>text</type></entry> ! <entry>The current value of the run-time configuration parameter</entry> </row> </tbody> </tgroup> </table> --- 571,615 ---- <row> <entry><literal>name</literal></entry> <entry><type>text</type></entry> ! <entry>run-time configuration parameter name</entry> </row> <row> <entry><literal>setting</literal></entry> <entry><type>text</type></entry> ! <entry>current value of the parameter</entry> </row> + + <row> + <entry><literal>context</literal></entry> + <entry><type>text</type></entry> + <entry>context required to set the parameter's value</entry> + </row> + + <row> + <entry><literal>vartype</literal></entry> + <entry><type>text</type></entry> + <entry>parameter type</entry> + </row> + + <row> + <entry><literal>source</literal></entry> + <entry><type>text</type></entry> + <entry>source of the current parameter value</entry> + </row> + + <row> + <entry><literal>min_val</literal></entry> + <entry><type>text</type></entry> + <entry>minimum allowed value of the parameter</entry> + </row> + + <row> + <entry><literal>max_val</literal></entry> + <entry><type>text</type></entry> + <entry>maximum allowed value of the parameter</entry> + </row> + </tbody> </tgroup> </table> Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v retrieving revision 1.131 diff -c -r1.131 guc.c *** src/backend/utils/misc/guc.c 11 Jun 2003 22:13:22 -0000 1.131 --- src/backend/utils/misc/guc.c 26 Jun 2003 04:03:00 -0000 *************** *** 159,168 **** */ enum config_type { ! PGC_BOOL, ! PGC_INT, ! PGC_REAL, ! PGC_STRING }; /* Generic fields applicable to all types of variables */ --- 159,208 ---- */ enum config_type { ! PGC_BOOL = 0, ! PGC_INT = 1, ! PGC_REAL = 2, ! PGC_STRING = 3 ! }; ! ! /* ! * Used for pg_settings. Keep in sync with config_type enum above ! */ ! static char *config_type_name[] = ! { ! "bool", ! "integer", ! "real", ! "string" ! }; ! ! /* ! * Used for pg_settings. Keep in sync with GucContext enum in guc.h ! */ ! static char *GucContextName[] = ! { ! "internal", ! "postmaster", ! "sighup", ! "backend", ! "super-user", ! "user" ! }; ! ! /* ! * Used for pg_settings. Keep in sync with GucSource enum in guc.h ! */ ! static char *GucSourceName[] = ! { ! "default", ! "environment variable", ! "configuration file", ! "command line", ! "database", ! "user", ! "client", ! "override", ! "session" }; /* Generic fields applicable to all types of variables */ *************** *** 2617,2639 **** * Return GUC variable value by variable number; optionally return canonical * form of name. Return value is palloc'd. */ ! char * ! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow) { ! struct config_generic *conf; /* check requested variable number valid */ Assert((varnum >= 0) && (varnum < num_guc_variables)); conf = guc_variables[varnum]; - if (varname) - *varname = conf->name; - if (noshow) *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; ! return _ShowOption(conf); } /* --- 2657,2758 ---- * Return GUC variable value by variable number; optionally return canonical * form of name. Return value is palloc'd. */ ! void ! GetConfigOptionByNum(int varnum, const char **values, bool *noshow) { ! char buffer[256]; ! struct config_generic *conf; /* check requested variable number valid */ Assert((varnum >= 0) && (varnum < num_guc_variables)); conf = guc_variables[varnum]; if (noshow) *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; ! /* first get the generic attributes */ ! ! /* name */ ! values[0] = conf->name; ! ! /* setting : use _ShowOption in order to avoid duplicating the logic */ ! values[1] = _ShowOption(conf); ! ! /* context */ ! values[2] = GucContextName[conf->context]; ! ! /* vartype */ ! values[3] = config_type_name[conf->vartype]; ! ! /* source */ ! values[4] = GucSourceName[conf->source]; ! ! /* now get the type specifc attributes */ ! switch (conf->vartype) ! { ! case PGC_BOOL: ! { ! /* min_val */ ! values[5] = NULL; ! ! /* max_val */ ! values[6] = NULL; ! } ! break; ! ! case PGC_INT: ! { ! struct config_int *lconf = (struct config_int *) conf; ! ! /* min_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->min); ! values[5] = pstrdup(buffer); ! ! /* max_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->max); ! values[6] = pstrdup(buffer); ! } ! break; ! ! case PGC_REAL: ! { ! struct config_real *lconf = (struct config_real *) conf; ! ! /* min_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->min); ! values[5] = pstrdup(buffer); ! ! /* max_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->max); ! values[6] = pstrdup(buffer); ! } ! break; ! ! case PGC_STRING: ! { ! /* min_val */ ! values[5] = NULL; ! ! /* max_val */ ! values[6] = NULL; ! } ! break; ! ! default: ! { ! /* ! * should never get here, but in case we do, set 'em to NULL ! */ ! ! /* min_val */ ! values[5] = NULL; ! ! /* max_val */ ! values[6] = NULL; ! } ! break; ! } } /* *************** *** 2673,2678 **** --- 2792,2799 ---- * show_all_settings - equiv to SHOW ALL command but implemented as * a Table Function. */ + #define NUM_PG_SETTINGS_ATTS 7 + Datum show_all_settings(PG_FUNCTION_ARGS) { *************** *** 2696,2707 **** */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! /* need a tuple descriptor representing two TEXT columns */ ! tupdesc = CreateTemplateTupleDesc(2, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0, false); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", TEXTOID, -1, 0, false); /* allocate a slot for a tuple with this tupdesc */ slot = TupleDescGetSlot(tupdesc); --- 2817,2841 ---- */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! /* ! * need a tuple descriptor representing NUM_PG_SETTINGS_ATTS columns ! * of the appropriate types ! */ ! tupdesc = CreateTemplateTupleDesc(NUM_PG_SETTINGS_ATTS, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0, false); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "context", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "vartype", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "source", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "min_val", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "max_val", + TEXTOID, -1, 0, false); /* allocate a slot for a tuple with this tupdesc */ slot = TupleDescGetSlot(tupdesc); *************** *** 2732,2740 **** if (call_cntr < max_calls) /* do when there is more left to send */ { ! char *values[2]; ! char *varname; ! char *varval; bool noshow; HeapTuple tuple; Datum result; --- 2866,2872 ---- if (call_cntr < max_calls) /* do when there is more left to send */ { ! char *values[NUM_PG_SETTINGS_ATTS]; bool noshow; HeapTuple tuple; Datum result; *************** *** 2744,2758 **** */ do { ! varval = GetConfigOptionByNum(call_cntr, ! (const char **) &varname, ! &noshow); if (noshow) { - /* varval is a palloc'd copy, so free it */ - if (varval != NULL) - pfree(varval); - /* bump the counter and get the next config setting */ call_cntr = ++funcctx->call_cntr; --- 2876,2884 ---- */ do { ! GetConfigOptionByNum(call_cntr, (const char **) values, &noshow); if (noshow) { /* bump the counter and get the next config setting */ call_cntr = ++funcctx->call_cntr; *************** *** 2762,2784 **** } } while (noshow); - /* - * Prepare a values array for storage in our slot. This should be - * an array of C strings which will be processed later by the - * appropriate "in" functions. - */ - values[0] = varname; - values[1] = varval; - /* build a tuple */ tuple = BuildTupleFromCStrings(attinmeta, values); /* make the tuple into a datum */ result = TupleGetDatum(slot, tuple); - - /* Clean up */ - if (varval != NULL) - pfree(varval); SRF_RETURN_NEXT(funcctx, result); } --- 2888,2898 ---- Index: src/bin/initdb/initdb.sh =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v retrieving revision 1.192 diff -c -r1.192 initdb.sh *** src/bin/initdb/initdb.sh 2 Jun 2003 19:00:29 -0000 1.192 --- src/bin/initdb/initdb.sh 26 Jun 2003 03:56:23 -0000 *************** *** 971,977 **** CREATE VIEW pg_settings AS \ SELECT * \ ! FROM pg_show_all_settings() AS A(name text, setting text); CREATE RULE pg_settings_u AS \ ON UPDATE TO pg_settings \ --- 971,979 ---- CREATE VIEW pg_settings AS \ SELECT * \ ! FROM pg_show_all_settings() AS A \ ! (name text, setting text, context text, vartype text, \ ! source text, min_val text, max_val text); CREATE RULE pg_settings_u AS \ ON UPDATE TO pg_settings \ Index: src/include/utils/guc.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc.h,v retrieving revision 1.32 diff -c -r1.32 guc.h *** src/include/utils/guc.h 11 Jun 2003 18:01:14 -0000 1.32 --- src/include/utils/guc.h 26 Jun 2003 02:58:54 -0000 *************** *** 49,63 **** * we don't yet know if the user is a superuser. * * USERSET options can be set by anyone any time. */ typedef enum { ! PGC_INTERNAL, ! PGC_POSTMASTER, ! PGC_SIGHUP, ! PGC_BACKEND, ! PGC_SUSET, ! PGC_USERSET } GucContext; /* --- 49,65 ---- * we don't yet know if the user is a superuser. * * USERSET options can be set by anyone any time. + * + * Keep in sync with GucContextName in guc.c */ typedef enum { ! PGC_INTERNAL = 0, ! PGC_POSTMASTER = 1, ! PGC_SIGHUP = 2, ! PGC_BACKEND = 3, ! PGC_SUSET = 4, ! PGC_USERSET = 5 } GucContext; /* *************** *** 69,74 **** --- 71,78 ---- * Sources <= PGC_S_OVERRIDE will set the default used by RESET, as well * as the current value. Note that source == PGC_S_OVERRIDE should be * used when setting a PGC_INTERNAL option. + * + * Keep in sync with GucSourceName in guc.c */ typedef enum { *************** *** 83,89 **** PGC_S_SESSION = 8 /* SET command */ } GucSource; - /* GUC vars that are actually declared in guc.c, rather than elsewhere */ extern bool log_statement; extern bool log_duration; --- 87,92 ---- *************** *** 123,129 **** extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); extern void ShowAllGUCConfig(DestReceiver *dest); extern char *GetConfigOptionByName(const char *name, const char **varname); ! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow); extern int GetNumConfigOptions(void); extern void SetPGVariable(const char *name, List *args, bool is_local); --- 126,132 ---- extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); extern void ShowAllGUCConfig(DestReceiver *dest); extern char *GetConfigOptionByName(const char *name, const char **varname); ! extern void GetConfigOptionByNum(int varnum, const char **values, bool *noshow); extern int GetNumConfigOptions(void); extern void SetPGVariable(const char *name, List *args, bool is_local); Index: src/test/regress/expected/rangefuncs.out =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v retrieving revision 1.7 diff -c -r1.7 rangefuncs.out *** src/test/regress/expected/rangefuncs.out 13 Feb 2003 20:45:22 -0000 1.7 --- src/test/regress/expected/rangefuncs.out 26 Jun 2003 02:58:54 -0000 *************** *** 1,4 **** ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; name | setting ------------------+--------- enable_hashagg | on --- 1,4 ---- ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; name | setting ------------------+--------- enable_hashagg | on Index: src/test/regress/expected/rules.out =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v retrieving revision 1.75 diff -c -r1.75 rules.out *** src/test/regress/expected/rules.out 26 May 2003 00:11:28 -0000 1.75 --- src/test/regress/expected/rules.out 26 Jun 2003 04:40:11 -0000 *************** *** 1273,1279 **** pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); ! pg_settings | SELECT a.name, a.setting FROM pg_show_all_settings() a(name text, setting text); pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; --- 1273,1279 ---- pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); ! pg_settings | SELECT a.name, a.setting, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings()a(name text, setting text, context text, vartype text, source text, min_val text, max_val text); pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; Index: src/test/regress/sql/rangefuncs.sql =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/rangefuncs.sql,v retrieving revision 1.4 diff -c -r1.4 rangefuncs.sql *** src/test/regress/sql/rangefuncs.sql 30 Aug 2002 19:56:49 -0000 1.4 --- src/test/regress/sql/rangefuncs.sql 26 Jun 2003 02:58:54 -0000 *************** *** 1,4 **** ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11); --- 1,4 ---- ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11);
I'm going to resend the patches that I have outstanding since it appears some may have been lost. Here's the third of three. =================================================== Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: >>ISTM that "source" is worth knowing. > > Hm, possibly. Any other opinions? This version has the seven fields I proposed, including "source". Here's an example that shows why I think it's valuable: regression=# \x Expanded display is on. regression=# select * from pg_settings where name = 'enable_seqscan'; -[ RECORD 1 ]----------- name | enable_seqscan setting | on context | user vartype | bool source | default min_val | max_val | regression=# update pg_settings set setting = 'off' where name = 'enable_seqscan'; -[ RECORD 1 ]--- set_config | off regression=# select * from pg_settings where name = 'enable_seqscan'; -[ RECORD 1 ]----------- name | enable_seqscan setting | off context | user vartype | bool source | session min_val | max_val | regression=# alter user postgres set enable_seqscan to 'off'; ALTER USER (log out and then back in again) regression=# \x Expanded display is on. regression=# select * from pg_settings where name = 'enable_seqscan'; -[ RECORD 1 ]----------- name | enable_seqscan setting | off context | user vartype | bool source | user min_val | max_val | In the first case, enable_seqscan is set to its default value. After setting it to off, it is obvious that the value has been changed for the session only. In the third case, you can see that the value has been set specifically for the user. This version of the patch also includes documentation changes. Passes all regression tests. Please apply. Joe Index: doc/src/sgml/runtime.sgml =================================================================== RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.187 diff -c -r1.187 runtime.sgml *** doc/src/sgml/runtime.sgml 25 Jun 2003 01:13:24 -0000 1.187 --- doc/src/sgml/runtime.sgml 26 Jun 2003 05:01:56 -0000 *************** *** 571,584 **** <row> <entry><literal>name</literal></entry> <entry><type>text</type></entry> ! <entry>The name of the run-time configuration parameter</entry> </row> <row> <entry><literal>setting</literal></entry> <entry><type>text</type></entry> ! <entry>The current value of the run-time configuration parameter</entry> </row> </tbody> </tgroup> </table> --- 571,615 ---- <row> <entry><literal>name</literal></entry> <entry><type>text</type></entry> ! <entry>run-time configuration parameter name</entry> </row> <row> <entry><literal>setting</literal></entry> <entry><type>text</type></entry> ! <entry>current value of the parameter</entry> </row> + + <row> + <entry><literal>context</literal></entry> + <entry><type>text</type></entry> + <entry>context required to set the parameter's value</entry> + </row> + + <row> + <entry><literal>vartype</literal></entry> + <entry><type>text</type></entry> + <entry>parameter type</entry> + </row> + + <row> + <entry><literal>source</literal></entry> + <entry><type>text</type></entry> + <entry>source of the current parameter value</entry> + </row> + + <row> + <entry><literal>min_val</literal></entry> + <entry><type>text</type></entry> + <entry>minimum allowed value of the parameter</entry> + </row> + + <row> + <entry><literal>max_val</literal></entry> + <entry><type>text</type></entry> + <entry>maximum allowed value of the parameter</entry> + </row> + </tbody> </tgroup> </table> Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v retrieving revision 1.131 diff -c -r1.131 guc.c *** src/backend/utils/misc/guc.c 11 Jun 2003 22:13:22 -0000 1.131 --- src/backend/utils/misc/guc.c 26 Jun 2003 04:03:00 -0000 *************** *** 159,168 **** */ enum config_type { ! PGC_BOOL, ! PGC_INT, ! PGC_REAL, ! PGC_STRING }; /* Generic fields applicable to all types of variables */ --- 159,208 ---- */ enum config_type { ! PGC_BOOL = 0, ! PGC_INT = 1, ! PGC_REAL = 2, ! PGC_STRING = 3 ! }; ! ! /* ! * Used for pg_settings. Keep in sync with config_type enum above ! */ ! static char *config_type_name[] = ! { ! "bool", ! "integer", ! "real", ! "string" ! }; ! ! /* ! * Used for pg_settings. Keep in sync with GucContext enum in guc.h ! */ ! static char *GucContextName[] = ! { ! "internal", ! "postmaster", ! "sighup", ! "backend", ! "super-user", ! "user" ! }; ! ! /* ! * Used for pg_settings. Keep in sync with GucSource enum in guc.h ! */ ! static char *GucSourceName[] = ! { ! "default", ! "environment variable", ! "configuration file", ! "command line", ! "database", ! "user", ! "client", ! "override", ! "session" }; /* Generic fields applicable to all types of variables */ *************** *** 2617,2639 **** * Return GUC variable value by variable number; optionally return canonical * form of name. Return value is palloc'd. */ ! char * ! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow) { ! struct config_generic *conf; /* check requested variable number valid */ Assert((varnum >= 0) && (varnum < num_guc_variables)); conf = guc_variables[varnum]; - if (varname) - *varname = conf->name; - if (noshow) *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; ! return _ShowOption(conf); } /* --- 2657,2758 ---- * Return GUC variable value by variable number; optionally return canonical * form of name. Return value is palloc'd. */ ! void ! GetConfigOptionByNum(int varnum, const char **values, bool *noshow) { ! char buffer[256]; ! struct config_generic *conf; /* check requested variable number valid */ Assert((varnum >= 0) && (varnum < num_guc_variables)); conf = guc_variables[varnum]; if (noshow) *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; ! /* first get the generic attributes */ ! ! /* name */ ! values[0] = conf->name; ! ! /* setting : use _ShowOption in order to avoid duplicating the logic */ ! values[1] = _ShowOption(conf); ! ! /* context */ ! values[2] = GucContextName[conf->context]; ! ! /* vartype */ ! values[3] = config_type_name[conf->vartype]; ! ! /* source */ ! values[4] = GucSourceName[conf->source]; ! ! /* now get the type specifc attributes */ ! switch (conf->vartype) ! { ! case PGC_BOOL: ! { ! /* min_val */ ! values[5] = NULL; ! ! /* max_val */ ! values[6] = NULL; ! } ! break; ! ! case PGC_INT: ! { ! struct config_int *lconf = (struct config_int *) conf; ! ! /* min_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->min); ! values[5] = pstrdup(buffer); ! ! /* max_val */ ! snprintf(buffer, sizeof(buffer), "%d", lconf->max); ! values[6] = pstrdup(buffer); ! } ! break; ! ! case PGC_REAL: ! { ! struct config_real *lconf = (struct config_real *) conf; ! ! /* min_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->min); ! values[5] = pstrdup(buffer); ! ! /* max_val */ ! snprintf(buffer, sizeof(buffer), "%g", lconf->max); ! values[6] = pstrdup(buffer); ! } ! break; ! ! case PGC_STRING: ! { ! /* min_val */ ! values[5] = NULL; ! ! /* max_val */ ! values[6] = NULL; ! } ! break; ! ! default: ! { ! /* ! * should never get here, but in case we do, set 'em to NULL ! */ ! ! /* min_val */ ! values[5] = NULL; ! ! /* max_val */ ! values[6] = NULL; ! } ! break; ! } } /* *************** *** 2673,2678 **** --- 2792,2799 ---- * show_all_settings - equiv to SHOW ALL command but implemented as * a Table Function. */ + #define NUM_PG_SETTINGS_ATTS 7 + Datum show_all_settings(PG_FUNCTION_ARGS) { *************** *** 2696,2707 **** */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! /* need a tuple descriptor representing two TEXT columns */ ! tupdesc = CreateTemplateTupleDesc(2, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0, false); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", TEXTOID, -1, 0, false); /* allocate a slot for a tuple with this tupdesc */ slot = TupleDescGetSlot(tupdesc); --- 2817,2841 ---- */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! /* ! * need a tuple descriptor representing NUM_PG_SETTINGS_ATTS columns ! * of the appropriate types ! */ ! tupdesc = CreateTemplateTupleDesc(NUM_PG_SETTINGS_ATTS, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", TEXTOID, -1, 0, false); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "context", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "vartype", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "source", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "min_val", + TEXTOID, -1, 0, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "max_val", + TEXTOID, -1, 0, false); /* allocate a slot for a tuple with this tupdesc */ slot = TupleDescGetSlot(tupdesc); *************** *** 2732,2740 **** if (call_cntr < max_calls) /* do when there is more left to send */ { ! char *values[2]; ! char *varname; ! char *varval; bool noshow; HeapTuple tuple; Datum result; --- 2866,2872 ---- if (call_cntr < max_calls) /* do when there is more left to send */ { ! char *values[NUM_PG_SETTINGS_ATTS]; bool noshow; HeapTuple tuple; Datum result; *************** *** 2744,2758 **** */ do { ! varval = GetConfigOptionByNum(call_cntr, ! (const char **) &varname, ! &noshow); if (noshow) { - /* varval is a palloc'd copy, so free it */ - if (varval != NULL) - pfree(varval); - /* bump the counter and get the next config setting */ call_cntr = ++funcctx->call_cntr; --- 2876,2884 ---- */ do { ! GetConfigOptionByNum(call_cntr, (const char **) values, &noshow); if (noshow) { /* bump the counter and get the next config setting */ call_cntr = ++funcctx->call_cntr; *************** *** 2762,2784 **** } } while (noshow); - /* - * Prepare a values array for storage in our slot. This should be - * an array of C strings which will be processed later by the - * appropriate "in" functions. - */ - values[0] = varname; - values[1] = varval; - /* build a tuple */ tuple = BuildTupleFromCStrings(attinmeta, values); /* make the tuple into a datum */ result = TupleGetDatum(slot, tuple); - - /* Clean up */ - if (varval != NULL) - pfree(varval); SRF_RETURN_NEXT(funcctx, result); } --- 2888,2898 ---- Index: src/bin/initdb/initdb.sh =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v retrieving revision 1.192 diff -c -r1.192 initdb.sh *** src/bin/initdb/initdb.sh 2 Jun 2003 19:00:29 -0000 1.192 --- src/bin/initdb/initdb.sh 26 Jun 2003 03:56:23 -0000 *************** *** 971,977 **** CREATE VIEW pg_settings AS \ SELECT * \ ! FROM pg_show_all_settings() AS A(name text, setting text); CREATE RULE pg_settings_u AS \ ON UPDATE TO pg_settings \ --- 971,979 ---- CREATE VIEW pg_settings AS \ SELECT * \ ! FROM pg_show_all_settings() AS A \ ! (name text, setting text, context text, vartype text, \ ! source text, min_val text, max_val text); CREATE RULE pg_settings_u AS \ ON UPDATE TO pg_settings \ Index: src/include/utils/guc.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc.h,v retrieving revision 1.32 diff -c -r1.32 guc.h *** src/include/utils/guc.h 11 Jun 2003 18:01:14 -0000 1.32 --- src/include/utils/guc.h 26 Jun 2003 02:58:54 -0000 *************** *** 49,63 **** * we don't yet know if the user is a superuser. * * USERSET options can be set by anyone any time. */ typedef enum { ! PGC_INTERNAL, ! PGC_POSTMASTER, ! PGC_SIGHUP, ! PGC_BACKEND, ! PGC_SUSET, ! PGC_USERSET } GucContext; /* --- 49,65 ---- * we don't yet know if the user is a superuser. * * USERSET options can be set by anyone any time. + * + * Keep in sync with GucContextName in guc.c */ typedef enum { ! PGC_INTERNAL = 0, ! PGC_POSTMASTER = 1, ! PGC_SIGHUP = 2, ! PGC_BACKEND = 3, ! PGC_SUSET = 4, ! PGC_USERSET = 5 } GucContext; /* *************** *** 69,74 **** --- 71,78 ---- * Sources <= PGC_S_OVERRIDE will set the default used by RESET, as well * as the current value. Note that source == PGC_S_OVERRIDE should be * used when setting a PGC_INTERNAL option. + * + * Keep in sync with GucSourceName in guc.c */ typedef enum { *************** *** 83,89 **** PGC_S_SESSION = 8 /* SET command */ } GucSource; - /* GUC vars that are actually declared in guc.c, rather than elsewhere */ extern bool log_statement; extern bool log_duration; --- 87,92 ---- *************** *** 123,129 **** extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); extern void ShowAllGUCConfig(DestReceiver *dest); extern char *GetConfigOptionByName(const char *name, const char **varname); ! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow); extern int GetNumConfigOptions(void); extern void SetPGVariable(const char *name, List *args, bool is_local); --- 126,132 ---- extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); extern void ShowAllGUCConfig(DestReceiver *dest); extern char *GetConfigOptionByName(const char *name, const char **varname); ! extern void GetConfigOptionByNum(int varnum, const char **values, bool *noshow); extern int GetNumConfigOptions(void); extern void SetPGVariable(const char *name, List *args, bool is_local); Index: src/test/regress/expected/rangefuncs.out =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v retrieving revision 1.7 diff -c -r1.7 rangefuncs.out *** src/test/regress/expected/rangefuncs.out 13 Feb 2003 20:45:22 -0000 1.7 --- src/test/regress/expected/rangefuncs.out 26 Jun 2003 02:58:54 -0000 *************** *** 1,4 **** ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; name | setting ------------------+--------- enable_hashagg | on --- 1,4 ---- ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; name | setting ------------------+--------- enable_hashagg | on Index: src/test/regress/expected/rules.out =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v retrieving revision 1.75 diff -c -r1.75 rules.out *** src/test/regress/expected/rules.out 26 May 2003 00:11:28 -0000 1.75 --- src/test/regress/expected/rules.out 26 Jun 2003 04:40:11 -0000 *************** *** 1273,1279 **** pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); ! pg_settings | SELECT a.name, a.setting FROM pg_show_all_settings() a(name text, setting text); pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; --- 1273,1279 ---- pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); ! pg_settings | SELECT a.name, a.setting, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings()a(name text, setting text, context text, vartype text, source text, min_val text, max_val text); pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; Index: src/test/regress/sql/rangefuncs.sql =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/rangefuncs.sql,v retrieving revision 1.4 diff -c -r1.4 rangefuncs.sql *** src/test/regress/sql/rangefuncs.sql 30 Aug 2002 19:56:49 -0000 1.4 --- src/test/regress/sql/rangefuncs.sql 26 Jun 2003 02:58:54 -0000 *************** *** 1,4 **** ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11); --- 1,4 ---- ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; CREATE TABLE foo2(fooid int, f2 int); INSERT INTO foo2 VALUES(1, 11);
Tom Lane wrote: > This reminds me, someone (Barry?) was griping that SHOW DATESTYLE > doesn't produce a value that SET DATESTYLE will take. Did we agree > that it was OK to change the output to look like "ISO, US" etc? Is this a TODO? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Joe Conway wrote: > I'm going to resend the patches that I have outstanding since it appears > some may have been lost. Here's the third of three. > =================================================== > > > Tom Lane wrote: > > Joe Conway <mail@joeconway.com> writes: > >>ISTM that "source" is worth knowing. > > > > Hm, possibly. Any other opinions? > > This version has the seven fields I proposed, including "source". Here's > an example that shows why I think it's valuable: > > regression=# \x > Expanded display is on. > regression=# select * from pg_settings where name = 'enable_seqscan'; > -[ RECORD 1 ]----------- > name | enable_seqscan > setting | on > context | user > vartype | bool > source | default > min_val | > max_val | > > regression=# update pg_settings set setting = 'off' where name = > 'enable_seqscan'; > -[ RECORD 1 ]--- > set_config | off > > regression=# select * from pg_settings where name = 'enable_seqscan'; > -[ RECORD 1 ]----------- > name | enable_seqscan > setting | off > context | user > vartype | bool > source | session > min_val | > max_val | > > regression=# alter user postgres set enable_seqscan to 'off'; > ALTER USER > > (log out and then back in again) > > regression=# \x > Expanded display is on. > regression=# select * from pg_settings where name = 'enable_seqscan'; > -[ RECORD 1 ]----------- > name | enable_seqscan > setting | off > context | user > vartype | bool > source | user > min_val | > max_val | > > In the first case, enable_seqscan is set to its default value. After > setting it to off, it is obvious that the value has been changed for the > session only. In the third case, you can see that the value has been set > specifically for the user. > > This version of the patch also includes documentation changes. Passes > all regression tests. Please apply. > > Joe > > Index: doc/src/sgml/runtime.sgml > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v > retrieving revision 1.187 > diff -c -r1.187 runtime.sgml > *** doc/src/sgml/runtime.sgml 25 Jun 2003 01:13:24 -0000 1.187 > --- doc/src/sgml/runtime.sgml 26 Jun 2003 05:01:56 -0000 > *************** > *** 571,584 **** > <row> > <entry><literal>name</literal></entry> > <entry><type>text</type></entry> > ! <entry>The name of the run-time configuration parameter</entry> > </row> > > <row> > <entry><literal>setting</literal></entry> > <entry><type>text</type></entry> > ! <entry>The current value of the run-time configuration parameter</entry> > </row> > </tbody> > </tgroup> > </table> > --- 571,615 ---- > <row> > <entry><literal>name</literal></entry> > <entry><type>text</type></entry> > ! <entry>run-time configuration parameter name</entry> > </row> > > <row> > <entry><literal>setting</literal></entry> > <entry><type>text</type></entry> > ! <entry>current value of the parameter</entry> > </row> > + > + <row> > + <entry><literal>context</literal></entry> > + <entry><type>text</type></entry> > + <entry>context required to set the parameter's value</entry> > + </row> > + > + <row> > + <entry><literal>vartype</literal></entry> > + <entry><type>text</type></entry> > + <entry>parameter type</entry> > + </row> > + > + <row> > + <entry><literal>source</literal></entry> > + <entry><type>text</type></entry> > + <entry>source of the current parameter value</entry> > + </row> > + > + <row> > + <entry><literal>min_val</literal></entry> > + <entry><type>text</type></entry> > + <entry>minimum allowed value of the parameter</entry> > + </row> > + > + <row> > + <entry><literal>max_val</literal></entry> > + <entry><type>text</type></entry> > + <entry>maximum allowed value of the parameter</entry> > + </row> > + > </tbody> > </tgroup> > </table> > Index: src/backend/utils/misc/guc.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v > retrieving revision 1.131 > diff -c -r1.131 guc.c > *** src/backend/utils/misc/guc.c 11 Jun 2003 22:13:22 -0000 1.131 > --- src/backend/utils/misc/guc.c 26 Jun 2003 04:03:00 -0000 > *************** > *** 159,168 **** > */ > enum config_type > { > ! PGC_BOOL, > ! PGC_INT, > ! PGC_REAL, > ! PGC_STRING > }; > > /* Generic fields applicable to all types of variables */ > --- 159,208 ---- > */ > enum config_type > { > ! PGC_BOOL = 0, > ! PGC_INT = 1, > ! PGC_REAL = 2, > ! PGC_STRING = 3 > ! }; > ! > ! /* > ! * Used for pg_settings. Keep in sync with config_type enum above > ! */ > ! static char *config_type_name[] = > ! { > ! "bool", > ! "integer", > ! "real", > ! "string" > ! }; > ! > ! /* > ! * Used for pg_settings. Keep in sync with GucContext enum in guc.h > ! */ > ! static char *GucContextName[] = > ! { > ! "internal", > ! "postmaster", > ! "sighup", > ! "backend", > ! "super-user", > ! "user" > ! }; > ! > ! /* > ! * Used for pg_settings. Keep in sync with GucSource enum in guc.h > ! */ > ! static char *GucSourceName[] = > ! { > ! "default", > ! "environment variable", > ! "configuration file", > ! "command line", > ! "database", > ! "user", > ! "client", > ! "override", > ! "session" > }; > > /* Generic fields applicable to all types of variables */ > *************** > *** 2617,2639 **** > * Return GUC variable value by variable number; optionally return canonical > * form of name. Return value is palloc'd. > */ > ! char * > ! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow) > { > ! struct config_generic *conf; > > /* check requested variable number valid */ > Assert((varnum >= 0) && (varnum < num_guc_variables)); > > conf = guc_variables[varnum]; > > - if (varname) > - *varname = conf->name; > - > if (noshow) > *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; > > ! return _ShowOption(conf); > } > > /* > --- 2657,2758 ---- > * Return GUC variable value by variable number; optionally return canonical > * form of name. Return value is palloc'd. > */ > ! void > ! GetConfigOptionByNum(int varnum, const char **values, bool *noshow) > { > ! char buffer[256]; > ! struct config_generic *conf; > > /* check requested variable number valid */ > Assert((varnum >= 0) && (varnum < num_guc_variables)); > > conf = guc_variables[varnum]; > > if (noshow) > *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; > > ! /* first get the generic attributes */ > ! > ! /* name */ > ! values[0] = conf->name; > ! > ! /* setting : use _ShowOption in order to avoid duplicating the logic */ > ! values[1] = _ShowOption(conf); > ! > ! /* context */ > ! values[2] = GucContextName[conf->context]; > ! > ! /* vartype */ > ! values[3] = config_type_name[conf->vartype]; > ! > ! /* source */ > ! values[4] = GucSourceName[conf->source]; > ! > ! /* now get the type specifc attributes */ > ! switch (conf->vartype) > ! { > ! case PGC_BOOL: > ! { > ! /* min_val */ > ! values[5] = NULL; > ! > ! /* max_val */ > ! values[6] = NULL; > ! } > ! break; > ! > ! case PGC_INT: > ! { > ! struct config_int *lconf = (struct config_int *) conf; > ! > ! /* min_val */ > ! snprintf(buffer, sizeof(buffer), "%d", lconf->min); > ! values[5] = pstrdup(buffer); > ! > ! /* max_val */ > ! snprintf(buffer, sizeof(buffer), "%d", lconf->max); > ! values[6] = pstrdup(buffer); > ! } > ! break; > ! > ! case PGC_REAL: > ! { > ! struct config_real *lconf = (struct config_real *) conf; > ! > ! /* min_val */ > ! snprintf(buffer, sizeof(buffer), "%g", lconf->min); > ! values[5] = pstrdup(buffer); > ! > ! /* max_val */ > ! snprintf(buffer, sizeof(buffer), "%g", lconf->max); > ! values[6] = pstrdup(buffer); > ! } > ! break; > ! > ! case PGC_STRING: > ! { > ! /* min_val */ > ! values[5] = NULL; > ! > ! /* max_val */ > ! values[6] = NULL; > ! } > ! break; > ! > ! default: > ! { > ! /* > ! * should never get here, but in case we do, set 'em to NULL > ! */ > ! > ! /* min_val */ > ! values[5] = NULL; > ! > ! /* max_val */ > ! values[6] = NULL; > ! } > ! break; > ! } > } > > /* > *************** > *** 2673,2678 **** > --- 2792,2799 ---- > * show_all_settings - equiv to SHOW ALL command but implemented as > * a Table Function. > */ > + #define NUM_PG_SETTINGS_ATTS 7 > + > Datum > show_all_settings(PG_FUNCTION_ARGS) > { > *************** > *** 2696,2707 **** > */ > oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); > > ! /* need a tuple descriptor representing two TEXT columns */ > ! tupdesc = CreateTemplateTupleDesc(2, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", > TEXTOID, -1, 0, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", > TEXTOID, -1, 0, false); > > /* allocate a slot for a tuple with this tupdesc */ > slot = TupleDescGetSlot(tupdesc); > --- 2817,2841 ---- > */ > oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); > > ! /* > ! * need a tuple descriptor representing NUM_PG_SETTINGS_ATTS columns > ! * of the appropriate types > ! */ > ! tupdesc = CreateTemplateTupleDesc(NUM_PG_SETTINGS_ATTS, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", > TEXTOID, -1, 0, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", > TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "context", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "vartype", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "source", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "min_val", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "max_val", > + TEXTOID, -1, 0, false); > > /* allocate a slot for a tuple with this tupdesc */ > slot = TupleDescGetSlot(tupdesc); > *************** > *** 2732,2740 **** > > if (call_cntr < max_calls) /* do when there is more left to send */ > { > ! char *values[2]; > ! char *varname; > ! char *varval; > bool noshow; > HeapTuple tuple; > Datum result; > --- 2866,2872 ---- > > if (call_cntr < max_calls) /* do when there is more left to send */ > { > ! char *values[NUM_PG_SETTINGS_ATTS]; > bool noshow; > HeapTuple tuple; > Datum result; > *************** > *** 2744,2758 **** > */ > do > { > ! varval = GetConfigOptionByNum(call_cntr, > ! (const char **) &varname, > ! &noshow); > if (noshow) > { > - /* varval is a palloc'd copy, so free it */ > - if (varval != NULL) > - pfree(varval); > - > /* bump the counter and get the next config setting */ > call_cntr = ++funcctx->call_cntr; > > --- 2876,2884 ---- > */ > do > { > ! GetConfigOptionByNum(call_cntr, (const char **) values, &noshow); > if (noshow) > { > /* bump the counter and get the next config setting */ > call_cntr = ++funcctx->call_cntr; > > *************** > *** 2762,2784 **** > } > } while (noshow); > > - /* > - * Prepare a values array for storage in our slot. This should be > - * an array of C strings which will be processed later by the > - * appropriate "in" functions. > - */ > - values[0] = varname; > - values[1] = varval; > - > /* build a tuple */ > tuple = BuildTupleFromCStrings(attinmeta, values); > > /* make the tuple into a datum */ > result = TupleGetDatum(slot, tuple); > - > - /* Clean up */ > - if (varval != NULL) > - pfree(varval); > > SRF_RETURN_NEXT(funcctx, result); > } > --- 2888,2898 ---- > Index: src/bin/initdb/initdb.sh > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v > retrieving revision 1.192 > diff -c -r1.192 initdb.sh > *** src/bin/initdb/initdb.sh 2 Jun 2003 19:00:29 -0000 1.192 > --- src/bin/initdb/initdb.sh 26 Jun 2003 03:56:23 -0000 > *************** > *** 971,977 **** > > CREATE VIEW pg_settings AS \ > SELECT * \ > ! FROM pg_show_all_settings() AS A(name text, setting text); > > CREATE RULE pg_settings_u AS \ > ON UPDATE TO pg_settings \ > --- 971,979 ---- > > CREATE VIEW pg_settings AS \ > SELECT * \ > ! FROM pg_show_all_settings() AS A \ > ! (name text, setting text, context text, vartype text, \ > ! source text, min_val text, max_val text); > > CREATE RULE pg_settings_u AS \ > ON UPDATE TO pg_settings \ > Index: src/include/utils/guc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc.h,v > retrieving revision 1.32 > diff -c -r1.32 guc.h > *** src/include/utils/guc.h 11 Jun 2003 18:01:14 -0000 1.32 > --- src/include/utils/guc.h 26 Jun 2003 02:58:54 -0000 > *************** > *** 49,63 **** > * we don't yet know if the user is a superuser. > * > * USERSET options can be set by anyone any time. > */ > typedef enum > { > ! PGC_INTERNAL, > ! PGC_POSTMASTER, > ! PGC_SIGHUP, > ! PGC_BACKEND, > ! PGC_SUSET, > ! PGC_USERSET > } GucContext; > > /* > --- 49,65 ---- > * we don't yet know if the user is a superuser. > * > * USERSET options can be set by anyone any time. > + * > + * Keep in sync with GucContextName in guc.c > */ > typedef enum > { > ! PGC_INTERNAL = 0, > ! PGC_POSTMASTER = 1, > ! PGC_SIGHUP = 2, > ! PGC_BACKEND = 3, > ! PGC_SUSET = 4, > ! PGC_USERSET = 5 > } GucContext; > > /* > *************** > *** 69,74 **** > --- 71,78 ---- > * Sources <= PGC_S_OVERRIDE will set the default used by RESET, as well > * as the current value. Note that source == PGC_S_OVERRIDE should be > * used when setting a PGC_INTERNAL option. > + * > + * Keep in sync with GucSourceName in guc.c > */ > typedef enum > { > *************** > *** 83,89 **** > PGC_S_SESSION = 8 /* SET command */ > } GucSource; > > - > /* GUC vars that are actually declared in guc.c, rather than elsewhere */ > extern bool log_statement; > extern bool log_duration; > --- 87,92 ---- > *************** > *** 123,129 **** > extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); > extern void ShowAllGUCConfig(DestReceiver *dest); > extern char *GetConfigOptionByName(const char *name, const char **varname); > ! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow); > extern int GetNumConfigOptions(void); > > extern void SetPGVariable(const char *name, List *args, bool is_local); > --- 126,132 ---- > extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); > extern void ShowAllGUCConfig(DestReceiver *dest); > extern char *GetConfigOptionByName(const char *name, const char **varname); > ! extern void GetConfigOptionByNum(int varnum, const char **values, bool *noshow); > extern int GetNumConfigOptions(void); > > extern void SetPGVariable(const char *name, List *args, bool is_local); > Index: src/test/regress/expected/rangefuncs.out > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v > retrieving revision 1.7 > diff -c -r1.7 rangefuncs.out > *** src/test/regress/expected/rangefuncs.out 13 Feb 2003 20:45:22 -0000 1.7 > --- src/test/regress/expected/rangefuncs.out 26 Jun 2003 02:58:54 -0000 > *************** > *** 1,4 **** > ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; > name | setting > ------------------+--------- > enable_hashagg | on > --- 1,4 ---- > ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; > name | setting > ------------------+--------- > enable_hashagg | on > Index: src/test/regress/expected/rules.out > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v > retrieving revision 1.75 > diff -c -r1.75 rules.out > *** src/test/regress/expected/rules.out 26 May 2003 00:11:28 -0000 1.75 > --- src/test/regress/expected/rules.out 26 Jun 2003 04:40:11 -0000 > *************** > *** 1273,1279 **** > pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); > pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); > pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespacen ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); > ! pg_settings | SELECT a.name, a.setting FROM pg_show_all_settings() a(name text, setting text); > pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); > pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relnameAS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); > pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) ASseq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; > --- 1273,1279 ---- > pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); > pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); > pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespacen ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); > ! pg_settings | SELECT a.name, a.setting, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings()a(name text, setting text, context text, vartype text, source text, min_val text, max_val text); > pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); > pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relnameAS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); > pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) ASseq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; > Index: src/test/regress/sql/rangefuncs.sql > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/rangefuncs.sql,v > retrieving revision 1.4 > diff -c -r1.4 rangefuncs.sql > *** src/test/regress/sql/rangefuncs.sql 30 Aug 2002 19:56:49 -0000 1.4 > --- src/test/regress/sql/rangefuncs.sql 26 Jun 2003 02:58:54 -0000 > *************** > *** 1,4 **** > ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; > > CREATE TABLE foo2(fooid int, f2 int); > INSERT INTO foo2 VALUES(1, 11); > --- 1,4 ---- > ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; > > CREATE TABLE foo2(fooid int, f2 int); > INSERT INTO foo2 VALUES(1, 11); > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Tom Lane wrote: >> This reminds me, someone (Barry?) was griping that SHOW DATESTYLE >> doesn't produce a value that SET DATESTYLE will take. Did we agree >> that it was OK to change the output to look like "ISO, US" etc? > Is this a TODO? No, it's a DONE. regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> This reminds me, someone (Barry?) was griping that SHOW DATESTYLE > >> doesn't produce a value that SET DATESTYLE will take. Did we agree > >> that it was OK to change the output to look like "ISO, US" etc? > > > Is this a TODO? > > No, it's a DONE. I just tried this with CVS: test=> show datestyle; DateStyle --------------------------------------- ISO with US (NonEuropean) conventions (1 row) test=> set datestyle = 'ISO with US (NonEuropean) conventions'; ERROR: SET DATESTYLE: invalid list syntax Am I missing something? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Joe Conway wrote: > I'm going to resend the patches that I have outstanding since it appears > some may have been lost. Here's the third of three. > =================================================== > > > Tom Lane wrote: > > Joe Conway <mail@joeconway.com> writes: > >>ISTM that "source" is worth knowing. > > > > Hm, possibly. Any other opinions? > > This version has the seven fields I proposed, including "source". Here's > an example that shows why I think it's valuable: > > regression=# \x > Expanded display is on. > regression=# select * from pg_settings where name = 'enable_seqscan'; > -[ RECORD 1 ]----------- > name | enable_seqscan > setting | on > context | user > vartype | bool > source | default > min_val | > max_val | > > regression=# update pg_settings set setting = 'off' where name = > 'enable_seqscan'; > -[ RECORD 1 ]--- > set_config | off > > regression=# select * from pg_settings where name = 'enable_seqscan'; > -[ RECORD 1 ]----------- > name | enable_seqscan > setting | off > context | user > vartype | bool > source | session > min_val | > max_val | > > regression=# alter user postgres set enable_seqscan to 'off'; > ALTER USER > > (log out and then back in again) > > regression=# \x > Expanded display is on. > regression=# select * from pg_settings where name = 'enable_seqscan'; > -[ RECORD 1 ]----------- > name | enable_seqscan > setting | off > context | user > vartype | bool > source | user > min_val | > max_val | > > In the first case, enable_seqscan is set to its default value. After > setting it to off, it is obvious that the value has been changed for the > session only. In the third case, you can see that the value has been set > specifically for the user. > > This version of the patch also includes documentation changes. Passes > all regression tests. Please apply. > > Joe > > Index: doc/src/sgml/runtime.sgml > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v > retrieving revision 1.187 > diff -c -r1.187 runtime.sgml > *** doc/src/sgml/runtime.sgml 25 Jun 2003 01:13:24 -0000 1.187 > --- doc/src/sgml/runtime.sgml 26 Jun 2003 05:01:56 -0000 > *************** > *** 571,584 **** > <row> > <entry><literal>name</literal></entry> > <entry><type>text</type></entry> > ! <entry>The name of the run-time configuration parameter</entry> > </row> > > <row> > <entry><literal>setting</literal></entry> > <entry><type>text</type></entry> > ! <entry>The current value of the run-time configuration parameter</entry> > </row> > </tbody> > </tgroup> > </table> > --- 571,615 ---- > <row> > <entry><literal>name</literal></entry> > <entry><type>text</type></entry> > ! <entry>run-time configuration parameter name</entry> > </row> > > <row> > <entry><literal>setting</literal></entry> > <entry><type>text</type></entry> > ! <entry>current value of the parameter</entry> > </row> > + > + <row> > + <entry><literal>context</literal></entry> > + <entry><type>text</type></entry> > + <entry>context required to set the parameter's value</entry> > + </row> > + > + <row> > + <entry><literal>vartype</literal></entry> > + <entry><type>text</type></entry> > + <entry>parameter type</entry> > + </row> > + > + <row> > + <entry><literal>source</literal></entry> > + <entry><type>text</type></entry> > + <entry>source of the current parameter value</entry> > + </row> > + > + <row> > + <entry><literal>min_val</literal></entry> > + <entry><type>text</type></entry> > + <entry>minimum allowed value of the parameter</entry> > + </row> > + > + <row> > + <entry><literal>max_val</literal></entry> > + <entry><type>text</type></entry> > + <entry>maximum allowed value of the parameter</entry> > + </row> > + > </tbody> > </tgroup> > </table> > Index: src/backend/utils/misc/guc.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v > retrieving revision 1.131 > diff -c -r1.131 guc.c > *** src/backend/utils/misc/guc.c 11 Jun 2003 22:13:22 -0000 1.131 > --- src/backend/utils/misc/guc.c 26 Jun 2003 04:03:00 -0000 > *************** > *** 159,168 **** > */ > enum config_type > { > ! PGC_BOOL, > ! PGC_INT, > ! PGC_REAL, > ! PGC_STRING > }; > > /* Generic fields applicable to all types of variables */ > --- 159,208 ---- > */ > enum config_type > { > ! PGC_BOOL = 0, > ! PGC_INT = 1, > ! PGC_REAL = 2, > ! PGC_STRING = 3 > ! }; > ! > ! /* > ! * Used for pg_settings. Keep in sync with config_type enum above > ! */ > ! static char *config_type_name[] = > ! { > ! "bool", > ! "integer", > ! "real", > ! "string" > ! }; > ! > ! /* > ! * Used for pg_settings. Keep in sync with GucContext enum in guc.h > ! */ > ! static char *GucContextName[] = > ! { > ! "internal", > ! "postmaster", > ! "sighup", > ! "backend", > ! "super-user", > ! "user" > ! }; > ! > ! /* > ! * Used for pg_settings. Keep in sync with GucSource enum in guc.h > ! */ > ! static char *GucSourceName[] = > ! { > ! "default", > ! "environment variable", > ! "configuration file", > ! "command line", > ! "database", > ! "user", > ! "client", > ! "override", > ! "session" > }; > > /* Generic fields applicable to all types of variables */ > *************** > *** 2617,2639 **** > * Return GUC variable value by variable number; optionally return canonical > * form of name. Return value is palloc'd. > */ > ! char * > ! GetConfigOptionByNum(int varnum, const char **varname, bool *noshow) > { > ! struct config_generic *conf; > > /* check requested variable number valid */ > Assert((varnum >= 0) && (varnum < num_guc_variables)); > > conf = guc_variables[varnum]; > > - if (varname) > - *varname = conf->name; > - > if (noshow) > *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; > > ! return _ShowOption(conf); > } > > /* > --- 2657,2758 ---- > * Return GUC variable value by variable number; optionally return canonical > * form of name. Return value is palloc'd. > */ > ! void > ! GetConfigOptionByNum(int varnum, const char **values, bool *noshow) > { > ! char buffer[256]; > ! struct config_generic *conf; > > /* check requested variable number valid */ > Assert((varnum >= 0) && (varnum < num_guc_variables)); > > conf = guc_variables[varnum]; > > if (noshow) > *noshow = (conf->flags & GUC_NO_SHOW_ALL) ? true : false; > > ! /* first get the generic attributes */ > ! > ! /* name */ > ! values[0] = conf->name; > ! > ! /* setting : use _ShowOption in order to avoid duplicating the logic */ > ! values[1] = _ShowOption(conf); > ! > ! /* context */ > ! values[2] = GucContextName[conf->context]; > ! > ! /* vartype */ > ! values[3] = config_type_name[conf->vartype]; > ! > ! /* source */ > ! values[4] = GucSourceName[conf->source]; > ! > ! /* now get the type specifc attributes */ > ! switch (conf->vartype) > ! { > ! case PGC_BOOL: > ! { > ! /* min_val */ > ! values[5] = NULL; > ! > ! /* max_val */ > ! values[6] = NULL; > ! } > ! break; > ! > ! case PGC_INT: > ! { > ! struct config_int *lconf = (struct config_int *) conf; > ! > ! /* min_val */ > ! snprintf(buffer, sizeof(buffer), "%d", lconf->min); > ! values[5] = pstrdup(buffer); > ! > ! /* max_val */ > ! snprintf(buffer, sizeof(buffer), "%d", lconf->max); > ! values[6] = pstrdup(buffer); > ! } > ! break; > ! > ! case PGC_REAL: > ! { > ! struct config_real *lconf = (struct config_real *) conf; > ! > ! /* min_val */ > ! snprintf(buffer, sizeof(buffer), "%g", lconf->min); > ! values[5] = pstrdup(buffer); > ! > ! /* max_val */ > ! snprintf(buffer, sizeof(buffer), "%g", lconf->max); > ! values[6] = pstrdup(buffer); > ! } > ! break; > ! > ! case PGC_STRING: > ! { > ! /* min_val */ > ! values[5] = NULL; > ! > ! /* max_val */ > ! values[6] = NULL; > ! } > ! break; > ! > ! default: > ! { > ! /* > ! * should never get here, but in case we do, set 'em to NULL > ! */ > ! > ! /* min_val */ > ! values[5] = NULL; > ! > ! /* max_val */ > ! values[6] = NULL; > ! } > ! break; > ! } > } > > /* > *************** > *** 2673,2678 **** > --- 2792,2799 ---- > * show_all_settings - equiv to SHOW ALL command but implemented as > * a Table Function. > */ > + #define NUM_PG_SETTINGS_ATTS 7 > + > Datum > show_all_settings(PG_FUNCTION_ARGS) > { > *************** > *** 2696,2707 **** > */ > oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); > > ! /* need a tuple descriptor representing two TEXT columns */ > ! tupdesc = CreateTemplateTupleDesc(2, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", > TEXTOID, -1, 0, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", > TEXTOID, -1, 0, false); > > /* allocate a slot for a tuple with this tupdesc */ > slot = TupleDescGetSlot(tupdesc); > --- 2817,2841 ---- > */ > oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); > > ! /* > ! * need a tuple descriptor representing NUM_PG_SETTINGS_ATTS columns > ! * of the appropriate types > ! */ > ! tupdesc = CreateTemplateTupleDesc(NUM_PG_SETTINGS_ATTS, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name", > TEXTOID, -1, 0, false); > TupleDescInitEntry(tupdesc, (AttrNumber) 2, "setting", > TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 3, "context", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 4, "vartype", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 5, "source", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 6, "min_val", > + TEXTOID, -1, 0, false); > + TupleDescInitEntry(tupdesc, (AttrNumber) 7, "max_val", > + TEXTOID, -1, 0, false); > > /* allocate a slot for a tuple with this tupdesc */ > slot = TupleDescGetSlot(tupdesc); > *************** > *** 2732,2740 **** > > if (call_cntr < max_calls) /* do when there is more left to send */ > { > ! char *values[2]; > ! char *varname; > ! char *varval; > bool noshow; > HeapTuple tuple; > Datum result; > --- 2866,2872 ---- > > if (call_cntr < max_calls) /* do when there is more left to send */ > { > ! char *values[NUM_PG_SETTINGS_ATTS]; > bool noshow; > HeapTuple tuple; > Datum result; > *************** > *** 2744,2758 **** > */ > do > { > ! varval = GetConfigOptionByNum(call_cntr, > ! (const char **) &varname, > ! &noshow); > if (noshow) > { > - /* varval is a palloc'd copy, so free it */ > - if (varval != NULL) > - pfree(varval); > - > /* bump the counter and get the next config setting */ > call_cntr = ++funcctx->call_cntr; > > --- 2876,2884 ---- > */ > do > { > ! GetConfigOptionByNum(call_cntr, (const char **) values, &noshow); > if (noshow) > { > /* bump the counter and get the next config setting */ > call_cntr = ++funcctx->call_cntr; > > *************** > *** 2762,2784 **** > } > } while (noshow); > > - /* > - * Prepare a values array for storage in our slot. This should be > - * an array of C strings which will be processed later by the > - * appropriate "in" functions. > - */ > - values[0] = varname; > - values[1] = varval; > - > /* build a tuple */ > tuple = BuildTupleFromCStrings(attinmeta, values); > > /* make the tuple into a datum */ > result = TupleGetDatum(slot, tuple); > - > - /* Clean up */ > - if (varval != NULL) > - pfree(varval); > > SRF_RETURN_NEXT(funcctx, result); > } > --- 2888,2898 ---- > Index: src/bin/initdb/initdb.sh > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/bin/initdb/initdb.sh,v > retrieving revision 1.192 > diff -c -r1.192 initdb.sh > *** src/bin/initdb/initdb.sh 2 Jun 2003 19:00:29 -0000 1.192 > --- src/bin/initdb/initdb.sh 26 Jun 2003 03:56:23 -0000 > *************** > *** 971,977 **** > > CREATE VIEW pg_settings AS \ > SELECT * \ > ! FROM pg_show_all_settings() AS A(name text, setting text); > > CREATE RULE pg_settings_u AS \ > ON UPDATE TO pg_settings \ > --- 971,979 ---- > > CREATE VIEW pg_settings AS \ > SELECT * \ > ! FROM pg_show_all_settings() AS A \ > ! (name text, setting text, context text, vartype text, \ > ! source text, min_val text, max_val text); > > CREATE RULE pg_settings_u AS \ > ON UPDATE TO pg_settings \ > Index: src/include/utils/guc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc.h,v > retrieving revision 1.32 > diff -c -r1.32 guc.h > *** src/include/utils/guc.h 11 Jun 2003 18:01:14 -0000 1.32 > --- src/include/utils/guc.h 26 Jun 2003 02:58:54 -0000 > *************** > *** 49,63 **** > * we don't yet know if the user is a superuser. > * > * USERSET options can be set by anyone any time. > */ > typedef enum > { > ! PGC_INTERNAL, > ! PGC_POSTMASTER, > ! PGC_SIGHUP, > ! PGC_BACKEND, > ! PGC_SUSET, > ! PGC_USERSET > } GucContext; > > /* > --- 49,65 ---- > * we don't yet know if the user is a superuser. > * > * USERSET options can be set by anyone any time. > + * > + * Keep in sync with GucContextName in guc.c > */ > typedef enum > { > ! PGC_INTERNAL = 0, > ! PGC_POSTMASTER = 1, > ! PGC_SIGHUP = 2, > ! PGC_BACKEND = 3, > ! PGC_SUSET = 4, > ! PGC_USERSET = 5 > } GucContext; > > /* > *************** > *** 69,74 **** > --- 71,78 ---- > * Sources <= PGC_S_OVERRIDE will set the default used by RESET, as well > * as the current value. Note that source == PGC_S_OVERRIDE should be > * used when setting a PGC_INTERNAL option. > + * > + * Keep in sync with GucSourceName in guc.c > */ > typedef enum > { > *************** > *** 83,89 **** > PGC_S_SESSION = 8 /* SET command */ > } GucSource; > > - > /* GUC vars that are actually declared in guc.c, rather than elsewhere */ > extern bool log_statement; > extern bool log_duration; > --- 87,92 ---- > *************** > *** 123,129 **** > extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); > extern void ShowAllGUCConfig(DestReceiver *dest); > extern char *GetConfigOptionByName(const char *name, const char **varname); > ! extern char *GetConfigOptionByNum(int varnum, const char **varname, bool *noshow); > extern int GetNumConfigOptions(void); > > extern void SetPGVariable(const char *name, List *args, bool is_local); > --- 126,132 ---- > extern void ShowGUCConfigOption(const char *name, DestReceiver *dest); > extern void ShowAllGUCConfig(DestReceiver *dest); > extern char *GetConfigOptionByName(const char *name, const char **varname); > ! extern void GetConfigOptionByNum(int varnum, const char **values, bool *noshow); > extern int GetNumConfigOptions(void); > > extern void SetPGVariable(const char *name, List *args, bool is_local); > Index: src/test/regress/expected/rangefuncs.out > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rangefuncs.out,v > retrieving revision 1.7 > diff -c -r1.7 rangefuncs.out > *** src/test/regress/expected/rangefuncs.out 13 Feb 2003 20:45:22 -0000 1.7 > --- src/test/regress/expected/rangefuncs.out 26 Jun 2003 02:58:54 -0000 > *************** > *** 1,4 **** > ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; > name | setting > ------------------+--------- > enable_hashagg | on > --- 1,4 ---- > ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; > name | setting > ------------------+--------- > enable_hashagg | on > Index: src/test/regress/expected/rules.out > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/test/regress/expected/rules.out,v > retrieving revision 1.75 > diff -c -r1.75 rules.out > *** src/test/regress/expected/rules.out 26 May 2003 00:11:28 -0000 1.75 > --- src/test/regress/expected/rules.out 26 Jun 2003 04:40:11 -0000 > *************** > *** 1273,1279 **** > pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); > pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); > pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespacen ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); > ! pg_settings | SELECT a.name, a.setting FROM pg_show_all_settings() a(name text, setting text); > pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); > pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relnameAS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); > pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) ASseq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; > --- 1273,1279 ---- > pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, pg_get_indexdef(i.oid)AS indexdef FROM (((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid= x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind= 'i'::"char")); > pg_locks | SELECT l.relation, l."database", l."transaction", l.pid, l."mode", l.granted FROM pg_lock_status()l(relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean); > pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespacen ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); > ! pg_settings | SELECT a.name, a.setting, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings()a(name text, setting text, context text, vartype text, source text, min_val text, max_val text); > pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid)AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start FROM pg_database d, (SELECT pg_stat_get_backend_idset()AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid)= u.usesysid)); > pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relnameAS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char"); > pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) ASseq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan, sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'r'::"char")GROUP BY c.oid, n.nspname, c.relname; > Index: src/test/regress/sql/rangefuncs.sql > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/test/regress/sql/rangefuncs.sql,v > retrieving revision 1.4 > diff -c -r1.4 rangefuncs.sql > *** src/test/regress/sql/rangefuncs.sql 30 Aug 2002 19:56:49 -0000 1.4 > --- src/test/regress/sql/rangefuncs.sql 26 Jun 2003 02:58:54 -0000 > *************** > *** 1,4 **** > ! SELECT * FROM pg_settings WHERE name LIKE 'enable%'; > > CREATE TABLE foo2(fooid int, f2 int); > INSERT INTO foo2 VALUES(1, 11); > --- 1,4 ---- > ! SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%'; > > CREATE TABLE foo2(fooid int, f2 int); > INSERT INTO foo2 VALUES(1, 11); > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote: > Patch applied. Thanks. > There was enough code drift since this patch, that a couple of bugs materialized. New items have been added to GucContext and GucSource enums, but of course they were not added to the corresponding GucContextName[] and GucSourceName[] arrays in the patch. Here's a new patch to fix the resulting bugs. Joe Index: src/backend/utils/misc/guc.c =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v retrieving revision 1.140 diff -c -r1.140 guc.c *** src/backend/utils/misc/guc.c 27 Jul 2003 04:35:53 -0000 1.140 --- src/backend/utils/misc/guc.c 27 Jul 2003 05:22:12 -0000 *************** *** 156,162 **** /* ! * Used for pg_settings. Keep in sync with config_type enum above */ static char *config_type_name[] = { --- 156,162 ---- /* ! * Used for pg_settings. Keep in sync with config_type enum in guc_tables.h */ static char *config_type_name[] = { *************** *** 176,181 **** --- 176,182 ---- "sighup", "backend", "super-user", + "userlimit", "user" }; *************** *** 188,193 **** --- 189,195 ---- "environment variable", "configuration file", "command line", + "userstart", "database", "user", "client", Index: src/include/utils/guc.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc.h,v retrieving revision 1.36 diff -c -r1.36 guc.h *** src/include/utils/guc.h 27 Jul 2003 04:35:54 -0000 1.36 --- src/include/utils/guc.h 27 Jul 2003 05:23:21 -0000 *************** *** 52,57 **** --- 52,59 ---- * non-super users. * * USERSET options can be set by anyone any time. + * + * Keep in sync with GucContextName in guc.c */ typedef enum { Index: src/include/utils/guc_tables.h =================================================================== RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc_tables.h,v retrieving revision 1.2 diff -c -r1.2 guc_tables.h *** src/include/utils/guc_tables.h 27 Jul 2003 04:35:54 -0000 1.2 --- src/include/utils/guc_tables.h 27 Jul 2003 05:23:51 -0000 *************** *** 58,63 **** --- 58,65 ---- /* * GUC supports these types of variables: + * + * Keep in sync with config_type_name in guc.c */ enum config_type {
Aizaz Ahmed <aahmed@redhat.com> writes: > looks like there's some duplication between this array and the > static const char *const GucContext_names[] array in > src/backend/utils/misc/help_config.c > Is there some way we could have them both use the same array? Good idea. Please send a patch that exports the guc.c array for use in the other file. I'd lean towards the lower-cased spellings, though I'm not strong about it. (I'd also not use a dash in "super-user".) > Also, as a side note, I don't think Tom is a big fan of using comments > to indicate what needs to be kept in sync with what, if I can take the > liberty to quote him the last time a situation like this arose: I still stand by that opinion. We've seen a couple failures now with respect to these arrays, but I think that comes from having people independently patching the same code with too long delay between patch submission and application. The comment wouldn't have prevented the error, because patch(1) can't read comments. regards, tom lane
On Sun, 2003-07-27 at 01:29, Joe Conway wrote: > Bruce Momjian wrote: > > Patch applied. Thanks. > /* > ! * Used for pg_settings. Keep in sync with config_type enum in guc_tables.h > */ > static char *config_type_name[] = > { > *************** > *** 176,181 **** > --- 176,182 ---- > "sighup", > "backend", > "super-user", > + "userlimit", > "user" > }; looks like there's some duplication between this array and the static const char *const GucContext_names[] array in src/backend/utils/misc/help_config.c Is there some way we could have them both use the same array? (it doesn't matter for help_config whether the names are upper- or lower-case.) Also, as a side note, I don't think Tom is a big fan of using comments to indicate what needs to be kept in sync with what, if I can take the liberty to quote him the last time a situation like this arose: Re: [PATCHES] fix for new SUSET GUC variables Mon, 14 Jul 2003 16:33:14 -0400 > We don't normally try to enumerate in comments all the places you'd > need to change when adding to an enum or other widely-used > definition. You're supposed to find them by searching the source code > for references to the existing values. Depending on comments for that > sort of thing is far too error-prone --- you can just about guarantee > that the comment will fail to track new uses. Thanks, Aizaz
Patch applied. Thanks. --------------------------------------------------------------------------- Joe Conway wrote: > Bruce Momjian wrote: > > Patch applied. Thanks. > > > > There was enough code drift since this patch, that a couple of bugs > materialized. > > New items have been added to GucContext and GucSource enums, but of > course they were not added to the corresponding GucContextName[] and > GucSourceName[] arrays in the patch. Here's a new patch to fix the > resulting bugs. > > Joe > > > Index: src/backend/utils/misc/guc.c > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v > retrieving revision 1.140 > diff -c -r1.140 guc.c > *** src/backend/utils/misc/guc.c 27 Jul 2003 04:35:53 -0000 1.140 > --- src/backend/utils/misc/guc.c 27 Jul 2003 05:22:12 -0000 > *************** > *** 156,162 **** > > > /* > ! * Used for pg_settings. Keep in sync with config_type enum above > */ > static char *config_type_name[] = > { > --- 156,162 ---- > > > /* > ! * Used for pg_settings. Keep in sync with config_type enum in guc_tables.h > */ > static char *config_type_name[] = > { > *************** > *** 176,181 **** > --- 176,182 ---- > "sighup", > "backend", > "super-user", > + "userlimit", > "user" > }; > > *************** > *** 188,193 **** > --- 189,195 ---- > "environment variable", > "configuration file", > "command line", > + "userstart", > "database", > "user", > "client", > Index: src/include/utils/guc.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc.h,v > retrieving revision 1.36 > diff -c -r1.36 guc.h > *** src/include/utils/guc.h 27 Jul 2003 04:35:54 -0000 1.36 > --- src/include/utils/guc.h 27 Jul 2003 05:23:21 -0000 > *************** > *** 52,57 **** > --- 52,59 ---- > * non-super users. > * > * USERSET options can be set by anyone any time. > + * > + * Keep in sync with GucContextName in guc.c > */ > typedef enum > { > Index: src/include/utils/guc_tables.h > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/src/include/utils/guc_tables.h,v > retrieving revision 1.2 > diff -c -r1.2 guc_tables.h > *** src/include/utils/guc_tables.h 27 Jul 2003 04:35:54 -0000 1.2 > --- src/include/utils/guc_tables.h 27 Jul 2003 05:23:51 -0000 > *************** > *** 58,63 **** > --- 58,65 ---- > > /* > * GUC supports these types of variables: > + * > + * Keep in sync with config_type_name in guc.c > */ > enum config_type > { -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Mon, 2003-07-28 at 10:57, Tom Lane wrote: > Good idea. Please send a patch that exports the guc.c array for use in > the other file. I'd lean towards the lower-cased spellings, though I'm > not strong about it. (I'd also not use a dash in "super-user".) I've attached the patch below. It modifies help_config.c to use the new GucContextName array from guc.c It also changes 'super-user' to 'superuser' I was waiting for Joe's earlier patch to be applied, this should be safe to apply now. Thanks, Aizaz Index: backend/utils/misc/guc.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v retrieving revision 1.142 diff -c -p -r1.142 guc.c *** backend/utils/misc/guc.c 28 Jul 2003 16:22:02 -0000 1.142 --- backend/utils/misc/guc.c 28 Jul 2003 18:01:38 -0000 *************** static char *config_type_name[] = *** 169,181 **** /* * Used for pg_settings. Keep in sync with GucContext enum in guc.h */ ! static char *GucContextName[] = { "internal", "postmaster", "sighup", "backend", ! "super-user", "userlimit", "user" }; --- 169,181 ---- /* * Used for pg_settings. Keep in sync with GucContext enum in guc.h */ ! char *GucContextName[] = { "internal", "postmaster", "sighup", "backend", ! "superuser", "userlimit", "user" }; Index: backend/utils/misc/help_config.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/help_config.c,v retrieving revision 1.2 diff -c -p -r1.2 help_config.c *** backend/utils/misc/help_config.c 9 Jul 2003 17:57:47 -0000 1.2 --- backend/utils/misc/help_config.c 28 Jul 2003 18:01:38 -0000 *************** static bool displayStruct(mixedStruct * *** 135,151 **** /* * This array contains the display names for each of the GucContexts available * - * Note: these strings are deliberately not localized. */ ! static const char *const GucContext_names[] = { ! "INTERNAL", ! "POSTMASTER", ! "SIGHUP", ! "BACKEND", ! "SUSET", ! "USERLIMIT", ! "USERSET" ! }; /* * Reads in the the command line options and sets the state of the program --- 135,142 ---- /* * This array contains the display names for each of the GucContexts available * */ ! extern char * GucContextName[]; /* * Reads in the the command line options and sets the state of the program *************** printGenericHead(struct config_generic s *** 406,412 **** { printf(gettext(GENERIC_FORMAT[outFormat]), structToPrint.name, ! GucContext_names[structToPrint.context], gettext(config_group_names[structToPrint.group])); } --- 397,403 ---- { printf(gettext(GENERIC_FORMAT[outFormat]), structToPrint.name, ! GucContextName[structToPrint.context], gettext(config_group_names[structToPrint.group])); }
Aizaz Ahmed <aahmed@redhat.com> writes: >> Good idea. Please send a patch that exports the guc.c array for use in >> the other file. I'd lean towards the lower-cased spellings, though I'm >> not strong about it. (I'd also not use a dash in "super-user".) > I've attached the patch below. It modifies help_config.c to use the new > GucContextName array from guc.c Applied, thanks. I also did some other cleanup, including sticking the enum constant names into the array declarations --- this is in service of the notion that grepping for the constants should help you find places you need to change when adding one. regards, tom lane