Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Daniel Gustafsson]

> On 1 Mar 2023, at 10:29, Jim Jones  wrote:
> 
> On 01.03.23 01:47, Kirk Wolak wrote:
>> Patch Posted with one edit, for line editings (Thanks Jim!)
> The patch didn't pass the SanityCheck:
> 
> https://cirrus-ci.com/task/5445242183221248?logs=build#L1337
> 
> missing a header perhaps?
> 
> #include "time.h"

This patch spent the March commitfest not building and still doesn't build, so
I'm marking this returned with feedback.  Please feel free to resubmit to the
next commitfest if there is renewed interest in the patch.

--
Daniel Gustafsson

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Pavel Stehule]

ne 9. 4. 2023 v 3:54 odesílatel Tom Lane  napsal:

> Greg Stark  writes:
> > I'm not sure if the *prompt* is a sensible place for it though. The
> > place it seems like it would be most useful is reading the output of
> > script executions where there would be no prompts. Perhaps it's the
> > command tags and \echo statements that should be timestamped.
>
> Hmm, that is an interesting idea.  I kind of like it, not least because
> it eliminates most of the tension between wanting a complete timestamp
> and wanting a short prompt.  Command tags are short enough that there's
> plenty of room.
>

I don't agree so there is a common request for a short prompt. Usually I
use four terminals on screen, and still my terminal has a width of 124
characters (and I use relatively small display of my Lenovo T520). Last
years I use prompt like:

(2023-04-09 06:08:30) postgres=# select 1;
┌──┐
│ ?column? │
╞══╡
│1 │
└──┘
(1 row)

and it is working. Nice thing when I paste the timestamp in examples. I
have not any problems with prompt width

Regards

Pavel

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Tom Lane]

Greg Stark  writes:
> I'm not sure if the *prompt* is a sensible place for it though. The
> place it seems like it would be most useful is reading the output of
> script executions where there would be no prompts. Perhaps it's the
> command tags and \echo statements that should be timestamped.

Hmm, that is an interesting idea.  I kind of like it, not least because
it eliminates most of the tension between wanting a complete timestamp
and wanting a short prompt.  Command tags are short enough that there's
plenty of room.

> And I think experience shows that there are three reasonable formats
> for dates, the default LC_TIME format, ISO8601, and a relative
> "seconds (with milliseconds) since starting". I think having a feature
> that doesn't support those three would feel incomplete and eventually
> need to be finished.

Yeah, I don't believe that one timestamp format is going to satisfy
everyone.  But that was especially true when trying to wedge it
into the prompt, where the need for brevity adds more constraints.

regards, tom lane

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Greg Stark]

On Wed, 22 Feb 2023 at 13:38, Kirk Wolak  wrote:
>
> 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!

Yeah, well, it's kind of beside the point that you're satisfied with
this one format. We tend to think about what all users would expect
and what a complete feature would look like.

I actually tend to think this would be a nice feature. It's telling
that log files and other tracing tools tend to produce exactly this
type of output with every line prefixed with either a relative or
absolute timestamp.

I'm not sure if the *prompt* is a sensible place for it though. The
place it seems like it would be most useful is reading the output of
script executions where there would be no prompts. Perhaps it's the
command tags and \echo statements that should be timestamped.

And I think experience shows that there are three reasonable formats
for dates, the default LC_TIME format, ISO8601, and a relative
"seconds (with milliseconds) since starting". I think having a feature
that doesn't support those three would feel incomplete and eventually
need to be finished.

-- 
greg

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Thu, Mar 2, 2023 at 9:56 AM Laurenz Albe 
wrote:

> On Wed, 2023-03-01 at 11:13 -0500, Kirk Wolak wrote:
> > Thanks, corrected, and confirmed Unix line endings.
>
> The patch builds fine and works as intended.
>
> I leave it to the committers to decide whether the patch is worth the
> effort or not, given that you can get a similar effect with %`date`.
> It adds some value by being simpler and uniform across all platforms.
>
> I'll mark the patch as "ready for committer".
>
> Yours,
> Laurenz Albe
>

Thanks Laurenz.

To be clear, I use windows AND linux, and I share my file between them.

in linux:  `date +%H:%M:%S`   is used
in windows: `ECHO %time%`

so, I wrote a ts.cmd and ts.sh  so I could share one prompt:   `ts`
but now every time I connect a new account to this file, I have to go
find/copy my ts file.
Same when I share it with other developers.

This was the pain that started the quest.
Thanks to everyone for their support!

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Laurenz Albe]

On Wed, 2023-03-01 at 11:13 -0500, Kirk Wolak wrote:
> Thanks, corrected, and confirmed Unix line endings.

The patch builds fine and works as intended.

I leave it to the committers to decide whether the patch is worth the
effort or not, given that you can get a similar effect with %`date`.
It adds some value by being simpler and uniform across all platforms.

I'll mark the patch as "ready for committer".

Yours,
Laurenz Albe

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Wed, Mar 1, 2023 at 11:55 AM Jim Jones  wrote:

> On 01.03.23 17:13, Kirk Wolak wrote:
>
> Thanks, corrected, and confirmed Unix line endings.
> FWIW, the simplest way to test it is with this command (I usually get it
> wrong on the first guess)
>
> \set PROMPT1 %T ' ' :PROMPT1
>
> Kirk
>
> Nice. The patch applies clean and the cfbots seem much happier now - all
> passed.
>
> 17:23:19 postgres=# SELECT now();
>   now
> ---
>  2023-03-01 17:23:19.807339+01
> (1 row)
>
> The docs render also just fine. I'm now wondering if HH24:MI:SS should be
> formatted with, e.g. using 
>
> "The current time on the client in HH24:MI:SS format."
>
> But that I'll leave to the docs experts to judge :)
>
> Best, Jim
>
Thanks Jim.

I hope one of the Docs experts chime in.  It's easy enough to fix.  Just
not sure if it's required.
What a great learning experience!

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Jim Jones]

On 01.03.23 17:13, Kirk Wolak wrote:

Thanks, corrected, and confirmed Unix line endings.
FWIW, the simplest way to test it is with this command (I usually get 
it wrong on the first guess)


\set PROMPT1 %T ' ' :PROMPT1

Kirk


Nice. The patch applies clean and the cfbots seem much happier now - all 
passed.


17:23:19 postgres=# SELECT now();
  now
---
 2023-03-01 17:23:19.807339+01
(1 row)

The docs render also just fine. I'm now wondering if HH24:MI:SS should 
be formatted with, e.g. using 


"The current time on the client in HH24:MI:SS format."

But that I'll leave to the docs experts to judge :)

Best, Jim

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Wed, Mar 1, 2023 at 4:41 AM Jim Jones  wrote:

> On 01.03.23 01:59, Kirk Wolak wrote:
> > I cannot get the last email to show up for the commitfest.
> > This is version 2 of the original patch. [1]
> > Thanks Jim!
> >
> > [1]
> https://postgr.es/m/CACLU5mSRwHr_8z%3DenMj-nXF1tmC7%2BJn5heZQNiKuLyxYUtL2fg%40mail.gmail.com
> >
> > Regards Kirk.
>
> The patch didn't pass the SanityCheck:
>
> https://cirrus-ci.com/task/5445242183221248?logs=build#L1337
>
> missing a header perhaps?
>
> #include "time.h"
>
> Best, Jim
>

Thanks, corrected, and confirmed Unix line endings.
FWIW, the simplest way to test it is with this command (I usually get it
wrong on the first guess)

\set PROMPT1 %T ' ' :PROMPT1

Kirk
From bfafeaec64d01a404fe36f26ec4355607776e66b Mon Sep 17 00:00:00 2001
From: Kirk Wolak 
Date: Wed, 1 Mar 2023 16:02:10 +
Subject: [PATCH] [PATCH v3] Time option added to psql prompt

This adds a useful time option to the prompt: %T. Which does not
require a wasteful backquoted shell command which is also not
compatible between operating systems.
The format is simply HH24:MI:SS no other options available by design!

Author: Kirk Wolak 
Reviewed-By: Andrey Borodin 
Reviewed-By: Nikolay Samokhvalov 
Thread: 
https://postgr.es/m/CACLU5mSRwHr_8z%3DenMj-nXF1tmC7%2BJn5heZQNiKuLyxYUtL2fg%40mail.gmail.com
---
 doc/src/sgml/ref/psql-ref.sgml |  9 +
 src/bin/psql/prompt.c  | 11 ++-
 2 files changed, 19 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11d..04ab9eeb8c0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4575,6 +4575,15 @@ testdb= INSERT INTO my_table VALUES 
(:'content');
 
   
 
+  
+%T
+
+ 
+  The current time on the client in HH24:MI:SS format.
+ 
+
+  
+
   
 %x
 
diff --git a/src/bin/psql/prompt.c b/src/bin/psql/prompt.c
index 969cd9908e5..24dffcd461c 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -18,6 +18,7 @@
 #include "libpq/pqcomm.h"
 #include "prompt.h"
 #include "settings.h"
+#include "time.h"
 
 /*--
  * get_prompt
@@ -41,6 +42,7 @@
  * or a ! if session is not connected to a database;
  * in prompt2 -, *, ', or ";
  * in prompt3 nothing
+ * %T - time in HH24:MI:SS format
  * %x - transaction status: empty, *, !, ? (unknown or no connection)
  * %l - The line number inside the current statement, starting from 1.
  * %? - the error code of the last query (not yet implemented)
@@ -223,7 +225,14 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
break;
}
break;
-
+   /* output HH24:MI:SS */
+   case 'T':
+   {
+   time_t current_time = 
time(NULL);
+   struct tm *tm_info = 
localtime(_time);
+   sprintf(buf, "%02d:%02d:%02d", 
tm_info->tm_hour, tm_info->tm_min, tm_info->tm_sec);
+   }
+   break;
case 'x':
if (!pset.db)
buf[0] = '?';
-- 
GitLab

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Jim Jones]

On 01.03.23 01:59, Kirk Wolak wrote:

I cannot get the last email to show up for the commitfest.
This is version 2 of the original patch. [1]
Thanks Jim!

[1]https://postgr.es/m/CACLU5mSRwHr_8z%3DenMj-nXF1tmC7%2BJn5heZQNiKuLyxYUtL2fg%40mail.gmail.com

Regards Kirk.


The patch didn't pass the SanityCheck:

https://cirrus-ci.com/task/5445242183221248?logs=build#L1337

missing a header perhaps?

#include "time.h"

Best, Jim

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Jim Jones]

On 01.03.23 01:47, Kirk Wolak wrote:

Patch Posted with one edit, for line editings (Thanks Jim!)


The patch didn't pass the SanityCheck:

https://cirrus-ci.com/task/5445242183221248?logs=build#L1337

missing a header perhaps?

#include "time.h"

Best, Jim

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Thu, Feb 23, 2023 at 2:05 PM Kirk Wolak  wrote:

> On Thu, Feb 23, 2023 at 9:52 AM Tom Lane  wrote:
>
>> Heikki Linnakangas  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
>>
>
> Note, for this above feature, I was thinking we have a  ROW_COUNT variable
> I use \set to see.
> The simplest way to add this is maybe a set variable:  EXEC_TIME
> And it's set when ROW_COUNT gets set.
> +1
>
> ==
> Now, since this opened a lively discussion, I am officially submitting my
> first patch.
> This includes the small change to prompt.c and the documentation.  I had
> help from Andrey Borodin,
> and Pavel Stehule, who have supported me in how to propose, and use
> gitlab, etc.
>
> We are programmers... It's literally our job to sharpen our tools.  And
> PSQL is one of my most used.
> A small frustration, felt regularly was the motive.
>
> Regards, Kirk
> PS: If I am supposed to edit the subject to say there is a patch here, I
> did not know
> PPS: I appreciate ANY and ALL feedback... This is how we learn!
>

Patch Posted with one edit, for line editings (Thanks Jim!)
From b9db157177bbdeeeb6d35c3623ca9355141419d7 Mon Sep 17 00:00:00 2001
From: Jim Jones 
Date: Wed, 1 Mar 2023 00:07:55 +0100
Subject: [PATCH v2] Time option added to psql prompt

This adds a useful time option to the prompt: %T. Which does not
require a wasteful backquoted shell command which is also not
compatible between operating systems.
The format is simply HH24:MI:SS no other options available by design!

Author: Kirk Wolak 
Reviewed-By: Andrey Borodin 
Reviewed-By: Nikolay Samokhvalov 
Thread: 
https://postgr.es/m/CACLU5mSRwHr_8z%3DenMj-nXF1tmC7%2BJn5heZQNiKuLyxYUtL2fg%40mail.gmail.com
---
 doc/src/sgml/ref/psql-ref.sgml |  9 +
 src/bin/psql/prompt.c  | 10 +-
 2 files changed, 18 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11..04ab9eeb8c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4575,6 +4575,15 @@ testdb= INSERT INTO my_table VALUES 
(:'content');
 
   
 
+  
+%T
+
+ 
+  The current time on the client in HH24:MI:SS format.
+ 
+
+  
+
   
 %x
 
diff --git a/src/bin/psql/prompt.c b/src/bin/psql/prompt.c
index 969cd9908e..0c0c725df5 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -41,6 +41,7 @@
  * or a ! if session is not connected to a database;
  * in prompt2 -, *, ', or ";
  * in prompt3 nothing
+ * %T - time in HH24:MI:SS format
  * %x - transaction status: empty, *, !, ? (unknown or no connection)
  * %l - The line number inside the current statement, starting from 1.
  * %? - the error code of the last query (not yet implemented)
@@ -223,7 +224,14 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
break;
}
break;
-
+   /* output HH24:MI:SS */
+   case 'T':
+   {
+   time_t current_time = 
time(NULL);
+   struct tm *tm_info = 
localtime(_time);
+   sprintf(buf, "%02d:%02d:%02d", 
tm_info->tm_hour, tm_info->tm_min, tm_info->tm_sec);
+   }
+   break;
case 'x':
if (!pset.db)
buf[0] = '?';
-- 
2.25.1

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Thu, Feb 23, 2023 at 9:52 AM Tom Lane  wrote:

> Heikki Linnakangas  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
>

Note, for this above feature, I was thinking we have a  ROW_COUNT variable
I use \set to see.
The simplest way to add this is maybe a set variable:  EXEC_TIME
And it's set when ROW_COUNT gets set.
+1

==
Now, since this opened a lively discussion, I am officially submitting my
first patch.
This includes the small change to prompt.c and the documentation.  I had
help from Andrey Borodin,
and Pavel Stehule, who have supported me in how to propose, and use gitlab,
etc.

We are programmers... It's literally our job to sharpen our tools.  And
PSQL is one of my most used.
A small frustration, felt regularly was the motive.

Regards, Kirk
PS: If I am supposed to edit the subject to say there is a patch here, I
did not know
PPS: I appreciate ANY and ALL feedback... This is how we learn!
From eaf6d05028052a3ccaa8d980953ac4fd75255250 Mon Sep 17 00:00:00 2001
From: Kirk Wolak 
Date: Thu, 23 Feb 2023 17:52:09 +
Subject: [PATCH] Time option added to psql prompt

This adds a useful time option to the prompt: %T. Which does not
require a wasteful backquoted shell command which is also not
compatible between operating systems.
The format is simply HH24:MI:SS no other options available by design!

Author: Kirk Wolak 
Reviewed-By: Andrey Borodin 
Reviewed-By: Nikolay Samokhvalov 
Thread: 
https://postgr.es/m/CACLU5mSRwHr_8z%3DenMj-nXF1tmC7%2BJn5heZQNiKuLyxYUtL2fg%40mail.gmail.com
---
 doc/src/sgml/ref/psql-ref.sgml |  9 +
 src/bin/psql/prompt.c  | 10 +-
 2 files changed, 18 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index dc6528dc11d..04ab9eeb8c0 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -4575,6 +4575,15 @@ testdb= INSERT INTO my_table VALUES 
(:'content');
 
   
 
+  
+%T
+
+ 
+  The current time on the client in HH24:MI:SS format.
+ 
+
+  
+
   
 %x
 
diff --git a/src/bin/psql/prompt.c b/src/bin/psql/prompt.c
index 969cd9908e5..a590c27389b 100644
--- a/src/bin/psql/prompt.c
+++ b/src/bin/psql/prompt.c
@@ -41,6 +41,7 @@
  * or a ! if session is not connected to a database;
  * in prompt2 -, *, ', or ";
  * in prompt3 nothing
+ * %T - time in HH24:MI:SS format
  * %x - transaction status: empty, *, !, ? (unknown or no connection)
  * %l - The line number inside the current statement, starting from 1.
  * %? - the error code of the last query (not yet implemented)
@@ -223,7 +224,14 @@ get_prompt(promptStatus_t status, ConditionalStack cstack)
break;
}
break;
-
+   /* output HH24:MI:SS */
+   case 'T':
+   {
+   time_t current_time = 
time(NULL);
+   struct tm *tm_info = 
localtime(_time);
+   sprintf(buf, "%02d:%02d:%02d", 
tm_info->tm_hour, tm_info->tm_min, tm_info->tm_sec);
 
+   }   
+   break;
case 'x':
if (!pset.db)
buf[0] = '?';
-- 
GitLab

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Thu, Feb 23, 2023 at 1:16 PM Maciek Sakrejda 
wrote:

> On Thu, Feb 23, 2023, 09:55 Nikolay Samokhvalov 
> wrote:
>
>> On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda 
>> 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.
>>
>
> 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!

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Pavel Stehule]

čt 23. 2. 2023 v 19:16 odesílatel Maciek Sakrejda 
napsal:

> On Thu, Feb 23, 2023, 09:55 Nikolay Samokhvalov 
> wrote:
>
>> On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda 
>> 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.
>>
>
> 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


> Thanks,
> Maciek
>
>>

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Maciek Sakrejda]

On Thu, Feb 23, 2023, 09:55 Nikolay Samokhvalov 
wrote:

> On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda 
> 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.
>

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

>

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Nikolay Samokhvalov]

On Thu, Feb 23, 2023 at 9:05 AM Maciek Sakrejda 
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.

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Maciek Sakrejda]

+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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Tom Lane]

Heikki Linnakangas  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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Heikki Linnakangas]

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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Peter Eisentraut]

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.

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Nathan Bossart]

On Wed, Feb 22, 2023 at 07:17:37PM +0100, Daniel Gustafsson wrote:
>> On 22 Feb 2023, at 19:14, Heikki Linnakangas  wrote:
> 
>> How about a new backslash command or psql variable to show how long the 
>> previous statement took? Something like:
>> 
>> postgres=# select 
>> ?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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Pavel Stehule]

st 22. 2. 2023 v 18:59 odesílatel Nikolay Samokhvalov 
napsal:

> On Wed, Feb 22, 2023 at 9:55 AM Tom Lane  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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Wed, Feb 22, 2023 at 1:14 PM Heikki Linnakangas  wrote:

> On 22/02/2023 19:59, Nikolay Samokhvalov wrote:
> > On Wed, Feb 22, 2023 at 9:55 AM Tom Lane  > > 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 
>   ?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).

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Kirk Wolak]

On Wed, Feb 22, 2023 at 12:55 PM Tom Lane  wrote:

> Kirk Wolak  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(_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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Pavel Stehule]

st 22. 2. 2023 v 19:14 odesílatel Heikki Linnakangas 
napsal:

> On 22/02/2023 19:59, Nikolay Samokhvalov wrote:
> > On Wed, Feb 22, 2023 at 9:55 AM Tom Lane  > > 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 
>   ?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



>
> - Heikki
>
>

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Pavel Stehule]

Hi

st 22. 2. 2023 v 18:55 odesílatel Tom Lane  napsal:

> Kirk Wolak  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


>
> regards, tom lane
>

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Daniel Gustafsson]

> On 22 Feb 2023, at 19:14, Heikki Linnakangas  wrote:

> How about a new backslash command or psql variable to show how long the 
> previous statement took? Something like:
> 
> postgres=# select 
> ?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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Heikki Linnakangas]

On 22/02/2023 19:59, Nikolay Samokhvalov wrote:
On Wed, Feb 22, 2023 at 9:55 AM Tom Lane > 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 
 ?column?
--
  123
(1 row)

postgres=# \time

Time: 14011.975 ms (00:14.012)

This would solve the "I forgot to time something" problem.

- Heikki

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Nikolay Samokhvalov]

On Wed, Feb 22, 2023 at 9:55 AM Tom Lane  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.

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Tom Lane]

Kirk Wolak  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

Re: Proposal: %T Prompt parameter for psql for current time (like Oracle has)

[Nikolay Samokhvalov]

On Wed, Feb 22, 2023 at 9:18 AM Kirk Wolak  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