Thread: gettime() - a timeofday() alternative
Hi all, I propose to add an internal function gettime() that transparently returns the current system time, as a timestamptz with maximum precision. Calling gettime() would be a more elegant approach than calling timeofday() and converting it to a timestamp, and avoids some of the potential problems in that conversion (such as "Sat" being misinterpreted as an Australian timezone). I'm open to alternate suggestions for the name of the function. If there are no objections, I'll start cooking up a patch right away. -- BJ
Brendan Jurd wrote: > Hi all, > > I propose to add an internal function gettime() that transparently > returns the current system time, as a timestamptz with maximum > precision. > > Calling gettime() would be a more elegant approach than calling > timeofday() and converting it to a timestamp, and avoids some of the > potential problems in that conversion (such as "Sat" being > misinterpreted as an Australian timezone). > > I'm open to alternate suggestions for the name of the function. > > If there are no objections, I'll start cooking up a patch right away. We already have a TODO for this:* Add transaction_timestamp(), statement_timestamp(), clock_timestamp() functionality CurrentCURRENT_TIMESTAMP returns the start time of the current transaction, and gettimeofday() returns the wallclock time.This will make time reporting more consistent and will allow reporting of the statement start time. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> We already have a TODO for this: > > * Add transaction_timestamp(), statement_timestamp(), clock_timestamp() > functionality I like the idea of having a function for statement start time. I think I'll incorporate it into my patch. The suggested naming convention in the TODO is good as well. I'd be inclined to make those *_timestamp() functions the core functions, and then make things like now() and gettime() shorthand equivalents.
On 8/14/05, Bruce Momjian <pgman@candle.pha.pa.us> wrote: > Brendan Jurd wrote: > > > We already have a TODO for this: > > > > > > * Add transaction_timestamp(), statement_timestamp(), clock_timestamp() > > > functionality > > > > I like the idea of having a function for statement start time. I > > think I'll incorporate it into my patch. > > Regarding the statement_timestamp() ... if the entire query path is parser -> rewriter -> planner/optimiser -> executor, what point in that path would be considered the true start of the "statement"? I would assume that it's right at the beginning, when it is first handed to the parser, but that's a very naive assumption.
On Sun, Aug 14, 2005 at 05:51:16AM +1000, Brendan Jurd wrote:
> Regarding the statement_timestamp() ... if the entire query path is
> parser -> rewriter -> planner/optimiser -> executor, what point in
> that path would be considered the true start of the "statement"? I
> would assume that it's right at the beginning, when it is first handed
> to the parser, but that's a very naive assumption.
The latest time before any index or table has been accessed to derive
results?
For prepared statements, for example, the time the statement was prepared
doesn't seem useful to me.
Cheers,
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness
bindthem...
http://mark.mielke.cc/
Brendan Jurd <direvus@gmail.com> writes:
> Regarding the statement_timestamp() ... if the entire query path is
> parser -> rewriter -> planner/optimiser -> executor, what point in
> that path would be considered the true start of the "statement"?
IIRC, what we actually intended that to mean is the time of receipt of
the current interactive command --- that is, it gets set in the
postgres.c outer loop, not anywhere in the parser/etc path. Otherwise
there's not a unique answer (consider statements issued inside SQL
functions for instance).
regards, tom lane
Jim C. Nasby wrote: > On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote: > > Brendan Jurd <direvus@gmail.com> writes: > > > Regarding the statement_timestamp() ... if the entire query path is > > > parser -> rewriter -> planner/optimiser -> executor, what point in > > > that path would be considered the true start of the "statement"? > > > > IIRC, what we actually intended that to mean is the time of receipt of > > the current interactive command --- that is, it gets set in the > > postgres.c outer loop, not anywhere in the parser/etc path. Otherwise > > there's not a unique answer (consider statements issued inside SQL > > functions for instance). > > ISTM that it would be useful to be able to use timestamp_statement > within a function though... although I guess timestamp_clock might > suffice in most cases. Another consideration is that this is a potential > source of confusion; people could easily think that timestamp_statement > would operate the same inside a function as it would outside. > > Would it be reasonable to add one more timestamp that works the same > inside and outside a function? In either case, can anyone think of a > less-ambiguous name for timestamp_statement? timestamp_client_statement? That highlights it is when the client sends the statement. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Jim C. Nasby wrote:
>> On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote:
>>> IIRC, what we actually intended that to mean is the time of receipt of
>>> the current interactive command --- that is, it gets set in the
>>> postgres.c outer loop, not anywhere in the parser/etc path. Otherwise
>>> there's not a unique answer (consider statements issued inside SQL
>>> functions for instance).
>> Would it be reasonable to add one more timestamp that works the same
>> inside and outside a function? In either case, can anyone think of a
>> less-ambiguous name for timestamp_statement?
> timestamp_client_statement? That highlights it is when the client sends
> the statement.
timestamp_command, maybe, would convey the right image.
(I don't think we need yet a fourth flavor of this, nor do I see anything
about it that "works differently inside and outside a function".)
regards, tom lane
On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote: > Brendan Jurd <direvus@gmail.com> writes: > > Regarding the statement_timestamp() ... if the entire query path is > > parser -> rewriter -> planner/optimiser -> executor, what point in > > that path would be considered the true start of the "statement"? > > IIRC, what we actually intended that to mean is the time of receipt of > the current interactive command --- that is, it gets set in the > postgres.c outer loop, not anywhere in the parser/etc path. Otherwise > there's not a unique answer (consider statements issued inside SQL > functions for instance). ISTM that it would be useful to be able to use timestamp_statement within a function though... although I guess timestamp_clock might suffice in most cases. Another consideration is that this is a potential source of confusion; people could easily think that timestamp_statement would operate the same inside a function as it would outside. Would it be reasonable to add one more timestamp that works the same inside and outside a function? In either case, can anyone think of a less-ambiguous name for timestamp_statement? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Jim C. Nasby wrote: > >> On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote: > >>> IIRC, what we actually intended that to mean is the time > of receipt of > >>> the current interactive command --- that is, it gets set in the > >>> postgres.c outer loop, not anywhere in the parser/etc > path. Otherwise > >>> there's not a unique answer (consider statements issued inside SQL > >>> functions for instance). > > >> Would it be reasonable to add one more timestamp that > works the same > >> inside and outside a function? In either case, can anyone > think of a > >> less-ambiguous name for timestamp_statement? > > > timestamp_client_statement? That highlights it is when the > client sends > > the statement. > > timestamp_command, maybe, would convey the right image. > > (I don't think we need yet a fourth flavor of this, nor do I > see anything > about it that "works differently inside and outside a function".) Here's what I thought was going to happen: psql> BEGIN; -- sets timestamp_transaction(); psql> SELECT func1(); -- sets timestamp_statement() func1: SELECT something; -- doesn't set timestamp_statement(), because it's in a function call func2(); return; psql> SELECT something; -- sets timestamp_statement() again Maybe I just mis-understood and each statement that's issued will update timestamp_statement(). If that's the case I thinkwe're fine (I can't really think of a use-case for timestamp_command() myself...). OTOH, if the intention is to do what I outlined in the above timeline, I think we should also have timestamp_command(), andchange timestamp_statement() so that it always indicates the timestamp at the start of the current statement. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461