Time measurement format - more human readable

On 29/09/14 00:49, Bogdan Pilch wrote:

Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way time spent executing the SQL
commands is printed out.

The idea is to have a human readable time printed, e.g.:
Time: 1:32:15.45 m:s:ms
Time: 2_10:12:55:444.033 d_h:m:s:ms

Attached you can find a patch without any regression tests for that as
this is practically impossible to test with regression tests. The
duration of an SQL command (even though using pg_sleep) would differ
on each machine and even between consecutive runs. Therefore one
cannot specify a static expected output.
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.

My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.

This modification does not require any interaction with user.
It may create backward compatibility issues if some SQL developers
assumed that the format is always <milis>.<micros>.

regards
bogdan

If this is a forced, and not optional, then I think it is a backward
step. IMnsHO

For programmatic analysis: either <milis>.<micros> or the number, of
seconds (with a fractional part), would be okay.

I would be happy if there was a configuration parameter to control it.
At least a simple boolean to choose between the new & old format - but
better still, would be a time format string to allow people to choose
the representation they consider most appropriate for their own needs.

Having a configuration parameter set to the original format, would also
avoid unnecessary backwards compatibility problems!

Cheers,
Gavin

On 9/28/14, 7:49 AM, Bogdan Pilch wrote:

I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way time spent executing the SQL
commands is printed out.

The idea is to have a human readable time printed

There are already a wide range of human readable time interval output
formats available in the database; see the list at
http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE

If none of those are acceptable to you, it would be difficult but not
impossible to justify something new. I could see tweaking one of those
into a slightly updated new style aimed at this specific job, especially
since it doesn't have to consider things like negative intervals.

There's value in printing time measurements using one of these interval
styles sometimes, instead of the relatively raw values given right now.
It would need to be an option though, and one that let the user allow
choosing any of the supported interval formats. I personally would
prefer to never see the existing format the number is reported in go
away--too much software already expects it to be there, in that format.
But adding this human readable version after that, when the user asks
specifically for it, could be an acceptable addition.

So there's a rough spec for the job you'd have to take on here. I'd
expect it to expand in scope almost immediately to also consider the
output of similar time intervals from mechanisms like
log_min_duration_statement, too though, rather than just focusing on
psql timing data. There's a whole second round of almost inevitable
scope creep to working on this.

If you were hoping what you submitted might be considered directly,
sorry; that's not going to happen. Handling input and output of times
and dates is a very deep topic, and small patches trying to adjust such
behavior without grappling with the full complexity are normally
rejected outright. There are cases where the existing code just has
simple hacks in there now that could easily be whacked around. But once
the topic of cleaning those up appears, swapping to an alternate simple
hack is rarely how that goes. It normally heads toward considering the
full right thing to do to handle all cases usefully.

--
Greg Smith greg.smith@crunchydatasolutions.com
Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2014-09-28 20:32:30 -0400, Gregory Smith wrote:

On 9/28/14, 7:49 AM, Bogdan Pilch wrote:

I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way time spent executing the SQL
commands is printed out.

The idea is to have a human readable time printed

There are already a wide range of human readable time interval output
formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE

He's talking about psql's \timing...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andres Freund <andres@2ndquadrant.com> writes:

On 2014-09-28 20:32:30 -0400, Gregory Smith wrote:

On 9/28/14, 7:49 AM, Bogdan Pilch wrote:

I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way time spent executing the SQL
commands is printed out.

There are already a wide range of human readable time interval output
formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE

He's talking about psql's \timing...

Indeed. Still, it seems like this has more downside than upside.
It seems likely to break some peoples' scripts, and where exactly
is the groundswell of complaint that the existing format is
unreadable? TBH, I've not heard even one complaint about that
before today. On the other hand, the number of complaints we will
get if we change the format is likely to be more than zero.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

How about, the format of psql duration can be set via some ...
backslash command or commdn line switch? And the default of course
remains the current behavior?

bogdan

Andres Freund <andres@2ndquadrant.com> writes:

On 2014-09-28 20:32:30 -0400, Gregory Smith wrote:

On 9/28/14, 7:49 AM, Bogdan Pilch wrote:

I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way time spent executing the SQL
commands is printed out.

There are already a wide range of human readable time interval output
formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE

He's talking about psql's \timing...

Indeed. Still, it seems like this has more downside than upside.
It seems likely to break some peoples' scripts, and where exactly
is the groundswell of complaint that the existing format is
unreadable? TBH, I've not heard even one complaint about that
before today. On the other hand, the number of complaints we will
get if we change the format is likely to be more than zero.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Please don't top post, initial context is important, especially Tom's! :-)
(see my reply below)

On 01/10/14 03:52, Bogdan Pilch wrote:

How about, the format of psql duration can be set via some ...
backslash command or commdn line switch? And the default of course
remains the current behavior?

bogdan

Andres Freund <andres@2ndquadrant.com> writes:

On 2014-09-28 20:32:30 -0400, Gregory Smith wrote:

On 9/28/14, 7:49 AM, Bogdan Pilch wrote:

I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way time spent executing the SQL
commands is printed out.

There are already a wide range of human readable time interval output
formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE

He's talking about psql's \timing...

Indeed. Still, it seems like this has more downside than upside.
It seems likely to break some peoples' scripts, and where exactly
is the groundswell of complaint that the existing format is
unreadable? TBH, I've not heard even one complaint about that
before today. On the other hand, the number of complaints we will
get if we change the format is likely to be more than zero.

regards, tom lane

As a user, your suggestion is fine by me.

Cheers,
Gavin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 9/29/14, 1:08 AM, Andres Freund wrote:

On 2014-09-28 20:32:30 -0400, Gregory Smith wrote:

There are already a wide range of human readable time interval output
formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE

He's talking about psql's \timing...

I got that. My point was that even though psql's timing report is kind
of a quick thing hacked into there, if it were revised I'd expect two
things will happen eventually:

-Asking if any of the the interval conversion code can be re-used for
this purpose, rather than adding yet another custom to one code path
"standard".

-Asking if this should really just be treated like a full interval
instead, and then overlapping with a significant amount of that baggage
so that you have all the existing format choices.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 9/29/14, 1:08 AM, Andres Freund wrote:

On 2014-09-28 20:32:30 -0400, Gregory Smith wrote:

There are already a wide range of human readable time interval output
formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE

He's talking about psql's \timing...

I got that. My point was that even though psql's timing report is
kind of a quick thing hacked into there, if it were revised I'd
expect two things will happen eventually:

-Asking if any of the the interval conversion code can be re-used
for this purpose, rather than adding yet another custom to one code
path "standard".

-Asking if this should really just be treated like a full interval
instead, and then overlapping with a significant amount of that
baggage so that you have all the existing format choices.

That's actually a good idea.
So what you're sayig is that if I come up with some nice way of
setting customized time output format, keeping the default the way it
is now, then it would be worth considering?

Now I understand why it says that a discussion is recommended before
implementing and posting. ;-)

bogdan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers