Re: [HACKERS] Time measurement format - more human readable
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
Re: [HACKERS] Time measurement format - more human readable
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
Re: [HACKERS] Time measurement format - more human readable
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 and...@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
Re: [HACKERS] Time measurement format - more human readable
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 and...@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
[HACKERS] 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
From 25b2e3f9d888ecf0cc6fe0fbb569004cf9ce315b Mon Sep 17 00:00:00 2001
From: Bogdan Pilch bogdan.pi...@opensynergy.com
Date: Sat, 16 Aug 2014 23:20:18 +0200
Subject: [PATCH] BPI: Implemented enhancement f timing feature (displaying
time in a more readable way).
---
src/bin/psql/common.c | 75 +--
1 file changed, 73 insertions(+), 2 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index c08c813..9c7f1ff 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -25,10 +25,18 @@
#include mbprint.h
+#define TIMING_BUFFER_SIZE 64
+
+#define SECONDS_DENOMINATOR (1000.0)
+#define MINUTES_DENOMINATOR (60.0 * SECONDS_DENOMINATOR)
+#define HOURS_DENOMINATOR (60.0 * MINUTES_DENOMINATOR)
+#define DAYS_DENOMINATOR (24.0 * HOURS_DENOMINATOR)
+
static bool ExecQueryUsingCursor(const char *query, double *elapsed_msec);
static bool command_no_begin(const char *query);
static bool is_select_command(const char *query);
+void ms2str_format(double intime, char *out);
/*
* setQFout
@@ -880,6 +888,7 @@ SendQuery(const char *query)
bool OK = false;
bool on_error_rollback_savepoint = false;
static bool on_error_rollback_warning = false;
+ char timing_buf[TIMING_BUFFER_SIZE];
if (!pset.db)
{
@@ -1063,8 +1072,11 @@ SendQuery(const char *query)
PQclear(results);
/* Possible microtiming output */
- if (pset.timing)
- printf(_(Time: %.3f ms\n), elapsed_msec);
+ if (pset.timing) {
+ ms2str_format(elapsed_msec, timing_buf);
+ //printf(_(Time: %.3f ms\n), elapsed_msec);
+ printf(_(Time: %s\n), timing_buf);
+ }
/* check for events that may occur during query execution */
@@ -1748,3 +1760,62 @@ expand_tilde(char **filename)
return;
}
+
+/*
+ * Fill in the supplied buffer with nice time broken down to dd:hh:mm:ss:ms.us
+ *
+ */
+void ms2str_format(double intime, char *out)
+{
+ int days, hours, minutes, seconds;
+ double ms;
+
+ days = (int) (intime / (DAYS_DENOMINATOR));
+ intime -= ((double)days * DAYS_DENOMINATOR);
+ hours = (int) (intime / (HOURS_DENOMINATOR));
+ intime -= ((double)hours * HOURS_DENOMINATOR);
+ minutes = (int) (intime / (MINUTES_DENOMINATOR));
+ intime -= ((double)minutes * MINUTES_DENOMINATOR);
+ seconds = (int) (intime / (SECONDS_DENOMINATOR));
+ intime -= ((double)seconds * SECONDS_DENOMINATOR);
+ ms = (intime);
+
+ if (days 0)
+ sprintf(out, %d_%d:%d:%d:%.02f d_h:m:s:ms, days, hours, minutes, seconds, ms);
+ else if (hours 0)
+ sprintf(out, %d:%d:%d:%.02f h:m:s:ms, hours, minutes, seconds, ms);
+ else if (minutes 0)
+ sprintf(out, %d:%d:%.02f m:s:ms, minutes, seconds, ms);
+ else if (seconds 0)
+ sprintf(out, %d:%.02f s:ms, seconds, ms);
+ else
+ sprintf(out, %.02f ms, ms);
+}
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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
Re: [HACKERS] Time measurement format - more human readable
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.sm...@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
Re: [HACKERS] Time measurement format - more human readable
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
Re: [HACKERS] Time measurement format - more human readable
Andres Freund and...@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
