Thread: Updating psql for features of new FE/BE protocol
There are a number of things that need to be done in psql before feature
freeze. Any comments on the following points?
* We need a client-side autocommit-off implementation to substitute for
the one removed from the server. I am inclined to create a new psql
backslash command:\autocommit on traditional handling of transactions\autocommit off force BEGIN before
anyuser command that's not already in a transaction\autocommit with no arg, show current state
An alternative to creating a new command is to define a special variable
in psql, whereupon the above three would instead be rendered\set AUTOCOMMIT on\set AUTOCOMMIT off\echo :AUTOCOMMIT
The first choice seems less verbose to me, but if anyone wants to make a
case for the second, I'm open to it. Note that either of these could be
put in ~/.psqlrc if someone wants autocommit off as their default.
* Since libpq now keeps track of transaction state, it would be a simple
matter to add a prompt-string % construct to show something that indicates
the state (with possible values "idle", "in transaction", "in failed
transaction"). Any thoughts about exactly what this ought to look like?
I prototyped it with code that showed 'I', 'T', or 'E' but I suspect that
non-alphabetic characters would be better, since they wouldn't look like
part of a database name or other things you might put in the prompt.
BTW, both of the above features will work against pre-7.4 servers, with
the exception that a 7.3 server running with server-side autocommit off
will confuse libpq's tracking of transaction state. Not sure how
important that really is, given that we don't recommend running psql
against servers of different versions.
* I plan to get rid of psql's startup-time query to find out if you are
superuser, and instead let the '#' vs '>' prompt be driven through another
ParameterStatus variable, per a proposal I made awhile ago. (If anyone
can propose a better name for the GUC variable than "am_superuser", let's
hear it.) If I remove the startup query entirely, then when talking to
pre-7.4 servers the prompt would always show '>'. It'd be possible to
continue to issue the query against pre-7.4 servers only, but is it worth
the trouble? Again, we don't go to much trouble to make psql work 100%
with old servers.
* We can get rid of psql's LO_TRANSACTION variable, or at least make the
behavior more robust, now that psql can see the current transaction state.
Two cases are pretty obvious: if idle, then start and end our own
transaction around the LO operation. If in a transaction, do not issue
BEGIN or COMMIT, but just use that transaction. The third case is what
to do if in a failed transaction. You could argue that the LO operation
should be allowed to fail as well. Alternatively, we could roll back the
failed xact and then proceed as in the idle case. Anyone have a good case
to make for either choice? Should we redefine LO_TRANSACTION to allow
both choices to be supported? Does anyone feel that the existing
LO_TRANSACTION behaviors of forced-rollback or forced-commit of open
transactions should continue to be supported?
* \encoding can be driven from ParameterStatus too, thus making it more
robust (it will correctly show an encoding set via a SET command).
Opinions welcome ...
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Since libpq now keeps track of transaction state, it would be a simple > matter to add a prompt-string % construct to show something that indicates > the state greg=> SELECT 'I am idle'; greg=*> SELECT 'I am in a transaction'; greg=!> SELECT 'I am in a failed transaction, please save me.'; > Not sure how important that really is, given that we don't recommend > running psql against servers of different versions. We also do not check the version or throw a warning on a mismatched version, something I think it may be time to reevaluate. Everything else sounds good. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200306251455 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE++fzOvJuQZxSWSsgRApyZAKDQbTVR6u6sFGgl4FWgYy23VQ/U8ACfTHyU FGkdjEg6CCIsOQo9NSYen8g= =9fFL -----END PGP SIGNATURE-----
Tom Lane wrote: > There are a number of things that need to be done in psql before feature > freeze. Any comments on the following points? > > * We need a client-side autocommit-off implementation to substitute for > the one removed from the server. I am inclined to create a new psql > backslash command: > \autocommit on traditional handling of transactions > \autocommit off force BEGIN before any user command > that's not already in a transaction > \autocommit with no arg, show current state > An alternative to creating a new command is to define a special variable > in psql, whereupon the above three would instead be rendered > \set AUTOCOMMIT on > \set AUTOCOMMIT off > \echo :AUTOCOMMIT > The first choice seems less verbose to me, but if anyone wants to make a > case for the second, I'm open to it. Note that either of these could be > put in ~/.psqlrc if someone wants autocommit off as their default. I thought we were trying to get away from multi-letter backslash variables like \connect. I think we should use \set,\echo, though of course, those are multi-letter too, so maybe it isn't an issue. I just find \df and \df_and_more_letters_that_make_a_word to just be weird. > * Since libpq now keeps track of transaction state, it would be a simple > matter to add a prompt-string % construct to show something that indicates > the state (with possible values "idle", "in transaction", "in failed > transaction"). Any thoughts about exactly what this ought to look like? > I prototyped it with code that showed 'I', 'T', or 'E' but I suspect that > non-alphabetic characters would be better, since they wouldn't look like > part of a database name or other things you might put in the prompt. > > BTW, both of the above features will work against pre-7.4 servers, with > the exception that a 7.3 server running with server-side autocommit off > will confuse libpq's tracking of transaction state. Not sure how > important that really is, given that we don't recommend running psql > against servers of different versions. Don't worry about old servers in this regard. > * I plan to get rid of psql's startup-time query to find out if you are > superuser, and instead let the '#' vs '>' prompt be driven through another > ParameterStatus variable, per a proposal I made awhile ago. (If anyone > can propose a better name for the GUC variable than "am_superuser", let's I think 'is_superuser' is more appropriate. -- 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
greg@turnstep.com writes:
>> Not sure how important that really is, given that we don't recommend
>> running psql against servers of different versions.
> We also do not check the version or throw a warning on a mismatched
> version, something I think it may be time to reevaluate.
It would be easy (and essentially free, since libpq already gets the info)
to add such a notice to psql startup. How do other people feel about
it? How would you word the notice exactly?
"psql: server version is FOO, psql version is BAR, some things may not work"
seems awfully vague, but I doubt we can be much more specific ...
regards, tom lane
From: "Tom Lane" <tgl@sss.pgh.pa.us> > It would be easy (and essentially free, since libpq already gets the info) > to add such a notice to psql startup. How do other people feel about > it? How would you word the notice exactly? > "psql: server version is FOO, psql version is BAR, some things may not work" > seems awfully vague, but I doubt we can be much more specific ... Do we have any documentation on psql compatibility across versions? If so, we could refer the user to that document. Might be nice to know that most \commands will not work, but ad hoc queries will be fine. Matthew
Is it too late to suggest that there be a way to have output displayed on screen AND output to a file? I've got my Oracle systems set up so that all sqlplus sessions do this, complete with using the process or session number as part of the output file name so each is unique. This gives me a running record of what I did when, which saves me a LOT of time if I want to view the results of some query I ran last week. I can delete or zip up files if I get short on disk space space -- Mike Nolan
nolan@celery.tssi.com writes:
> Is it too late to suggest that there be a way to have output displayed
> on screen AND output to a file?
tee perhaps?
This is irrelevant to what I'm doing, in any case, and it's not an itch
I feel personally. Work on it yourself if you want it ...
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes: > I think 'is_superuser' is more appropriate. Okay, fine. I forgot one other thing that is available from the recent libpq additions and needs to be exposed by psql: error message verbosity setting. What's there now is described in http://candle.pha.pa.us/main/writings/pgsql/sgml/libpq-control.html to wit, "terse", "default", and "verbose" options. We have the choice of exposing this as a backslash command or as a special variable in psql --- any preferences? Also, I would like to provide the same set of options w.r.t. messages logged in the server log. Here there is an additional frammish that could be imagined, ie, more detail for more-serious errors. Any opinions about what it should look like? regards, tom lane
On Wed, 2003-06-25 at 13:49, Tom Lane wrote: > There are a number of things that need to be done in psql before feature > freeze. Any comments on the following points? > > * We need a client-side autocommit-off implementation to substitute for > the one removed from the server. I am inclined to create a new psql > backslash command: > \autocommit on traditional handling of transactions > \autocommit off force BEGIN before any user command > that's not already in a transaction > \autocommit with no arg, show current state > An alternative to creating a new command is to define a special variable > in psql, whereupon the above three would instead be rendered > \set AUTOCOMMIT on > \set AUTOCOMMIT off > \echo :AUTOCOMMIT > The first choice seems less verbose to me, but if anyone wants to make a > case for the second, I'm open to it. Note that either of these could be > put in ~/.psqlrc if someone wants autocommit off as their default. A case for the latter is that it's very similar to environment variables, a well known system. The main advantage I see -- other than the shell similarities -- is the ability to call set with no arguments and get a listing of all the options. This is currently much shorter than the already overburdened \? screen and would concentrate all psql preference settings in one location.
> nolan@celery.tssi.com writes: > > Is it too late to suggest that there be a way to have output displayed > > on screen AND output to a file? > > tee perhaps? Tee ALMOST does it. Try doing a \d while tee'ing the output, for example. I don't quite get everything back before it asks for the next input line, sometimes all that is missing is the prompt itself. I haven't set up a 7.4 test system yet, but I've been looking into it in 7.3.3. it gives me something fairly harmless to work on as I learn more C. I think tee may write straight to sysout, so it is probably intermingling with the writes from within psql. I'm not sure why sometimes it is only missing a line or two and other times it is missing several lines. There doesn't appear to be a way to set the popen on the \o command to non-buffer mode or to force a flush on a pipe. (The equivalent of fflush.) I have also noticed that if I have timing on, the timing stats do not get sent to the output file, just to the screen. (That doesn't concern me at this point, it was just a side comment on screen vs file output.) > This is irrelevant to what I'm doing, in any case, and it's not an itch > I feel personally. Work on it yourself if you want it ... I'm trying to, now I really feel like a rookie! :-) -- Mike Nolan
Arguile <arguile@lucentstudios.com> writes:
> On Wed, 2003-06-25 at 13:49, Tom Lane wrote:
>> The first choice seems less verbose to me, but if anyone wants to make a
>> case for the second, I'm open to it. Note that either of these could be
>> put in ~/.psqlrc if someone wants autocommit off as their default.
> A case for the latter is that it's very similar to environment
> variables, a well known system.
> The main advantage I see -- other than the shell similarities -- is the
> ability to call set with no arguments and get a listing of all the
> options. This is currently much shorter than the already overburdened \?
> screen and would concentrate all psql preference settings in one
> location.
Fair points. I'm sold on \set AUTOCOMMIT unless someone else wants to
try to change my mind again ...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I think 'is_superuser' is more appropriate. > > Okay, fine. > > I forgot one other thing that is available from the recent libpq > additions and needs to be exposed by psql: error message verbosity > setting. > > What's there now is described in > http://candle.pha.pa.us/main/writings/pgsql/sgml/libpq-control.html > to wit, "terse", "default", and "verbose" options. > > We have the choice of exposing this as a backslash command or as a > special variable in psql --- any preferences? My preference for such things is to use variables. It seems to me that backslash commands should be reserved for actual actions, e.g. "show me the list of tables" or "import data from stdin", etc. It seems to me that variables are a natural way of representing the state of psql, and that changing that state should be accomplished through the standard mechanisms, i.e. \set. > Also, I would like to provide the same set of options w.r.t. messages > logged in the server log. Here there is an additional frammish that > could be imagined, ie, more detail for more-serious errors. Any > opinions about what it should look like? Not sure exactly what you're asking for here. If you're asking what additional detail should be included for more serious errors, I'd say it should be things like the actual text of the query being executed and perhaps the file and line number of the code that threw the error. A stack trace could be useful in the most extreme cases (and, obviously, only when verbosity is maximized), too, but that may be too much to ask for. :-) -- Kevin Brown kevin@sysexperts.com
nolan@celery.tssi.com wrote: > > nolan@celery.tssi.com writes: > > > Is it too late to suggest that there be a way to have output displayed > > > on screen AND output to a file? > > > > tee perhaps? > > Tee ALMOST does it. Try doing a \d while tee'ing the output, for example. Try using "script" (start it from the shell before invoking psql). It sounds like it'll do much of what you're after. "Screen" also has a logging option which may work just as well, if not better, than "script", and has the additional advantage that the session will continue (and can be reattached to) even if your terminal window dies for whatever reason. -- Kevin Brown kevin@sysexperts.com
Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> Also, I would like to provide the same set of options w.r.t. messages
>> logged in the server log. Here there is an additional frammish that
>> could be imagined, ie, more detail for more-serious errors. Any
>> opinions about what it should look like?
> Not sure exactly what you're asking for here. If you're asking what
> additional detail should be included for more serious errors,
No, I was asking whether anyone thought such behavior should be
user-controllable, and if so exactly how the controlling GUC variables
should be defined.
One way I could imagine doing it is to split log_min_messages into
three variables, along the lines of "minimum message level to produce
a TERSE report", "minimum message level to produce a DEFAULT report",
and "minimum message level to produce a VERBOSE report". This seems
a bit inelegant though. Better ideas anyone?
regards, tom lane
Tom Lane wrote: > Kevin Brown <kevin@sysexperts.com> writes: > > Tom Lane wrote: > >> Also, I would like to provide the same set of options w.r.t. messages > >> logged in the server log. Here there is an additional frammish that > >> could be imagined, ie, more detail for more-serious errors. Any > >> opinions about what it should look like? > > > Not sure exactly what you're asking for here. If you're asking what > > additional detail should be included for more serious errors, > > No, I was asking whether anyone thought such behavior should be > user-controllable, and if so exactly how the controlling GUC variables > should be defined. > > One way I could imagine doing it is to split log_min_messages into > three variables, along the lines of "minimum message level to produce > a TERSE report", "minimum message level to produce a DEFAULT report", > and "minimum message level to produce a VERBOSE report". This seems > a bit inelegant though. Better ideas anyone? I doubt someone would want to control terse/default/verbose at various levels --- I assume they would just want all their messages to be terse/default/ or verbose. -- 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> One way I could imagine doing it is to split log_min_messages into
>> three variables, along the lines of "minimum message level to produce
>> a TERSE report", "minimum message level to produce a DEFAULT report",
>> and "minimum message level to produce a VERBOSE report". This seems
>> a bit inelegant though. Better ideas anyone?
> I doubt someone would want to control terse/default/verbose at various
> levels --- I assume they would just want all their messages to be
> terse/default/ or verbose.
<shrug> That would certainly be the least work to implement. I was
fishing to see if anyone felt that more is needed.
regards, tom lane
--On Wednesday, June 25, 2003 22:58:39 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: >> Tom Lane wrote: >>> One way I could imagine doing it is to split log_min_messages into >>> three variables, along the lines of "minimum message level to produce >>> a TERSE report", "minimum message level to produce a DEFAULT report", >>> and "minimum message level to produce a VERBOSE report". This seems >>> a bit inelegant though. Better ideas anyone? > >> I doubt someone would want to control terse/default/verbose at various >> levels --- I assume they would just want all their messages to be >> terse/default/ or verbose. > > <shrug> That would certainly be the least work to implement. I was > fishing to see if anyone felt that more is needed. hooks for the next release after we all get some experience with this? -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
> This is irrelevant to what I'm doing, in any case, and it's not an itch
> I feel personally. Work on it yourself if you want it ...
OK, I figured it out. :-)
It's a fairly short patch in 7.3.3, what do I need to do to submit it
for 7.4?
I also made a minor functional change that may need to be turned into
an additional variant on echo: if ECHO is set to 'queries' and output
is being sent other than to stdout (\o), it echoes the query to the output
file or pipe.
If that's too much of a change and this needs to be a separate option,
I'd suggest something like setting ECHO to 'queryout'. To be complete,
would it need to have a command line equivalent, too?
BTW, I figured out how to direct the output to a separate file each time,
I put the following in .psqlrc:
\o |tee `echo psql_${$}.txt`
--
Mike Nolan
Do we have any way of seeing all the predefined psql \set setting? Right now if you do \set, you see the settings for that client, but you don't see a list of setting the psql understands, like autocommit. You can only see those in the psql manual. On the server side, we have SHOW ALL which shows all setting. Of course, \set works more like environment variables, so maybe it makes sense not to have a way to display the powerful ones, but it would be convenient. wIs this a TODO? --------------------------------------------------------------------------- Arguile wrote: > On Wed, 2003-06-25 at 13:49, Tom Lane wrote: > > There are a number of things that need to be done in psql before feature > > freeze. Any comments on the following points? > > > > * We need a client-side autocommit-off implementation to substitute for > > the one removed from the server. I am inclined to create a new psql > > backslash command: > > \autocommit on traditional handling of transactions > > \autocommit off force BEGIN before any user command > > that's not already in a transaction > > \autocommit with no arg, show current state > > An alternative to creating a new command is to define a special variable > > in psql, whereupon the above three would instead be rendered > > \set AUTOCOMMIT on > > \set AUTOCOMMIT off > > \echo :AUTOCOMMIT > > The first choice seems less verbose to me, but if anyone wants to make a > > case for the second, I'm open to it. Note that either of these could be > > put in ~/.psqlrc if someone wants autocommit off as their default. > > A case for the latter is that it's very similar to environment > variables, a well known system. > > The main advantage I see -- other than the shell similarities -- is the > ability to call set with no arguments and get a listing of all the > options. This is currently much shorter than the already overburdened \? > screen and would concentrate all psql preference settings in one > location. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- 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