user defined settings (aka user defined guc variables)

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

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

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

----- Original Message -----
From: "Gavin Sherry" <swm(at)linuxworld(dot)com(dot)au>
To: "Joe Conway" <mail(at)joeconway(dot)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(at)mascari(dot)com

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

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

Joe Conway <mail(at)joeconway(dot)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

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)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

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(at)gmx(dot)net