Thread: 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 loadablemodules 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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
----- 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
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@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
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
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