Thread: Time measurement format - more human readable
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
Attachment
<div class="moz-cite-prefix">On 29/09/14 00:49, Bogdan Pilch wrote:<br /></div><blockquote cite="mid:20140928114951.GB22150@artax.karlin.mff.cuni.cz"type="cite"><pre wrap="">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 </pre><br /><fieldset class="mimeAttachmentHeader"></fieldset><br /><pre wrap=""> </pre></blockquote> If this is a forced, and not optional, then I think it is a backward step. IMnsHO<br /><br /> For programmaticanalysis: either <milis>.<micros> or the number, of seconds (with a fractional part), would be okay.<br/><br /> I would be happy if there was a configuration parameter to control it. At least a simple boolean to choosebetween the new & old format - but better still, would be a time format string to allow people to choose the representationthey consider most appropriate for their own needs.<br /><br /> Having a configuration parameter set to theoriginal format, would also avoid unnecessary backwards compatibility problems!<br /><br /><br /> Cheers,<br /> Gavin<br/>
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/
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
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
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
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
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.
> 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