On Mon, May 20, 2013 at 7:33 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Jeff Janes <jeff.ja...@gmail.com> writes:
>> I'd like to run same query repeatedly and see how long it takes each time.
>> I thought \watch would be excellent for this, but it turns out that using
>> \watch suppresses the output of \timing.
>
>> Is this intentional, or unavoidable?
>
> \watch uses PSQLexec not SendQuery; the latter implements \timing which
> I agree is arguably useful here, but also autocommit/auto-savepoint
> behavior which probably isn't a good idea.
>
> It might be a good idea to refactor those two routines into one routine
> with some sort of bitmap flags argument to control the various add-on
> behaviors, but that seems like not 9.3 material anymore.
Attached patch changes \watch so that it displays how long the query takes
if \timing is enabled.
I didn't refactor PSQLexec and SendQuery into one routine because
the contents of those functions are not so same. I'm not sure how much
it's worth doing that refactoring. Anyway this feature is quite useful
even without that refactoring, I think.
BTW, I found that \watch doesn't check for async notifications. Is it useful
to allow \watch to do that? ISTM that it's not so bad idea to use \timing
to continuously check for async notifications. No?
Regards,
--
Fujii Masao
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***************
*** 2690,2695 **** do_watch(PQExpBuffer query_buf, long sleep)
--- 2690,2696 ----
PGresult *res;
time_t timer;
long i;
+ double elapsed_msec = 0;
/*
* Prepare title for output. XXX would it be better to use the time
***************
*** 2701,2710 **** do_watch(PQExpBuffer query_buf, long sleep)
myopt.title = title;
/*
! * Run the query. We use PSQLexec, which is kind of cheating, but
! * SendQuery doesn't let us suppress autocommit behavior.
*/
! res = PSQLexec(query_buf->data, false);
/* PSQLexec handles failure results and returns NULL */
if (res == NULL)
--- 2702,2711 ----
myopt.title = title;
/*
! * Run the query. We use PSQLexecInternal, which is kind of cheating,
! * but SendQuery doesn't let us suppress autocommit behavior.
*/
! res = PSQLexecInternal(query_buf->data, false, &elapsed_msec);
/* PSQLexec handles failure results and returns NULL */
if (res == NULL)
***************
*** 2755,2760 **** do_watch(PQExpBuffer query_buf, long sleep)
--- 2756,2765 ----
fflush(pset.queryFout);
+ /* Possible microtiming output */
+ if (pset.timing)
+ printf(_("Time: %.3f ms\n"), elapsed_msec);
+
/*
* Set up cancellation of 'watch' via SIGINT. We redo this each time
* through the loop since it's conceivable something inside PSQLexec
*** a/src/bin/psql/common.c
--- b/src/bin/psql/common.c
***************
*** 439,445 **** AcceptResult(const PGresult *result)
--- 439,459 ----
PGresult *
PSQLexec(const char *query, bool start_xact)
{
+ return PSQLexecInternal(query, start_xact, NULL);
+ }
+
+ /*
+ * Send "backdoor" queries.
+ *
+ * Measure how long the given query takes if elapsed_msec is not NULL and
+ * \timing is enabled.
+ */
+ PGresult *
+ PSQLexecInternal(const char *query, bool start_xact, double *elapsed_msec)
+ {
PGresult *res;
+ instr_time before;
+ instr_time after;
if (!pset.db)
{
***************
*** 483,488 **** PSQLexec(const char *query, bool start_xact)
--- 497,505 ----
PQclear(res);
}
+ if (elapsed_msec != NULL && pset.timing)
+ INSTR_TIME_SET_CURRENT(before);
+
res = PQexec(pset.db, query);
ResetCancelConn();
***************
*** 493,498 **** PSQLexec(const char *query, bool start_xact)
--- 510,522 ----
res = NULL;
}
+ if (elapsed_msec != NULL && pset.timing)
+ {
+ INSTR_TIME_SET_CURRENT(after);
+ INSTR_TIME_SUBTRACT(after, before);
+ *elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
+ }
+
return res;
}
*** a/src/bin/psql/common.h
--- b/src/bin/psql/common.h
***************
*** 37,42 **** extern void SetCancelConn(void);
--- 37,44 ----
extern void ResetCancelConn(void);
extern PGresult *PSQLexec(const char *query, bool start_xact);
+ extern PGresult *PSQLexecInternal(const char *query, bool start_xact,
+ double *elapsed_msec);
extern bool SendQuery(const char *query);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
