Re: [HACKERS] allowed user/db variables

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, "Patches (PostgreSQL)" <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [HACKERS] allowed user/db variables
Date: 2003-07-20 00:48:06
Message-ID: 200307200048.h6K0m6w00321@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches


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(at)joeconway(dot)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_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 AS 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_class i 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 JOIN pg_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_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 AS 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_class i 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 JOIN pg_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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2003-07-20 00:48:39 Re: [HACKERS] allowed user/db variables
Previous Message Bruce Momjian 2003-07-20 00:31:22 Re: Updating psql for features of new FE/BE protocol

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2003-07-20 00:48:39 Re: [HACKERS] allowed user/db variables
Previous Message Larry Rosenman 2003-07-20 00:01:43 Re: PG Patch [openserver followup]