Proposal: %T Prompt parameter for psql for current time (like Oracle has)
Proposal: Simply add the %T (PROMPT variable) to output the current time
(HH24:MI:SS) into the prompt. This has been in sqlplus since I can
remember, and I find it really useful when I forgot to time something, or
to review for Time spent on a problem, or for how old my session is...
I am recommending no formatting options, just keep it simple. No, I don't
care about adding the date. If I don't know the date of some line in my
history, it's already a problem! (And date would logically be some other
variable)
Yes, I've found ways around it using the shell backquote. This is hacky,
and it's also really ugly in windows. I also found it impossible to share
my plpgsqlrc file because between linux and windows.
This would be current time on the local machine. Keeping it simple.
It feels like a small change. The simplest test would be to capture the
prompt, select sleep(1.1); and make sure the prompt change. This code
should be trivially stable.
If it seems useful, I believe I can work with others to get it implemented,
and the documentation changed, and a patch generated. (I need to develop
these skills)
What does the community say? Is there support for this?
Regards, Kirk
On Wed, Feb 22, 2023 at 9:18 AM Kirk Wolak <wolakk@gmail.com> wrote:
Proposal: Simply add the %T (PROMPT variable) to output the current time
(HH24:MI:SS) into the prompt. This has been in sqlplus since I can
remember, and I find it really useful when I forgot to time something, or
to review for Time spent on a problem, or for how old my session is...
This is a great idea, in my opinion. I usually do something involving ts to
track timestamps when executing something non-trivial via psql in
interactive (see below) or non-interactive mode.
But this is a not well-known thing to use (and ts is not installed by
default on Ubuntu, etc.) – having timestamps in prompt would be convenient.
test=> \o | ts
test=> select 1;
test=> Feb 22 09:49:49 ?column?
Feb 22 09:49:49 ----------
Feb 22 09:49:49 1
Feb 22 09:49:49 (1 row)
Feb 22 09:49:49
Kirk Wolak <wolakk@gmail.com> writes:
Proposal: Simply add the %T (PROMPT variable) to output the current time
(HH24:MI:SS) into the prompt.
I'm not really convinced that %`date` isn't a usable solution for this,
especially since it seems like a very niche requirement. The next
person who wants it might well have a different desire than you
for exactly what gets shown. The output of date can be customized,
but a hard-wired prompt.c feature not so much.
On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.
regards, tom lane
On Wed, Feb 22, 2023 at 9:55 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.
It depends on personal preferences. When I work on a large screen, I can
afford to spend some characters in prompts, if it gives convenience – and
many do (looking, for example, at modern tmux/zsh prompts showing git
branch context, etc).
Default behavior might remain short – it wouldn't make sense to extend it
for everyone.
On 22/02/2023 19:59, Nikolay Samokhvalov wrote:
On Wed, Feb 22, 2023 at 9:55 AM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.It depends on personal preferences. When I work on a large screen, I can
afford to spend some characters in prompts, if it gives convenience –
and many do (looking, for example, at modern tmux/zsh prompts showing
git branch context, etc).Default behavior might remain short – it wouldn't make sense to extend
it for everyone.
I have no objections to adding a %T option, although deciding what
format to use is a hassle. -1 for changing the default.
But let's look at the original request:
This has been in sqlplus since I can remember, and I find it really
useful when I forgot to time something, or to review for Time spent
on a problem, or for how old my session is...
I've felt that pain too. You run a query, and it takes longer than I
expected. How long did it actually take? Too bad I didn't enable \timing
beforehand..
How about a new backslash command or psql variable to show how long the
previous statement took? Something like:
postgres=# select <unexpectedly slow query>
?column?
----------
123
(1 row)
postgres=# \time
Time: 14011.975 ms (00:14.012)
This would solve the "I forgot to time something" problem.
- Heikki
On 22 Feb 2023, at 19:14, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
How about a new backslash command or psql variable to show how long the previous statement took? Something like:
postgres=# select <unexpectedly slow query>
?column?
----------
123
(1 row)postgres=# \time
Time: 14011.975 ms (00:14.012)
This would solve the "I forgot to time something" problem.
I don't have an opinion on adding a prompt option, but I've wanted this
(without realizing this was the format of it) many times.
--
Daniel Gustafsson
Hi
st 22. 2. 2023 v 18:55 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Kirk Wolak <wolakk@gmail.com> writes:
Proposal: Simply add the %T (PROMPT variable) to output the current time
(HH24:MI:SS) into the prompt.I'm not really convinced that %`date` isn't a usable solution for this,
especially since it seems like a very niche requirement. The next
person who wants it might well have a different desire than you
for exactly what gets shown. The output of date can be customized,
but a hard-wired prompt.c feature not so much.On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.
Can we introduce some special syntax that allows using words (and maybe
some params)?
Regards
Pavel
Show quoted text
regards, tom lane
st 22. 2. 2023 v 19:14 odesílatel Heikki Linnakangas <hlinnaka@iki.fi>
napsal:
On 22/02/2023 19:59, Nikolay Samokhvalov wrote:
On Wed, Feb 22, 2023 at 9:55 AM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.It depends on personal preferences. When I work on a large screen, I can
afford to spend some characters in prompts, if it gives convenience –
and many do (looking, for example, at modern tmux/zsh prompts showing
git branch context, etc).Default behavior might remain short – it wouldn't make sense to extend
it for everyone.I have no objections to adding a %T option, although deciding what
format to use is a hassle. -1 for changing the default.But let's look at the original request:
This has been in sqlplus since I can remember, and I find it really
useful when I forgot to time something, or to review for Time spent
on a problem, or for how old my session is...I've felt that pain too. You run a query, and it takes longer than I
expected. How long did it actually take? Too bad I didn't enable \timing
beforehand..How about a new backslash command or psql variable to show how long the
previous statement took? Something like:postgres=# select <unexpectedly slow query>
?column?
----------
123
(1 row)postgres=# \time
Time: 14011.975 ms (00:14.012)
This would solve the "I forgot to time something" problem.
It is a good idea, unfortunately, it doesn't help with more commands. But
it is a nice idea, and can be implemented.
I am not sure if \time is best way - maybe we can display another runtime
data (when it will be possible, like io profile or queryid)
Regards
Pavel
Show quoted text
- Heikki
On Wed, Feb 22, 2023 at 12:55 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Kirk Wolak <wolakk@gmail.com> writes:
Proposal: Simply add the %T (PROMPT variable) to output the current time
(HH24:MI:SS) into the prompt.I'm not really convinced that %`date` isn't a usable solution for this,
especially since it seems like a very niche requirement. The next
person who wants it might well have a different desire than you
for exactly what gets shown. The output of date can be customized,
but a hard-wired prompt.c feature not so much.On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.regards, tom lane
Tom,
I totally respect where you are coming from, and you are rightfully the
big dog!
In reverse order. That limited namespace. I assume you mean the 52 alpha
characters, of which, we are using 7,
and this change would make it 8. Can we agree that at the current pace of
consumption it will be decades before
we get to 26, and they appear to be pretty well defended?
I already requested ONLY the HH24 format. 8 characters of output. no
options. It's a waste of time.
After all these years, sqlplus still has only one setting (show it, or
not). I am asking the same here.
And I will gladly defend not changing it! Ever!
I believe that leaves the real question:
Can't we just shell out? (which is what I do no, with issues as stated, and
a lot harder to do from memory if someplace new)
It's far easier in linux than windows to get what you want.
It's much more complicated if you try to use the same pgsqlrc file for
multiple environments and users.
We are talking about adding this much code, and consuming 1 of the
remaining 45 namespace items.
case 'T':
time_t current_time = time(NULL);
struct tm *tm_info = localtime(¤t_time);
sprintf(buf, "%02d:%02d:%02d", tm_info->tm_hour,
tm_info->tm_min, tm_info->tm_sec);
break;
Does this help my case at all?
If I crossed any lines, it's not my intention. I was tired of dealing with
this, and helping others to set it up.
With Respect,
Kirk
On Wed, Feb 22, 2023 at 1:14 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
On 22/02/2023 19:59, Nikolay Samokhvalov wrote:
On Wed, Feb 22, 2023 at 9:55 AM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.It depends on personal preferences. When I work on a large screen, I can
afford to spend some characters in prompts, if it gives convenience –
and many do (looking, for example, at modern tmux/zsh prompts showing
git branch context, etc).Default behavior might remain short – it wouldn't make sense to extend
it for everyone.I have no objections to adding a %T option, although deciding what
format to use is a hassle. -1 for changing the default.But let's look at the original request:
This has been in sqlplus since I can remember, and I find it really
useful when I forgot to time something, or to review for Time spent
on a problem, or for how old my session is...I've felt that pain too. You run a query, and it takes longer than I
expected. How long did it actually take? Too bad I didn't enable \timing
beforehand..How about a new backslash command or psql variable to show how long the
previous statement took? Something like:postgres=# select <unexpectedly slow query>
?column?
----------
123
(1 row)postgres=# \time
Time: 14011.975 ms (00:14.012)
This would solve the "I forgot to time something" problem.
- Heikki
TBH, I have that turned on by default. Load a script. Have 300 of those
lines, and tell me how long it took?
In my case, it's much easier. The other uses cases, including noticing I
changed some configuration and I
should reconnect (because I use multiple sessions, and I am in the early
stages with lots of changes).
st 22. 2. 2023 v 18:59 odesílatel Nikolay Samokhvalov <samokhvalov@gmail.com>
napsal:
On Wed, Feb 22, 2023 at 9:55 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
On the whole I'd rather not eat more of the limited namespace for
psql prompt codes for this.It depends on personal preferences. When I work on a large screen, I can
afford to spend some characters in prompts, if it gives convenience – and
many do (looking, for example, at modern tmux/zsh prompts showing git
branch context, etc).Default behavior might remain short – it wouldn't make sense to extend it
for everyone.
+1
On Wed, Feb 22, 2023 at 07:17:37PM +0100, Daniel Gustafsson wrote:
On 22 Feb 2023, at 19:14, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
How about a new backslash command or psql variable to show how long the previous statement took? Something like:
postgres=# select <unexpectedly slow query>
?column?
----------
123
(1 row)postgres=# \time
Time: 14011.975 ms (00:14.012)
This would solve the "I forgot to time something" problem.
I don't have an opinion on adding a prompt option, but I've wanted this
(without realizing this was the format of it) many times.
+1
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
On 22.02.23 19:14, Heikki Linnakangas wrote:
How about a new backslash command or psql variable to show how long the
previous statement took? Something like:
If you don't have \timing turned on before the query starts, psql won't
record what the time was before the query, so you can't compute the run
time afterwards. This kind of feature would only work if you always
take the start time, even if \timing is turned off.
On 23/02/2023 13:20, Peter Eisentraut wrote:
On 22.02.23 19:14, Heikki Linnakangas wrote:
How about a new backslash command or psql variable to show how long the
previous statement took? Something like:If you don't have \timing turned on before the query starts, psql won't
record what the time was before the query, so you can't compute the run
time afterwards. This kind of feature would only work if you always
take the start time, even if \timing is turned off.
Correct. That seems acceptable though? gettimeofday() can be slow on
some platforms, but I doubt it's *that* slow, that we couldn't call it
two times per query.
- Heikki
Heikki Linnakangas <hlinnaka@iki.fi> writes:
On 23/02/2023 13:20, Peter Eisentraut wrote:
If you don't have \timing turned on before the query starts, psql won't
record what the time was before the query, so you can't compute the run
time afterwards. This kind of feature would only work if you always
take the start time, even if \timing is turned off.
Correct. That seems acceptable though? gettimeofday() can be slow on
some platforms, but I doubt it's *that* slow, that we couldn't call it
two times per query.
Yeah, you'd need to capture both the start and stop times even if
\timing isn't on, in case you get asked later. But the backend is
going to call gettimeofday at least once per query, likely more
depending on what features you use. And there are inherently
multiple kernel calls involved in sending a query and receiving
a response. I tend to agree with Heikki that this overhead would
be unnoticeable. (Of course, some investigation proving that
wouldn't be unwarranted.)
regards, tom lane
+1 on solving the general problem of "I forgot to set \timing--how
long did this run?". I could have used that more than once in the
past, and I'm sure it will come up again.
I think Heikki's solution is probably more practical since (1) even if
we add the prompt parameter originally proposed, I don't see it being
included in the default, so it would require users to change their
prompt before they can benefit from it and (2) even if we commit to
never allowing tweaks to the format, I foresee a slow but endless
trickle of requests and patches to do so.
Thanks,
Maciek
On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda <m.sakrejda@gmail.com>
wrote:
I think Heikki's solution is probably more practical since (1) ..
Note that these ideas target two *different* problems:
- what was the duration of the last query
- when was the last query executed
So, having both solved would be ideal.
On Thu, Feb 23, 2023, 09:55 Nikolay Samokhvalov <samokhvalov@gmail.com>
wrote:
On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda <m.sakrejda@gmail.com>
wrote:I think Heikki's solution is probably more practical since (1) ..
Note that these ideas target two *different* problems:
- what was the duration of the last query
- when was the last query executedSo, having both solved would be ideal.
Fair point, but since the duration solution needs to capture two timestamps
anyway, it could print start time as well as duration.
The prompt timestamp could still be handy for more intricate session
forensics, but I don't know if that's a common-enough use case.
Thanks,
Maciek
Show quoted text
čt 23. 2. 2023 v 19:16 odesílatel Maciek Sakrejda <m.sakrejda@gmail.com>
napsal:
On Thu, Feb 23, 2023, 09:55 Nikolay Samokhvalov <samokhvalov@gmail.com>
wrote:On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda <m.sakrejda@gmail.com>
wrote:I think Heikki's solution is probably more practical since (1) ..
Note that these ideas target two *different* problems:
- what was the duration of the last query
- when was the last query executedSo, having both solved would be ideal.
Fair point, but since the duration solution needs to capture two
timestamps anyway, it could print start time as well as duration.The prompt timestamp could still be handy for more intricate session
forensics, but I don't know if that's a common-enough use case.
It is hard to say what is a common enough case, but I cannot imagine more
things than this.
small notice - bash has special support for this
Regards
Pavel
Show quoted text
Thanks,
Maciek
On Thu, Feb 23, 2023 at 1:16 PM Maciek Sakrejda <m.sakrejda@gmail.com>
wrote:
On Thu, Feb 23, 2023, 09:55 Nikolay Samokhvalov <samokhvalov@gmail.com>
wrote:On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda <m.sakrejda@gmail.com>
wrote:I think Heikki's solution is probably more practical since (1) ..
Note that these ideas target two *different* problems:
- what was the duration of the last query
- when was the last query executedSo, having both solved would be ideal.
Fair point, but since the duration solution needs to capture two
timestamps anyway, it could print start time as well as duration.The prompt timestamp could still be handy for more intricate session
forensics, but I don't know if that's a common-enough use case.Thanks,
Maciek
It's really common during migrations, and forensics. I often do a bunch of
stuff in 2 systems. Then check the overlap.
Andrey brought up the value of 2 people separate working on things, being
able to go back and review when did you change that setting? Which has
happened to many of us in support sessions...
Thanks!
