show all;

Care to add RESET ALL?

As SHOW is for humans anyway, I hacked SHOW ALL that
shows all current session variables. More info with less
typing.

I have not looked if the doc changes look sane, because
I have not sgml tools on my machine ATM.

--
marko

Index: doc/src/sgml/ref/show.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/show.sgml,v
retrieving revision 1.11
diff -c -r1.11 show.sgml
*** doc/src/sgml/ref/show.sgml	2000/12/25 23:15:26	1.11
--- doc/src/sgml/ref/show.sgml	2001/06/01 19:58:59
***************
*** 16,21 ****
--- 16,24 ----
<synopsis>
SHOW <replaceable class="PARAMETER">name</replaceable>
</synopsis>
+   <synopsis>
+ SHOW ALL
+   </synopsis>

<refsect2 id="R2-SQL-SHOW-1">
<title>Inputs</title>
***************
*** 29,34 ****
--- 32,43 ----
The name of a run-time parameter. See
<xref linkend="sql-set" endterm="sql-set-title">
for a list.
+        </para>
+       </listitem>
+       <term>ALL</term>
+       <listitem>
+        <para>
+         Show all current session parameters.
</para>
</listitem>
</varlistentry>
Index: src/backend/commands/variable.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/variable.c,v
retrieving revision 1.48
diff -c -r1.48 variable.c
*** src/backend/commands/variable.c	2001/05/08 21:06:42	1.48
--- src/backend/commands/variable.c	2001/06/01 19:59:01
***************
*** 748,754 ****
show_server_encoding();
else if (strcasecmp(name, "seed") == 0)
show_random_seed();
! 	else
{
const char *val = GetConfigOption(name);

--- 748,763 ----
show_server_encoding();
else if (strcasecmp(name, "seed") == 0)
show_random_seed();
! 	else if (strcasecmp(name, "all") == 0)
! 	{
! 		show_date();
! 		show_timezone();
! 		show_DefaultXactIsoLevel();
! 		show_XactIsoLevel();
! 		show_client_encoding();
! 		show_server_encoding();
! 		show_random_seed();
! 	} else
{
const char *val = GetConfigOption(name);

Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.227
diff -c -r2.227 gram.y
*** src/backend/parser/gram.y	2001/05/27 09:59:29	2.227
--- src/backend/parser/gram.y	2001/06/01 19:59:14
***************
*** 860,865 ****
--- 860,871 ----
n->name  = "timezone";
$$ = (Node *) n;
}
+ 		| SHOW ALL
+ 				{
+ 					VariableShowStmt *n = makeNode(VariableShowStmt);
+ 					n->name  = "all";
+ 					$$ = (Node *) n;
+ 				}
| SHOW TRANSACTION ISOLATION LEVEL
{
VariableShowStmt *n = makeNode(VariableShowStmt);

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

As SHOW is for humans anyway, I hacked SHOW ALL that
shows all current session variables. More info with less
typing.

I have not looked if the doc changes look sane, because
I have not sgml tools on my machine ATM.

--
marko

Index: doc/src/sgml/ref/show.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/show.sgml,v
retrieving revision 1.11
diff -c -r1.11 show.sgml
*** doc/src/sgml/ref/show.sgml	2000/12/25 23:15:26	1.11
--- doc/src/sgml/ref/show.sgml	2001/06/01 19:58:59
***************
*** 16,21 ****
--- 16,24 ----
<synopsis>
SHOW <replaceable class="PARAMETER">name</replaceable>
</synopsis>
+   <synopsis>
+ SHOW ALL
+   </synopsis>

<refsect2 id="R2-SQL-SHOW-1">
<title>Inputs</title>
***************
*** 29,34 ****
--- 32,43 ----
The name of a run-time parameter. See
<xref linkend="sql-set" endterm="sql-set-title">
for a list.
+        </para>
+       </listitem>
+       <term>ALL</term>
+       <listitem>
+        <para>
+         Show all current session parameters.
</para>
</listitem>
</varlistentry>
Index: src/backend/commands/variable.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/variable.c,v
retrieving revision 1.48
diff -c -r1.48 variable.c
*** src/backend/commands/variable.c	2001/05/08 21:06:42	1.48
--- src/backend/commands/variable.c	2001/06/01 19:59:01
***************
*** 748,754 ****
show_server_encoding();
else if (strcasecmp(name, "seed") == 0)
show_random_seed();
! 	else
{
const char *val = GetConfigOption(name);

--- 748,763 ----
show_server_encoding();
else if (strcasecmp(name, "seed") == 0)
show_random_seed();
! 	else if (strcasecmp(name, "all") == 0)
! 	{
! 		show_date();
! 		show_timezone();
! 		show_DefaultXactIsoLevel();
! 		show_XactIsoLevel();
! 		show_client_encoding();
! 		show_server_encoding();
! 		show_random_seed();
! 	} else
{
const char *val = GetConfigOption(name);

Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.227
diff -c -r2.227 gram.y
*** src/backend/parser/gram.y	2001/05/27 09:59:29	2.227
--- src/backend/parser/gram.y	2001/06/01 19:59:14
***************
*** 860,865 ****
--- 860,871 ----
n->name  = "timezone";
$$ = (Node *) n;
}
+ 		| SHOW ALL
+ 				{
+ 					VariableShowStmt *n = makeNode(VariableShowStmt);
+ 					n->name  = "all";
+ 					$$ = (Node *) n;
+ 				}
| SHOW TRANSACTION ISOLATION LEVEL
{
VariableShowStmt *n = makeNode(VariableShowStmt);

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Marko Kreen <marko@l-t.ee> writes:

As SHOW is for humans anyway, I hacked SHOW ALL that
shows all current session variables.

That's not even *close* to being all the SET variables.
You forgot GUC.

regards, tom lane

Marko Kreen writes:

As SHOW is for humans anyway, I hacked SHOW ALL that
shows all current session variables. More info with less
typing.

There are a lot more session variables than your patch shows.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Sorry, rejected. I thought that list looked a little small.

As SHOW is for humans anyway, I hacked SHOW ALL that
shows all current session variables. More info with less
typing.

I have not looked if the doc changes look sane, because
I have not sgml tools on my machine ATM.

--
marko

Index: doc/src/sgml/ref/show.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/ref/show.sgml,v
retrieving revision 1.11
diff -c -r1.11 show.sgml
*** doc/src/sgml/ref/show.sgml	2000/12/25 23:15:26	1.11
--- doc/src/sgml/ref/show.sgml	2001/06/01 19:58:59
***************
*** 16,21 ****
--- 16,24 ----
<synopsis>
SHOW <replaceable class="PARAMETER">name</replaceable>
</synopsis>
+   <synopsis>
+ SHOW ALL
+   </synopsis>

<refsect2 id="R2-SQL-SHOW-1">
<title>Inputs</title>
***************
*** 29,34 ****
--- 32,43 ----
The name of a run-time parameter. See
<xref linkend="sql-set" endterm="sql-set-title">
for a list.
+        </para>
+       </listitem>
+       <term>ALL</term>
+       <listitem>
+        <para>
+         Show all current session parameters.
</para>
</listitem>
</varlistentry>
Index: src/backend/commands/variable.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/commands/variable.c,v
retrieving revision 1.48
diff -c -r1.48 variable.c
*** src/backend/commands/variable.c	2001/05/08 21:06:42	1.48
--- src/backend/commands/variable.c	2001/06/01 19:59:01
***************
*** 748,754 ****
show_server_encoding();
else if (strcasecmp(name, "seed") == 0)
show_random_seed();
! 	else
{
const char *val = GetConfigOption(name);

--- 748,763 ----
show_server_encoding();
else if (strcasecmp(name, "seed") == 0)
show_random_seed();
! 	else if (strcasecmp(name, "all") == 0)
! 	{
! 		show_date();
! 		show_timezone();
! 		show_DefaultXactIsoLevel();
! 		show_XactIsoLevel();
! 		show_client_encoding();
! 		show_server_encoding();
! 		show_random_seed();
! 	} else
{
const char *val = GetConfigOption(name);

Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.227
diff -c -r2.227 gram.y
*** src/backend/parser/gram.y	2001/05/27 09:59:29	2.227
--- src/backend/parser/gram.y	2001/06/01 19:59:14
***************
*** 860,865 ****
--- 860,871 ----
n->name  = "timezone";
$$ = (Node *) n;
}
+ 		| SHOW ALL
+ 				{
+ 					VariableShowStmt *n = makeNode(VariableShowStmt);
+ 					n->name  = "all";
+ 					$$ = (Node *) n;
+ 				}
| SHOW TRANSACTION ISOLATION LEVEL
{
VariableShowStmt *n = makeNode(VariableShowStmt);

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Fri, Jun 01, 2001 at 05:01:11PM -0400, Tom Lane wrote:

Marko Kreen <marko@l-t.ee> writes:

As SHOW is for humans anyway, I hacked SHOW ALL that
shows all current session variables.

That's not even *close* to being all the SET variables.
You forgot GUC.

Actually not. But I thought the GUC should be under separate
command, like 'show config'. The SHOW ALL should show
all SET-able vars. I understand the GUC ones are RO.
Am I mistaken?

I can ofcourse make SHOW ALL show GUC ones too, but then
(I think) somehow it would be nice to get only the SETable ones.
Or is this too hairy? Ofcourse when some of the GUC ones are
SETable, this wont matter.

--
marko

On Fri, Jun 01, 2001 at 05:35:04PM -0400, Bruce Momjian wrote:

Sorry, rejected. I thought that list looked a little small.

Yeah, but I got the idea accepted ;) Now what exactly should
SHOW ALL show ? GUC + non-GUC?

Another thing: currently the var display goes through
elog(NOTICE) and is quite verbose. Is it possible to
return those as a list of tuples? (name, value)
So they would be easier to access from client code too?

--
marko

Marko Kreen <marko@l-t.ee> writes:

That's not even *close* to being all the SET variables.
You forgot GUC.

Actually not. But I thought the GUC should be under separate
command, like 'show config'. The SHOW ALL should show
all SET-able vars. I understand the GUC ones are RO.
Am I mistaken?

Yes, you are. GUC supports several different levels of settability,
none of which are truly "read only". In any case ISTM that "SHOW ALL"
should show *everything*. I suppose there might be a use for a "SHOW
SETTABLE", or something like that, that would only show the variables
you currently have permission to change.

Actually, the variables that are still special-cased in variable.c
should all be folded into GUC, IMHO: there shouldn't be anything that
Get/SetPGVariable know about but Get/SetConfigOption don't. The
original version of GUC couldn't handle this, but now that there is an
assign_hook for GUC variables I think it could be done.

regards, tom lane

Marko Kreen <marko@l-t.ee> writes:

Another thing: currently the var display goes through
elog(NOTICE) and is quite verbose. Is it possible to
return those as a list of tuples? (name, value)

This would be a good idea, but I don't think there's any non-kluge
way to do it at the moment.

The right way to do it would be as a function returning a set of tuples,
which you might then bury inside a VIEW for syntactic convenience.
While that's certainly possible (SQL functions do it), it's a pain in
the neck to code and the interface is likely to change drastically
sometime soon. So I'd recommend holding that idea on the to-do list
until we do something about the function-returning-tuples problem.

regards, tom lane

On Fri, Jun 01, 2001 at 04:18:54PM -0400, Bruce Momjian wrote:

Care to add RESET ALL?

What exactly should it do? We have:

1) Compiled in default
2) postgres.conf / command line provided
3) through SET modified

I guess it should reset to 2). But is it possible to differ
2) and 3) values?

Or should it reset only some subset of vars (eg currenlty
variable.c special-cased?) What variables should belog to
that subset?

--
marko

On Fri, Jun 01, 2001 at 04:18:54PM -0400, Bruce Momjian wrote:

Care to add RESET ALL?

What exactly should it do? We have:

1) Compiled in default
2) postgres.conf / command line provided
3) through SET modified

I guess it should reset to 2). But is it possible to differ
2) and 3) values?

Or should it reset only some subset of vars (eg currenlty
variable.c special-cased?) What variables should belog to
that subset?

Good question and this brings up issues with SHOW. Let's look at:

SET ALL
SHOW ALL
RESET ALL

The first makes no sense. The third is needed for persistent
connections. Should RESET ALL also rollback any open transaction? If
not, we have to keep our 'BEGIN;COMMIT;' hack in PHP to reset the
transaction state. Do we eat an XID when we do that?

If we have SHOW ALL show things that are not SET-able, is it clear what
RESET ALL does? Can I recommend SHOW ALL output two groups, one
SET-able, and the rest non-SET-able. When I say SET-able, I mean
set-able by SET, not postgresql.conf.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Actually, the variables that are still special-cased in variable.c
should all be folded into GUC, IMHO: there shouldn't be anything that
Get/SetPGVariable know about but Get/SetConfigOption don't. The
original version of GUC couldn't handle this, but now that there is an
assign_hook for GUC variables I think it could be done.

Is some of the GUC stuff better not changed while running a backend or
are you talking about something else?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Fri, Jun 01, 2001 at 06:35:16PM -0400, Bruce Momjian wrote:

On Fri, Jun 01, 2001 at 04:18:54PM -0400, Bruce Momjian wrote:

Care to add RESET ALL?

What exactly should it do? We have:

1) Compiled in default
2) postgres.conf / command line provided
3) through SET modified

I guess it should reset to 2). But is it possible to differ
2) and 3) values?

Or should it reset only some subset of vars (eg currenlty
variable.c special-cased?) What variables should belog to
that subset?

Good question and this brings up issues with SHOW. Let's look at:

SET ALL
SHOW ALL
RESET ALL

The first makes no sense. The third is needed for persistent
connections. Should RESET ALL also rollback any open transaction? If
not, we have to keep our 'BEGIN;COMMIT;' hack in PHP to reset the
transaction state. Do we eat an XID when we do that?

If we have SHOW ALL show things that are not SET-able, is it clear what
RESET ALL does? Can I recommend SHOW ALL output two groups, one
SET-able, and the rest non-SET-able. When I say SET-able, I mean
set-able by SET, not postgresql.conf.

As I understand Tom, all vars should go through GUC and some of
the current .conf vars will be SET-able. So RESET ALL would
be quite weird.

How about command 'RESET;' that resets some fixed vars so you
get some quaranteed sane state on general SQL level and leaves
the really magic vars alone? Eg.

1) rolls TX back, so you are not in any TX
2) resets TRANSACTION ISOLATION LEVEL

What else?

--
marko

Marko Kreen <marko@l-t.ee> writes:

As I understand Tom, all vars should go through GUC and some of
the current .conf vars will be SET-able. So RESET ALL would
be quite weird.

RESET ALL should re-establish the same state of the conf vars that you'd
get with a freshly started backend. This doesn't seem weird or even in
need of discussion ...

1) rolls TX back, so you are not in any TX

This I do not agree with; it ties together two concepts that are better
kept separate. (If SET were under transaction control, my opinion might
be different ... but it isn't.) There is nothing wrong with sending a
ROLLBACK if you want to be sure you are not in a transaction block.
Further, there could be situations where you want to RESET the conf vars
without forcing a rollback.

regards, tom lane

1) rolls TX back, so you are not in any TX

This I do not agree with; it ties together two concepts that are better
kept separate. (If SET were under transaction control, my opinion might
be different ... but it isn't.) There is nothing wrong with sending a
ROLLBACK if you want to be sure you are not in a transaction block.
Further, there could be situations where you want to RESET the conf vars
without forcing a rollback.

I agree it links two unrelated items.

The problem with ROLLBACK is that it throws an error into the log if
there is not transaction. This is for persistent connections reset.
How about ROLLBACK QUIET?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The problem with ROLLBACK is that it throws an error into the log if

Error? I see only a notice.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The problem with ROLLBACK is that it throws an error into the log if

Error? I see only a notice.

The issue is that it fills the logs if used a lot:

DEBUG: redo is not required
DEBUG: database system is in production state
NOTICE: ROLLBACK: no transaction in progress

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The issue is that it fills the logs if used a lot:

Well, that's a generic problem with all our "helpful" NOTICEs.
ROLLBACK is far from being the only sinner. ISTM if you don't
like log bloat, the answer is to attack that head-on, not invent
yet another nonstandard spelling for ROLLBACK.

Some ideas to think about:

1. Invent SET variables to control the minimum elog severity level
that gets into the logs or sent to the client, rather than hard-wiring
these levels at DEBUG and NOTICE respectively.

2. Divide existing NOTICEs into more than one severity level. We have
a lot of messages like 'CREATE TABLE will create a sequence' that might
be helpful for development but are just plain nuisances in production
applications. It would be nice to turn off the "picky" notices at will.
#1 would take care of this if they had a lower severity level than other
notices.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The issue is that it fills the logs if used a lot:

Well, that's a generic problem with all our "helpful" NOTICEs.
ROLLBACK is far from being the only sinner. ISTM if you don't
like log bloat, the answer is to attack that head-on, not invent
yet another nonstandard spelling for ROLLBACK.

Some ideas to think about:

1. Invent SET variables to control the minimum elog severity level
that gets into the logs or sent to the client, rather than hard-wiring
these levels at DEBUG and NOTICE respectively.

2. Divide existing NOTICEs into more than one severity level. We have
a lot of messages like 'CREATE TABLE will create a sequence' that might
be helpful for development but are just plain nuisances in production
applications. It would be nice to turn off the "picky" notices at will.
#1 would take care of this if they had a lower severity level than other
notices.

The problem is that they don't want to change the log levels. They are
using BEGIN;COMMIT; as an automatic thing in the PHP interface to close
any open transaction before passing the persistent connection to another
user. This is a special case.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026