I've been playing around with making it possible to create user defined guc 
variables. This has been discussed, at least in passing, before. And it is 
even anticipated in guc.c as a possible future feature:
/*
  * Build the sorted array.	This is split out so that it could be
  * re-executed after startup (eg, we could allow loadable modules to
  * add vars, and then we'd need to re-sort).
  */

It is a feature that would be nice to have, so that, for example, a user 
defined variable named "my_classpath" could be created to point to the java 
CLASSPATH needed by a custom C function.

So far I have this much working:
- A new backend function, pg_create_user_setting(name, value, islocal) is used
   to "register" the setting.
- SHOW ALL, SHOW, current_setting(), and pg_show_all_settings()) will display
   it just like any other setting
- Similarly, SET and set_config() will change it.

I still need to make the user defined settings survive being saved by ALTER 
USER or ALTER DATABASE. I'm also thinking about a corresponding grammar 
addition, something along the lines of:

   CREATE SETTING name WITH VALUE value;

This would effectively perform:
   SELECT pg_create_user_setting(name, value, false);

I'm wondering whether it would be "a good thing" or "a bad thing" to have 
unrecognized settings found in postgresql.conf be registered as user defined 
settings?

Any comments, concerns, or objections?

Thanks,

Joe


Wed, 18 Dec 2002 17:00:19 -0800
Joe Conway <mail@joeconway.com>
user defined settings (aka user defined guc variables)
On Wed, 18 Dec 2002, Joe Conway wrote:

> I've been playing around with making it possible to create user defined guc 
> variables. This has been discussed, at least in passing, before. And it is 
> even anticipated in guc.c as a possible future feature:
> /*
>   * Build the sorted array.	This is split out so that it could be
>   * re-executed after startup (eg, we could allow loadable modules to
>   * add vars, and then we'd need to re-sort).
>   */
> 
> It is a feature that would be nice to have, so that, for example, a user 
> defined variable named "my_classpath" could be created to point to the java 
> CLASSPATH needed by a custom C function.

Hmm. Is GUC really the best place for something like that? (not that there
is any other place :-)).

Gavin


Thu, 19 Dec 2002 12:24:00 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: user defined settings (aka user defined guc variables)

What exactly can you do with these variables other than SHOW/SET.  Seems
it would be nice if they could be used in queries, like in a special
table like sysvar:

	SELECT sysvar.fsync;

---------------------------------------------------------------------------

Joe Conway wrote:
> I've been playing around with making it possible to create user defined guc 
> variables. This has been discussed, at least in passing, before. And it is 
> even anticipated in guc.c as a possible future feature:
> /*
>   * Build the sorted array.	This is split out so that it could be
>   * re-executed after startup (eg, we could allow loadable modules to
>   * add vars, and then we'd need to re-sort).
>   */
> 
> It is a feature that would be nice to have, so that, for example, a user 
> defined variable named "my_classpath" could be created to point to the java 
> CLASSPATH needed by a custom C function.
> 
> So far I have this much working:
> - A new backend function, pg_create_user_setting(name, value, islocal) is used
>    to "register" the setting.
> - SHOW ALL, SHOW, current_setting(), and pg_show_all_settings()) will display
>    it just like any other setting
> - Similarly, SET and set_config() will change it.
> 
> I still need to make the user defined settings survive being saved by ALTER 
> USER or ALTER DATABASE. I'm also thinking about a corresponding grammar 
> addition, something along the lines of:
> 
>    CREATE SETTING name WITH VALUE value;
> 
> This would effectively perform:
>    SELECT pg_create_user_setting(name, value, false);
> 
> I'm wondering whether it would be "a good thing" or "a bad thing" to have 
> unrecognized settings found in postgresql.conf be registered as user defined 
> settings?
> 
> Any comments, concerns, or objections?
> 
> Thanks,
> 
> Joe
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  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

Wed, 18 Dec 2002 20:26:06 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: user defined settings (aka user defined guc variables)
----- Original Message ----- 
From: "Gavin Sherry" <swm@linuxworld.com.au>
To: "Joe Conway" <mail@joeconway.com>


> On Wed, 18 Dec 2002, Joe Conway wrote:
> 
> > I've been playing around with making it possible to create user defined guc 
> > variables. This has been discussed, at least in passing, before. And it is 
> > even anticipated in guc.c as a possible future feature:
> > /*
> >   * Build the sorted array. This is split out so that it could be
> >   * re-executed after startup (eg, we could allow loadable modules to
> >   * add vars, and then we'd need to re-sort).
> >   */
> > 
> > It is a feature that would be nice to have, so that, for example, a user 
> > defined variable named "my_classpath" could be created to point to the java 
> > CLASSPATH needed by a custom C function.
> 
> Hmm. Is GUC really the best place for something like that? (not that there
> is any other place :-)).
> 
> Gavin

Maybe GUC should be stored in a Berkeley DB? ;-)

Mike Mascari
mascarm@mascari.com




Wed, 18 Dec 2002 20:28:15 -0500
"Mike Mascari" <mascarm@mascari.com>
Re: user defined settings (aka user defined guc variables)
On Wed, 18 Dec 2002, Bruce Momjian wrote:

> 
> What exactly can you do with these variables other than SHOW/SET.  Seems
> it would be nice if they could be used in queries, like in a special
> table like sysvar:
> 
> 	SELECT sysvar.fsync;

Isn't that just identical to having a table?

Gavin


Thu, 19 Dec 2002 12:36:09 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: user defined settings (aka user defined guc variables)
Gavin Sherry wrote:
> On Wed, 18 Dec 2002, Bruce Momjian wrote:
> 
> 
>>What exactly can you do with these variables other than SHOW/SET.  Seems
>>it would be nice if they could be used in queries, like in a special
>>table like sysvar:
>>
>>	SELECT sysvar.fsync;
> 
> 
> Isn't that just identical to having a table?

Well you can use it in a query:

regression=# select pg_create_user_setting('myvar',17,false);
  pg_create_user_setting
------------------------
  17
(1 row)

regression=# select typname from pg_type where oid = current_setting('myvar');
  typname
---------
  bytea
(1 row)

There are at least two differences to this approach vs a table:

1. Main reason is that if a user defined function/contrib module creates a 
table in my database I consider that too intrusive. I'd prefer not to have 
metadata tables in my database simply to support a loaded function.

2. It's faster. In some simple tests, I found that getting a setting value via 
current_setting('myvar') is about 40% faster than getting a value from a one 
row table, and about 100% faster than an indexed lookup in a larger table 
(after the table is cached, more than that on the first lookup).

Joe


Wed, 18 Dec 2002 18:02:56 -0800
Joe Conway <mail@joeconway.com>
Re: user defined settings (aka user defined guc variables)
Joe Conway <mail@joeconway.com> writes:
> I've been playing around with making it possible to create user defined guc 
> variables. This has been discussed, at least in passing, before. And it is 
> even anticipated in guc.c as a possible future feature:

It's fairly clear how the mechanisms for this would work.  What's less
clear to me is what's the point?  I do not see any reason to have a GUC
variable that isn't defined and used by some chunk of low-level C code.

			regards, tom lane

Thu, 19 Dec 2002 00:12:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: user defined settings (aka user defined guc variables) 
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>I've been playing around with making it possible to create user defined guc 
>>variables. This has been discussed, at least in passing, before. And it is 
>>even anticipated in guc.c as a possible future feature:
> 
> It's fairly clear how the mechanisms for this would work.  What's less
> clear to me is what's the point?  I do not see any reason to have a GUC
> variable that isn't defined and used by some chunk of low-level C code.

Well, the java example (CLASSPATH) I gave is one instance. The value could be 
accessed by C code in a user defined function.

Another example is an  application I have at work. It is designed as a single 
app that is configured and used in multiple physical locations. Eventually all 
the data are collected in one central instance of the app, and therefore each 
instance needs its own guid that is stamped on every record in the database. 
Currently we store the guid in a single row table that gets populated by the 
install script.   Could we continue to do it this way -- sure. But it seems 
like a natural place to use a configuration setting.

Another example I thought about was session information for a web app. Lots of 
people use fairly small tables with a high churn rate to do this. A user 
defined setting could be used to store these like persistent cookies without 
building up large numbers of dead tuples that need vacuuming all the time.

I know all of these can be done in other ways. If there's no interest, I'll 
accept that this was a dumb idea and move on ;-)

Joe


Wed, 18 Dec 2002 21:39:39 -0800
Joe Conway <mail@joeconway.com>
Re: user defined settings (aka user defined guc variables)
Joe Conway writes:

> I'm wondering whether it would be "a good thing" or "a bad thing" to have
> unrecognized settings found in postgresql.conf be registered as user defined
> settings?

Certainly bad, since all error checking would go away.  This is one of the
main reasons why registering settings at run-time won't work.  If you need
to store data, use tables.

-- 
Peter Eisentraut   peter_e@gmx.net


Fri, 20 Dec 2002 00:45:00 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: user defined settings (aka user defined guc variables)