show() function

Joe Conway <mail@joeconway.com> writes:

... a builtin
show('gucvarname') function returning a single scalar value has been
added, e.g.:

That name seems way too generic -- ie, likely to conflict with
existing user-defined functions. I'd prefer something like
show_variable; though perhaps Peter will have a better idea.

regards, tom lane

Tom Lane wrote:

That name seems way too generic -- ie, likely to conflict with
existing user-defined functions. I'd prefer something like
show_variable; though perhaps Peter will have a better idea.

I thought it was a good parallel to the current command, but I see your
point. I'll wait for a while to see if Peter has any suggestions, and
then resubmit.

Joe

Joe Conway writes:

Attached is a patch which (I believe) addresses Peter's concerns with
respect to the previous showguc patch. In this patch, contrib/showguc is
gone, the term GUC is not exposed to user space, and a builtin
show('gucvarname') function returning a single scalar value has been
added, e.g.:

I think this patch still includes a number of leftovers from previous
attempts, such as a get option by number function.

--
Peter Eisentraut peter_e@gmx.net

Joe Conway writes:

I thought it was a good parallel to the current command, but I see your
point. I'll wait for a while to see if Peter has any suggestions, and
then resubmit.

The only reason this function doesn't exist yet is that I've been avoiding
this very question.

My personal favorite these days was get_session_characteristic(), which is
reasonably SQL-related and doesn't overload any confusing terms such as
parameter, variable, or option. However, considering the new
transaction-only settings, this name would be incorrect. Again, stealing
from SQL, an alternative could be current_characteristic(), which you
couldn't really argue against except on the ground that it's pretty weird.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Joe Conway writes:

Attached is a patch which (I believe) addresses Peter's concerns with
respect to the previous showguc patch. In this patch, contrib/showguc is
gone, the term GUC is not exposed to user space, and a builtin
show('gucvarname') function returning a single scalar value has been
added, e.g.:

I think this patch still includes a number of leftovers from previous
attempts, such as a get option by number function.

I was hoping it would be acceptable to leave this function in (as well
as the one to get the number of config variables), once it was renamed
to remove the reference to GUC.

Without it, it is impossible for a user function to return a list of
config variables without prior knowledge of the config variable names. I
would still like to have this ability. The only workaround without these
functions is to parse "SHOW ALL" output which is an awfully ugly
alternative.

Joe

Peter Eisentraut wrote:

Joe Conway writes:

Attached is a patch which (I believe) addresses Peter's concerns with
respect to the previous showguc patch. In this patch, contrib/showguc is
gone, the term GUC is not exposed to user space, and a builtin
show('gucvarname') function returning a single scalar value has been
added, e.g.:

I think this patch still includes a number of leftovers from previous
attempts, such as a get option by number function.

I was hoping it would be acceptable to leave this function in (as well
as the one to get the number of config variables), once it was renamed
to remove the reference to GUC.

Without it, it is impossible for a user function to return a list of
config variables without prior knowledge of the config variable names. I
would still like to have this ability. The only workaround without these
functions is to parse "SHOW ALL" output which is an awfully ugly
alternative.

Joe

Peter Eisentraut wrote:

The only reason this function doesn't exist yet is that I've been avoiding
this very question.

My personal favorite these days was get_session_characteristic(), which is
reasonably SQL-related and doesn't overload any confusing terms such as
parameter, variable, or option. However, considering the new
transaction-only settings, this name would be incorrect. Again, stealing
from SQL, an alternative could be current_characteristic(), which you
couldn't really argue against except on the ground that it's pretty weird.

Some alternatives:

current_setting()
current_configuration()
current_behavior()

Do you like any of these better?

Joe

Joe Conway <mail@joeconway.com> writes:

I was hoping it would be acceptable to leave this function in (as well
as the one to get the number of config variables), once it was renamed
to remove the reference to GUC.

Without it, it is impossible for a user function to return a list of
config variables without prior knowledge of the config variable names. I
would still like to have this ability. The only workaround without these
functions is to parse "SHOW ALL" output which is an awfully ugly
alternative.

I agreed with your prior comments that making SHOW ALL return
query-style output isn't a complete solution --- we should do that,
but also the GUC variables should be exposed as a (read-only?) table
or function returning set to allow query-language manipulations of the
set. Unless someone's up for the pseudo-table implementation, a contrib
function returning set seems reasonable.

Also, I think Peter was objecting to exposing the name "GUC" at the
SQL function name level. I see no reason to avoid the phrase at the
C level; C code is going to be #including "utils/guc.h" anyway, so...

regards, tom lane

Tom Lane wrote:

I agreed with your prior comments that making SHOW ALL return
query-style output isn't a complete solution --- we should do that,
but also the GUC variables should be exposed as a (read-only?) table
or function returning set to allow query-language manipulations of the
set. Unless someone's up for the pseudo-table implementation, a contrib
function returning set seems reasonable.

I'm not sure I understand what you mean by a pseudo-table -- would a
table function wrapped in a system view (pg_settings?) be the same thing
as a pseudo-table?

Short of that, how's this for a plan:

1. New backend scalar function and guc.c/guc.h changes (as submitted
except the function name):
current_setting(text setting_name)
2. modify "SHOW X" to actually perform the equiv of:
select current_setting('X')
3. modify "SHOW ALL" to return query-style output ala EXPLAIN
4. submit contrib/showsettings, with a table function
current_settings(), which is a renamed version of the previously
submitted show_all_vars() function

Comments?

Joe

Joe Conway <mail@joeconway.com> writes:

Unless someone's up for the pseudo-table implementation, a contrib
function returning set seems reasonable.

I'm not sure I understand what you mean by a pseudo-table -- would a
table function wrapped in a system view (pg_settings?) be the same thing
as a pseudo-table?

I was actually alluding to the possibility of a *writable* table, eg

UPDATE pg_settings SET value = 'true' WHERE name = 'debug_print_query';

as a query-language equivalent of

SET debug_print_query = true;

I believe Oracle already manages some settings this way.

A read-only table is easy enough to make from an SRF, see the pg_stats
family of views for an example. I'm not sure how to get the
updatability part though ... and am happy to leave it for another day.

Short of that, how's this for a plan:

1. New backend scalar function and guc.c/guc.h changes (as submitted
except the function name):
current_setting(text setting_name)
2. modify "SHOW X" to actually perform the equiv of:
select current_setting('X')
3. modify "SHOW ALL" to return query-style output ala EXPLAIN
4. submit contrib/showsettings, with a table function
current_settings(), which is a renamed version of the previously
submitted show_all_vars() function

I think the exact SQL function names are still open to debate, but
otherwise seems like a plan.

regards, tom lane

Tom Lane writes:

I was actually alluding to the possibility of a *writable* table, eg

UPDATE pg_settings SET value = 'true' WHERE name = 'debug_print_query';

as a query-language equivalent of

SET debug_print_query = true;

I think this can be done with a stored procedure. Magic tables with
side-effects seem weird.

Is there anything fundamentally difficult with supporting "PROCEDURE
foo()" as equivalent with "FUNCTION foo() RETURNS opaque" and "CALL foo()"
as equivalent with "SELECT foo()" and throw away the result. Then you
could define a function analogous to SHOW and a procedure analogous to SET
and can manipulate the values using the full expression language.

--
Peter Eisentraut peter_e@gmx.net

Joe Conway writes:

I was hoping it would be acceptable to leave this function in (as well
as the one to get the number of config variables),

Why? I thought the SRF can have arbitrary termination actions, so we do
you need to count them in advance?

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

Is there anything fundamentally difficult with supporting "PROCEDURE
foo()" as equivalent with "FUNCTION foo() RETURNS opaque" and "CALL foo()"
as equivalent with "SELECT foo()" and throw away the result.

I'd like to see us *not* overload "opaque" with yet another meaning;
see past rants on subject. But as long as there was a distinguishable
representation of "returns void" in pg_proc, I'd see no problem with the
above.

plpgsql presently spells "CALL" as "PERFORM"; should we stick with that
precedent?

regards, tom lane

Peter Eisentraut wrote:

Joe Conway writes:

I was hoping it would be acceptable to leave this function in (as well
as the one to get the number of config variables),

Why? I thought the SRF can have arbitrary termination actions, so we do
you need to count them in advance?

Well, call it convenience, but num_guc_variables is used throughout
guc.c, so it seemed consistent and reliable to use as a termination action.

Joe

Joe Conway <mail@joeconway.com> writes:

Peter Eisentraut wrote:

Why? I thought the SRF can have arbitrary termination actions, so we do
you need to count them in advance?

Well, call it convenience, but num_guc_variables is used throughout
guc.c, so it seemed consistent and reliable to use as a termination action.

Just standing on the sidelines here, but the above looks like a failure
to communicate. I think Peter was asking a generic question: why should
the SRF support API include a count field? And Joe read it as something
very specific: whether the show_all_guc_variables function should employ
a count to decide when it's done.

My take is that the proposed SRF API includes a count field that the
function may use *if it wants to* to store a termination condition.
If not, fine: detect termination however you want. But the availability
of the field simplifies one common coding pattern, without creating any
noticeable overhead for functions that want to do it differently.

If that wasn't what either of you meant, I'll skulk away quietly...

regards, tom lane

Tom Lane wrote:

My take is that the proposed SRF API includes a count field that the
function may use *if it wants to* to store a termination condition.
If not, fine: detect termination however you want. But the availability
of the field simplifies one common coding pattern, without creating any
noticeable overhead for functions that want to do it differently.

If that wasn't what either of you meant, I'll skulk away quietly...

Right on target from my side ;-)

Joe

Tom Lane writes:

I'd like to see us *not* overload "opaque" with yet another meaning;
see past rants on subject. But as long as there was a distinguishable
representation of "returns void" in pg_proc, I'd see no problem with the
above.

I am aware of this concern. However, 0 is the most natural way to encode
"nothing" in PostgreSQL. Moreover, it would be desirable to be able to
declare trigger "routines" as procedures rather than opaque-returning
functions, so to preserve compatibility we'd have to make them equivalent.

To un-overload type OID 0, the unknown and C string types should be
changed to other numbers.

plpgsql presently spells "CALL" as "PERFORM"; should we stick with that
precedent?

I think not, because SQL99 says it's CALL (part 2, 15.1).

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

I'd like to see us *not* overload "opaque" with yet another meaning;
see past rants on subject. But as long as there was a distinguishable
representation of "returns void" in pg_proc, I'd see no problem with the
above.

I am aware of this concern. However, 0 is the most natural way to encode
"nothing" in PostgreSQL. Moreover, it would be desirable to be able to
declare trigger "routines" as procedures rather than opaque-returning
functions, so to preserve compatibility we'd have to make them equivalent.

Say what? Trigger routines do not return void ...

To un-overload type OID 0, the unknown and C string types should be
changed to other numbers.

Type OID 0 should only be used for "no type at all", as in the unused
slots of an oidvector, or the unused input-type column of a unary
operator. "Returns void" is a distinct concept, as is "returns tuple"
(or however you want to define the result of a trigger), as certainly
is C string. Unknown already has an OID.

I have speculated about inventing a notion of "pseudo types" (perhaps
marked by 'p' in typtype) that would be allowed as function input and/or
result types but not as column datatypes. Then we could create distinct
pseudotypes with distinct OIDs for each of these shades of meaning.

regards, tom lane

Tom Lane wrote:

Joe Conway <mail@joeconway.com> writes:
Short of that, how's this for a plan:
1. New backend scalar function and guc.c/guc.h changes (as submitted
except the function name):
current_setting(text setting_name)
2. modify "SHOW X" to actually perform the equiv of:
select current_setting('X')
3. modify "SHOW ALL" to return query-style output ala EXPLAIN
4. submit contrib/showsettings, with a table function
current_settings(), which is a renamed version of the previously
submitted show_all_vars() function

I think the exact SQL function names are still open to debate, but
otherwise seems like a plan.

The attached patch implements items 1, 2, and 3 above. I also modified
EXPLAIN to use new tuple projecting functions, based on the original
ones in explain.c.

Example:
test=# show debug_print_query;
debug_print_query
-------------------
off
(1 row)

test=# show all;
name | setting
-------------------------------+---------------------------------------
australian_timezones | off
authentication_timeout | 60
.
.
.
wal_files | 0
wal_sync_method | fdatasync
(96 rows)

Additionally I created a function called set_config_by_name() which
wraps set_config_option() as a SQL callable function. See below for a
discussion of why I did this.

Notes:
1. Please bump catversion.h. This patch requires initdb.

2. This patch includes the same Table Function API fixes that I
submitted on July 9:

http://archives.postgresql.org/pgsql-patches/2002-07/msg00056.php

Please disregard that one *if* this one is applied. If this one is
rejected please go ahead with the July 9th patch.

3. I also have a doc patch outstanding:

http://archives.postgresql.org/pgsql-patches/2002-07/msg00073.php

Any feedback on this?

I was actually alluding to the possibility of a *writable* table, eg

UPDATE pg_settings SET value = 'true' WHERE name =
'debug_print_query';

as a query-language equivalent of

SET debug_print_query = true;

I believe Oracle already manages some settings this way.

A read-only table is easy enough to make from an SRF, see the pg_stats
family of views for an example. I'm not sure how to get the
updatability part though ... and am happy to leave it for another day.

Using the show_all_vars() from contrib/showguc (which is *not* part of
the attached patch), and the new set_config_by_name(), I was able to
produce this effect using a VIEW and an UPDATE RULE. See the following:

test=# create view pg_settings as select varname as name, varval as
setting from show_all_vars();
CREATE VIEW
test=# create rule pg_settings_rule as on update to pg_settings do
instead select set_config(old.name, new.setting,'f');
CREATE RULE
test=# UPDATE pg_settings SET setting = 'true' WHERE name =
'debug_print_query';
set_config
------------
on
(1 row)

test=# show debug_print_query;
debug_print_query
-------------------
on
(1 row)

test=# UPDATE pg_settings SET setting = 'false' WHERE name =
'debug_print_query';
set_config
------------
off
(1 row)

test=# show debug_print_query;
debug_print_query
-------------------
off
(1 row)

Any interest in rolling show_all_vars(), perhaps renamed
show_all_settings() or something, into the backend and creating a
virtual table in this fashion?

Joe