Thread: Re: [GENERAL] Monitoring a Query
Neil Conway wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Aaron Held wrote: > > > Is there any way to monitor a long running query? > > > > Oh, sorry, you want to know how far the query has progressed. Gee, I > > don't think there is any easy way to do that. > > Would it be a good idea to add the time that the current query began > execution at to pg_stat_activity? What do people think about this? It seems like a good idea to me. -- 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, Pennsylvania 19073
Bruce Momjian wrote: > Neil Conway wrote: > >>Bruce Momjian <pgman@candle.pha.pa.us> writes: >> >>>Aaron Held wrote: >>> >>>>Is there any way to monitor a long running query? >>> >>>Oh, sorry, you want to know how far the query has progressed. Gee, I >>>don't think there is any easy way to do that. >> >>Would it be a good idea to add the time that the current query began >>execution at to pg_stat_activity? > > > What do people think about this? It seems like a good idea to me. > My application marks the start time of each query and I have found it very useful. The users like to see how long each query took, and the admin can take a quick look and see how many queries are running and how long each has been active for. Good for debugging and billing. -Aaron Held
On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote: > > > > Would it be a good idea to add the time that the current query began > > execution at to pg_stat_activity? > > What do people think about this? It seems like a good idea to me. OpenACS has a package called "Developer Support" that shows you (among other things) how long a query took to be executed. Very good to finding out slow-running queries that need to be optimized. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
It looks like that just timestamps things in its connection pool, that is what I do now. What I would like is to know about queries that have not finished yet. -Aaron Roberto Mello wrote: > On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote: > >>>Would it be a good idea to add the time that the current query began >>>execution at to pg_stat_activity? >> >>What do people think about this? It seems like a good idea to me. > > > OpenACS has a package called "Developer Support" that shows you (among > other things) how long a query took to be executed. Very good to finding > out slow-running queries that need to be optimized. > > -Roberto >
Roberto Mello wrote: > On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote: > > > > > > Would it be a good idea to add the time that the current query began > > > execution at to pg_stat_activity? > > > > What do people think about this? It seems like a good idea to me. > > OpenACS has a package called "Developer Support" that shows you (among > other things) how long a query took to be executed. Very good to finding > out slow-running queries that need to be optimized. 7.3 will have GUC 'log_duration' which will show query duration. -- 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, Pennsylvania 19073
Aaron Held wrote:
> It looks like that just timestamps things in its connection pool, that
> is what I do now.
>
> What I would like is to know about queries that have not finished yet.
OK, added to TODO:
* Add start time to pg_stat_activity
Should we supply the current duration too? That value would change on
each call. Seems redundant.
--
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, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, added to TODO:
> * Add start time to pg_stat_activity
It would be nearly free to include the start time of the current
transaction, because we already save that for use by now(). Is
that good enough, or do we need start time of the current query?
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > OK, added to TODO: > > * Add start time to pg_stat_activity > > It would be nearly free to include the start time of the current > transaction, because we already save that for use by now(). Is > that good enough, or do we need start time of the current query? Current query, I am afraid. We could optimize it so single-query transactions wouldn't need to call that again. -- 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, Pennsylvania 19073
On Mon, 23 Sep 2002 11:06:19 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
>Tom Lane wrote:
>> It would be nearly free to include the start time of the current
>> transaction, because we already save that for use by now(). Is
>> that good enough, or do we need start time of the current query?
>
>Current query, I am afraid. We could optimize it so single-query
>transactions wouldn't need to call that again.
This has been discussed before and I know I'm going to get flamed for
this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP)
return the start time of the current transaction is a bug, or at least
it is not conforming to the standard.
SQL92 says in 6.8 <datetime value function>:
General Rules
1) The <datetime value function>s CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP respectively return the current date, current
time, and current timestamp [...]
^^^^^^^
3) If an SQL-statement generally contains more than one reference
^^^^^^^^^
to one or more <datetime value function>s, then all such ref-
erences are effectively evaluated simultaneously. The time of
evaluation of the <datetime value function> during the execution
^^^^^^
of the SQL-statement is implementation-dependent.
SQL99 says in 6.19 <datetime value function>:
3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value
function>s that are generally contained, without an intervening
<routine invocation> whose subject routines do not include an
SQL function, in <value expression>s that are contained either
in S without an intervening <SQL procedure statement> or in an
<SQL procedure statement> contained in the <triggered action>
of a trigger activated as a consequence of executing S, are
effectively evaluated simultaneously. The time of evaluation of
a <datetime value function> during the execution of S and its
activated triggers is implementation-dependent.
I cannot say that I fully understand the second sentence (guess I have
to read it for another 100 times), but "during the execution of S"
seems to mean "not before the start and not after the end of S".
What do you think?
Servus
Manfred
Manfred Koizar <mkoi-pg@aon.at> writes:
> This has been discussed before and I know I'm going to get flamed for
> this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP)
> return the start time of the current transaction is a bug, or at least
> it is not conforming to the standard.
As you say, it's been discussed before. We concluded that the spec
defines the behavior as implementation-dependent, and therefore we
can pretty much do what we want.
If you want exact current time, there's always timeofday().
regards, tom lane
On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: >Manfred Koizar <mkoi-pg@aon.at> writes: >> This has been discussed before and I know I'm going to get flamed for >> this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP) >> return the start time of the current transaction is a bug, or at least >> it is not conforming to the standard. > >As you say, it's been discussed before. Yes, and I hate to be annoying. >We concluded that the spec defines the behavior as >implementation-dependent, AFAICT the spec requires the returned value to meet two conditions. C1: If a statement contains more than one <datetime value function>, they all have to return (maybe different formats of) the same value. C2: The returned value has to represent a point in time *during* the execution of the SQL-statement. The only thing an implementor is free to choose is which point in time "during the execution of the SQL-statement" is to be returned, i.e. a timestamp in the interval between the start of the statement and the first time when the value is needed. The current implementation only conforms to C1. >and therefore we can pretty much do what we want. Start time of the statement, ... of the transaction, ... of the session, ... of the postmaster, ... of the century? I understand that with subselects, functions, triggers, rules etc. it is not easy to implement the specification. If we can't do it now, we should at least add a todo and make clear in the documentation that CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant. Servus Manfred
Manfred, > C2: The returned value has to represent a point in time *during* the > execution of the SQL-statement. > > The only thing an implementor is free to choose is which point in time > "during the execution of the SQL-statement" is to be returned, i.e. a > timestamp in the interval between the start of the statement and the > first time when the value is needed. > > The current implementation only conforms to C1. I, for one, would judge that the start time of the statement is "during the execution"; it would only NOT be "during the execution" if it was a value *before* the start time of the statement. It's a semantic argument. The spec is, IMHO, rather vague on how this would relate to transactions. I do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a transaction to be an extension of an individual SQL statement for this purpose (at least, that's what I guess they did). Thus, if you accept the postulates that: 1) "During" a SQL statement includes the start time of the statement, and 2) A Transaction is the equivalent of a single SQL statement for many purposes, Then the current behavior is a logical conclusion. Further, we could not change that behaviour without breaking many people's applications. Ideally, since we get this question a lot, that a compile-time or execution-time switch to change the behavior of current_timestamp contextually would be nice. We just need someone who;s interested enough in writing one. -- -Josh BerkusAglio Database SolutionsSan Francisco
Josh Berkus wrote: > I, for one, would judge that the start time of the statement is "during the > execution"; it would only NOT be "during the execution" if it was a value > *before* the start time of the statement. It's a semantic argument. > > The spec is, IMHO, rather vague on how this would relate to transactions. I > do not find it at all inconsitent that Bruce, Thomas, and co. interpreted a > transaction to be an extension of an individual SQL statement for this > purpose (at least, that's what I guess they did). > > Thus, if you accept the postulates that: > 1) "During" a SQL statement includes the start time of the statement, and > 2) A Transaction is the equivalent of a single SQL statement for many > purposes, > Then the current behavior is a logical conclusion. > > Further, we could not change that behaviour without breaking many people's > applications. I don't see how we can defend returning the start of the transaction as the current_timestamp. In a multi-statement transaction, that doesn't seem very current to me. I know there are some advantages to returning the same value for all queries in a transaction, but is that value worth returning such stale time information? -- 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, > I don't see how we can defend returning the start of the transaction as > the current_timestamp. In a multi-statement transaction, that doesn't > seem very current to me. I know there are some advantages to returning > the same value for all queries in a transaction, but is that value worth > returning such stale time information? Then what *was* the reasoning behind the current behavior? -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Josh Berkus wrote: > > Bruce, > > > I don't see how we can defend returning the start of the transaction as > > the current_timestamp. In a multi-statement transaction, that doesn't > > seem very current to me. I know there are some advantages to returning > > the same value for all queries in a transaction, but is that value worth > > returning such stale time information? > > Then what *was* the reasoning behind the current behavior? I thought the spec required it, but now that I see it doesn't, I don't know why it was done that way. -- 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:
> I don't see how we can defend returning the start of the transaction as
> the current_timestamp.
Here's an example:
CREATE RULE foo AS ON INSERT TO mytable DO
( INSERT INTO log1 VALUES (... , now(), ...); INSERT INTO log2 VALUES (... , now(), ...) );
I think it's important that these commands store the same timestamp in
both log tables (not to mention that any now() being stored into mytable
itself generate that same timestamp).
If you scale that up just a little bit, you can devise scenarios where
successive client-issued commands (within a single transaction) want to
store the same timestamp. After all, it's only a minor implementation
detail that you chose to fire these logging operations via a rule and
not by client-side logic.
In short, there are plenty of situations where it's critical for
application correctness that a series of commands all be able to operate
with the same value of now(). I don't think that it's wise for Postgres
to try to decide where within a transaction it's safe to advance now().
That will inevitably break some applications, and it's not obvious what
the benefit is.
In short: if you want exact current time, there's timeofday(). If you
want start of transaction time, we've got that. If you want start of
current statement time, I have two questions: why, and exactly how do
you want to define current statement, considering functions, rules,
triggers, and all that other stuff that makes it interesting?
ISTM that if a client or function wants to record intratransaction
times, it can call timeofday() at the appropriate points for itself.
regards, tom lane
On Mon, Sep 23, 2002 at 10:48:30AM -0400, Bruce Momjian wrote: > > > > > > > > Would it be a good idea to add the time that the current query began > > > > execution at to pg_stat_activity? > > > > > > What do people think about this? It seems like a good idea to me. > > > > OpenACS has a package called "Developer Support" that shows you (among > > other things) how long a query took to be executed. Very good to finding > > out slow-running queries that need to be optimized. > > 7.3 will have GUC 'log_duration' which will show query duration. Forgive my ignorance here, but what is GUC? And how would I access the query duration? -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Q: What is purple and commutes? A: A boolean grape.
I see what you are saying now --- that even single user statements can trigger multiple statements, so you would have to say transaction start time is time the user query starts. I can see how that seems a little arbitrary. However, don't we have separate paths for user queries and queries sent as part of a rule? --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I don't see how we can defend returning the start of the transaction as > > the current_timestamp. > > Here's an example: > > CREATE RULE foo AS ON INSERT TO mytable DO > ( INSERT INTO log1 VALUES (... , now(), ...); > INSERT INTO log2 VALUES (... , now(), ...) ); > > I think it's important that these commands store the same timestamp in > both log tables (not to mention that any now() being stored into mytable > itself generate that same timestamp). > > If you scale that up just a little bit, you can devise scenarios where > successive client-issued commands (within a single transaction) want to > store the same timestamp. After all, it's only a minor implementation > detail that you chose to fire these logging operations via a rule and > not by client-side logic. > > In short, there are plenty of situations where it's critical for > application correctness that a series of commands all be able to operate > with the same value of now(). I don't think that it's wise for Postgres > to try to decide where within a transaction it's safe to advance now(). > That will inevitably break some applications, and it's not obvious what > the benefit is. > > In short: if you want exact current time, there's timeofday(). If you > want start of transaction time, we've got that. If you want start of > current statement time, I have two questions: why, and exactly how do > you want to define current statement, considering functions, rules, > triggers, and all that other stuff that makes it interesting? > > ISTM that if a client or function wants to record intratransaction > times, it can call timeofday() at the appropriate points for itself. > > regards, tom lane > -- 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
Roberto Mello wrote: > On Mon, Sep 23, 2002 at 10:48:30AM -0400, Bruce Momjian wrote: > > > > > > > > > > Would it be a good idea to add the time that the current query began > > > > > execution at to pg_stat_activity? > > > > > > > > What do people think about this? It seems like a good idea to me. > > > > > > OpenACS has a package called "Developer Support" that shows you (among > > > other things) how long a query took to be executed. Very good to finding > > > out slow-running queries that need to be optimized. > > > > 7.3 will have GUC 'log_duration' which will show query duration. > > Forgive my ignorance here, but what is GUC? And how would I access the > query duration? GUC is postgresql.conf and SET commands. They are variables that can be set. -- 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, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I see what you are saying now --- that even single user statements can
> trigger multiple statements, so you would have to say transaction start
> time is time the user query starts. I can see how that seems a little
> arbitrary. However, don't we have separate paths for user queries and
> queries sent as part of a rule?
We could use "time of arrival of the latest client command string",
if we wanted to do something like this. My point is that that very
arbitrarily assumes that those are the significant points within a
transaction, and that the client has no need to send multiple commands
that want to insert the same timestamp into different tables. This is
an unwarranted assumption about the client's control structure, IMHO.
A possible compromise is to dissociate now() and current_timestamp,
allowing the former to be start of transaction and the latter to be
start of client command.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I see what you are saying now --- that even single user statements can > > trigger multiple statements, so you would have to say transaction start > > time is time the user query starts. I can see how that seems a little > > arbitrary. However, don't we have separate paths for user queries and > > queries sent as part of a rule? > > We could use "time of arrival of the latest client command string", > if we wanted to do something like this. My point is that that very > arbitrarily assumes that those are the significant points within a > transaction, and that the client has no need to send multiple commands > that want to insert the same timestamp into different tables. This is > an unwarranted assumption about the client's control structure, IMHO. > > A possible compromise is to dissociate now() and current_timestamp, > allowing the former to be start of transaction and the latter to be > start of client command. I was thinking 'transaction_timestamp' for the transaction start time, and current_timestamp for the statement start time. I would equate now() with current_timestamp. -- 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
Tom, Bruce, > > A possible compromise is to dissociate now() and current_timestamp, > > allowing the former to be start of transaction and the latter to be > > start of client command. > > I was thinking 'transaction_timestamp' for the transaction start time, and > current_timestamp for the statement start time. I would equate now() > with current_timestamp. May I point out that this will break compatibility for those used to the current behavior? -- -Josh BerkusAglio Database SolutionsSan Francisco
Josh Berkus wrote: > > Tom, Bruce, > > > > A possible compromise is to dissociate now() and current_timestamp, > > > allowing the former to be start of transaction and the latter to be > > > start of client command. > > > > I was thinking 'transaction_timestamp' for the transaction start time, and > > current_timestamp for the statement start time. I would equate now() > > with current_timestamp. > > May I point out that this will break compatibility for those used to the > current behavior? I am not saying we have to make that change. My point is that our current behavior may not be the most intuitive, and that most people may prefer a change. Any such change would be documented in the release notes. -- 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 writes: > My point is that our current behavior may not be the most intuitive, and > that most people may prefer a change. I would prefer a change. -- John Hasler john@dhh.gt.org Dancing Horse Hill Elmwood, Wisconsin
John Hasler wrote: > Bruce Momjian writes: > > My point is that our current behavior may not be the most intuitive, and > > that most people may prefer a change. > > I would prefer a change. Yes, I guess that is my point, that we want to make transaction _and_ statement timestamp values available, but most people are going to use current_timestamp, and most people are going to assume it is statement time, not transaction time. Can I add TODO items for this: o Make CURRENT_TIMESTAMP/now() return statement start timeo Add TRANSACTION_TIMESTAMP to return transaction 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I was thinking 'transaction_timestamp' for the transaction start time, and
> current_timestamp for the statement start time. I would equate now()
> with current_timestamp.
So you want to both (a) invent even more nonstandard syntax than we
already have, and (b) break as many traditional-Postgres applications
as you possibly can?
'transaction_timestamp' has no reason to live. It's not in the spec.
And AFAIK the behavior of now() has been well-defined since the
beginning of Postgres. If you want to change 'current_timestamp' to
conform to a rather debatable reading of the spec, then fine --- but
keep your hands off of now().
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I was thinking 'transaction_timestamp' for the transaction start time, and > > current_timestamp for the statement start time. I would equate now() > > with current_timestamp. > > So you want to both (a) invent even more nonstandard syntax than we > already have, and (b) break as many traditional-Postgres applications > as you possibly can? No, but I would like to see you stop makeing condescending replies to emails. How is that! > 'transaction_timestamp' has no reason to live. It's not in the spec. > And AFAIK the behavior of now() has been well-defined since the > beginning of Postgres. If you want to change 'current_timestamp' to > conform to a rather debatable reading of the spec, then fine --- but > keep your hands off of now(). Oh, really. When you get down off your chair we can vote on it. -- 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:
> Can I add TODO items for this:
> o Make CURRENT_TIMESTAMP/now() return statement start time
> o Add TRANSACTION_TIMESTAMP to return transaction start time
I object to both of those as phrased. If you have already unilaterally
determined the design of this feature change, then go ahead and put that
in. But I'd suggest
o Revise current-time functions to allow access to statement start time
which doesn't presuppose the vote about how to do it.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Can I add TODO items for this: > > o Make CURRENT_TIMESTAMP/now() return statement start time > > o Add TRANSACTION_TIMESTAMP to return transaction start time > > I object to both of those as phrased. If you have already unilaterally > determined the design of this feature change, then go ahead and put that > in. But I'd suggest > > o Revise current-time functions to allow access to statement > start time > > which doesn't presuppose the vote about how to do it. OK, I am still just throwing out ideas. I am not sure we even have enough people who want statement_timestamp to put it in TODO. I do think we have a standards issue. My personal opinion is that most people think current_timestamp and now() are statement start time, not transaction start time. In the past we have told them the standard requires that but now I think we are not even sure if that is correct. So, I have these concerns: our CURRENT_TIMESTAMP may not be standards complianteven if it is, it is probably not returning the value most people wantmostpeople don't know it is returning the transaction start time So, we can just throw the TODO item you mentioned above with a question mark, or we can try to figure out what to return for CURRENT_TIMESTAMP, now(), and perhaps create a TRANSACTION_TIMESTAMP. So, do people want to discuss it or should we just throw it in TODO with a question mark? -- 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
On Tue, 24 Sep 2002 11:19:12 +1000, Martijn van Oosterhout
<kleptog@svana.org> wrote:
>Well, what I would suggest is that when you wrap several statements into a
>single transaction with begin/commit, the whole lot could be considered a
>single statement (since they form an atomic transaction so in a sense they
>are all executed simultaneously).
The people who wrote the specification knew about transactions. If
they had wanted what you describe above, they would have written:
3) If a transaction generally contains more than one reference
to one or more <datetime value function>s, then all such ref-
erences are effectively evaluated simultaneously. The time of
evaluation of the <datetime value function> during the execution
of the transaction is implementation-dependent.
But they wrote "SQL-statement", not "transaction".
>And hence Postgresql is perfectly compliant.
I'm not so sure.
>The current definition is, I would say, the most useful definition. Can you
>give an example where your definition would be more useful?
I did not write the standard, I'm only reading it. I have no problem
with an implementation that deviates from the standard "because we
know better". But we should users warn about this fact and not tell
them it is compliant.
Servus
Manfred
On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <josh@agliodbs.com> wrote: >I, for one, would judge that the start time of the statement is "during the >execution"; it would only NOT be "during the execution" if it was a value >*before* the start time of the statement. It's a semantic argument. Josh, you're right, I meant closed interval. >Further, we could not change that behaviour without breaking many people's >applications. > >Ideally, since we get this question a lot, that a compile-time or >execution-time switch to change the behavior of current_timestamp >contextually would be nice. Yes, GUC! >We just need someone who;s interested enough in >writing one. First we need someone who decyphers SQL99's wording. ServusManfred
On Mon, 23 Sep 2002 16:55:48 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: >Bruce Momjian <pgman@candle.pha.pa.us> writes: >Here's an example: > >CREATE RULE foo AS ON INSERT TO mytable DO >( INSERT INTO log1 VALUES (... , now(), ...); > INSERT INTO log2 VALUES (... , now(), ...) ); > >I think it's important that these commands store the same timestamp in >both log tables (not to mention that any now() being stored into mytable >itself generate that same timestamp). I agree. SQL99 mentions this requirement for triggers and I think we can apply it to rules as well. Here is another example: BEGIN; INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...); -- wait a few seconds INSERT INTO foo VALUES (..., CURRENT_TIMESTAMP, ...); COMMIT; Please don't ask me, why I would want that, but the standard demands the timestamps to be different. >After all, it's only a minor implementation >detail that you chose to fire these logging operations via a rule and >not by client-side logic. No, it's fundamentally different whether you do something in one SQL-statment or per a sequence of statements. ServusManfred
On Mon, 23 Sep 2002 23:35:13 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: >If you want to change 'current_timestamp' to >conform to a rather debatable reading of the spec, [...] Well the spec may be debatable, but could you please explain why my reading of the spec is debatable. The spec says "during the execution of the SQL-statement". You know English is not my first language, but as far as I have learned "during" does not mean "at any time before". ServusManfred
Manfred Koizar <mkoi-pg@aon.at> writes:
> On Mon, 23 Sep 2002 13:36:59 -0700, Josh Berkus <josh@agliodbs.com>
> wrote:
>> Ideally, since we get this question a lot, that a compile-time or
>> execution-time switch to change the behavior of current_timestamp
>> contextually would be nice.
> Yes, GUC!
I think a GUC variable is overkill, in fact potentially dangerous
(what if it's been changed without your app noticing)? I'm fine with
changing current_timestamp to be start-of-current-interactive-command,
though I'd not want to try to chop it more finely than that, for the
reasons already discussed. But I strongly feel that we should leave
the historical behavior of now() alone. There is no spec-based argument
for changing now(), since it isn't in the spec, and its behavior has
been set *and documented* in Postgres since Berkeley days.
If we leave now() alone then there's no need to create another
non-spec-compliant syntax like 'transaction_timestamp', either.
(I really don't want to see us do that, because without parens
it would mean making a new, not-in-the-spec fully-reserved word.)
BTW, as long as we are dorking with the current-time family, does
anyone want to vote for changing timeofday() to return a timestamptz
instead of a text string? There's no good argument except slavish
backward compatibility for having it return text, and we seem to be
quite willing to ignore backwards compatibility in this thread ...
regards, tom lane
>>>>> "Martijn" == Martijn van Oosterhout <kleptog@svana.org> writes:
Martijn> Well, what I would suggest is that when you wrap several
Martijn> statements into a single transaction with begin/commit,
Martijn> the whole lot could be considered a single statement
Martijn> (since they form an atomic transaction so in a sense they
Martijn> are all executed simultaneously). And hence Postgresql is
Martijn> perfectly compliant.
FWIW, and not that I am an Oracle fan :-), Oracle seems to interpret
this the same way when using a "select sysdate from dual" inside a
transaction.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Tom,
> If we leave now() alone then there's no need to create another
> non-spec-compliant syntax like 'transaction_timestamp', either.
> (I really don't want to see us do that, because without parens
> it would mean making a new, not-in-the-spec fully-reserved word.)
So, if I've got this straight:
-- current_timestamp will return the timestamp for the beginning of the
SQL statement.
-- now() will return the timestamp for the beginning of the
transaction.
-- timeofday() will return the timestamp of the exact time the function
is called.
... thus changing only current_timestamp.
This looks fine to me, as a search-and-replace on current_timestamp is
easy. However, we need to do a better job of warning people about the
change than we did with interval() to "interval"().
Actually, can I make the proposal that *any* change that breaks
backward compatibility be mentioned in both the new version
announcement and on the download page? This would prevent a lot of
grief. If I'm kept informed of these changes, I'll be happy to write
up a user-friendly announcement/instructions on how to cope with the
change.
> BTW, as long as we are dorking with the current-time family, does
> anyone want to vote for changing timeofday() to return a timestamptz
> instead of a text string? There's no good argument except slavish
> backward compatibility for having it return text, and we seem to be
> quite willing to ignore backwards compatibility in this thread ...
No, I don't see any reason to do this. It's not like timeofday() is a
particularly logical name, anyway. Why not introduce a new function,
rightnow(), that returns timestamptz?
Better yet, how about we introduce a parameter to now()? Example:
now() or now('transaction') returns the transaction timestamp.
now('statement') returns the statement timestamp
now('immediate') returns the timestamp at the exact time the function
is called.
This would seem to me much more consistent than having 3 different
time-calls, whose names have nothing to do with the difference between
them. And it has the advantage of not breaking backward compatibility.
We could introduce the new version of now() in 7.4, encourage everyone
to use it instead of other timestamp calls, and then in 7.5 change the
behavior of current_timestamp for SQL92 compliance.
-Josh Berkus
On Tue, Sep 24, 2002 at 10:55:41AM -0400, Roland Roberts wrote: > >>>>> "Martijn" == Martijn van Oosterhout <kleptog@svana.org> writes: > > Martijn> Well, what I would suggest is that when you wrap several > Martijn> statements into a single transaction with begin/commit, > Martijn> the whole lot could be considered a single statement > Martijn> (since they form an atomic transaction so in a sense they > Martijn> are all executed simultaneously). And hence Postgresql is > Martijn> perfectly compliant. > > FWIW, and not that I am an Oracle fan :-), Oracle seems to interpret > this the same way when using a "select sysdate from dual" inside a > transaction. Oh, interesting datapoint. Let me get this clear - on oracle, the equivalent of: BEGIN; SELECT current_timestamp; <go off to lunch, come back> SELECT current_timestamp; END; will give two identical timestamps? Ross
On Tue, Sep 24, 2002 at 08:05:59AM -0700, Josh Berkus wrote: > > This looks fine to me, as a search-and-replace on current_timestamp is > easy. However, we need to do a better job of warning people about the > change than we did with interval() to "interval"(). > > Actually, can I make the proposal that *any* change that breaks > backward compatibility be mentioned in both the new version > announcement and on the download page? This would prevent a lot of > grief. If I'm kept informed of these changes, I'll be happy to write > up a user-friendly announcement/instructions on how to cope with the > change. I'd suggest we (for values of we that probably resolve to Bruce or a Bruce triggered Josh ;-) start a new doc, right now, for 7.4_USER_VISIBLE_CHANGES, or some other, catchy title. In it, document, with example SQL snippets, if need be, the change from previous behavior, _when the patch is committed_. In fact, y'all could be hardnosed about not accepting a user visible syntax changing patch without it touching this file. Such a document would be invaluable for database migration. On another note, this discussion is happening on GENERAL and SQL, but is getting pretty technical - should someone more it to HACKERS to get input from developers who don't hang out here? Ross
Josh Berkus writes:
> now() or now('transaction') returns the transaction timestamp.
> now('statement') returns the statement timestamp now('immediate') returns
> the timestamp at the exact time the function is called.
I like that.
IMHO "the exact time the function is called" is what most people would
expect to get from now(), but it's too late for that.
--
John Hasler
john@dhh.gt.org
Dancing Horse Hill
Elmwood, Wisconsin
"Josh Berkus" <josh@agliodbs.com> writes:
> So, if I've got this straight:
> [ snip ]
> ... thus changing only current_timestamp.
Yeah, that's more or less what I was thinking. The argument for
changing current_timestamp seems to be really just spec compliance;
that doesn't apply to now() or timeofday().
> Better yet, how about we introduce a parameter to now()? Example:
> now() or now('transaction') returns the transaction timestamp.
> now('statement') returns the statement timestamp
> now('immediate') returns the timestamp at the exact time the function
> is called.
I like this.
> We could introduce the new version of now() in 7.4, encourage everyone
> to use it instead of other timestamp calls, and then in 7.5 change the
> behavior of current_timestamp for SQL92 compliance.
I'd be inclined to just do it; we have not been very good about
following through on multi-version sequences of changes. And the
folks who want a standard-compliant current_timestamp aren't going
to want to migrate to now('statement') instead ...
regards, tom lane
>>>>> "Ross" == Ross J Reedstrom <reedstrm@rice.edu> writes:
Ross> Oh, interesting datapoint. Let me get this clear - on
Ross> oracle, the equivalent of:
Well, I've never gone off to lunch in the middle, but in Oracle 7, I
had transactions which definitely took as much as a few minutes to
complete where the timestamp on every row committed was the same.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
Roland Roberts wrote:
> >>>>> "Ross" == Ross J Reedstrom <reedstrm@rice.edu> writes:
>
> Ross> Oh, interesting datapoint. Let me get this clear - on
> Ross> oracle, the equivalent of:
>
> Well, I've never gone off to lunch in the middle, but in Oracle 7, I
> had transactions which definitely took as much as a few minutes to
> complete where the timestamp on every row committed was the same.
Can you run a test:
BEGIN;
SELECT CURRENT_TIMESTAMP;
wait 5 seconds
SELECT CURRENT_TIMESTAMP;
Are the two times the same?
--
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, Pennsylvania 19073
On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
>Can you run a test:
>
> BEGIN;
> SELECT CURRENT_TIMESTAMP;
> wait 5 seconds
> SELECT CURRENT_TIMESTAMP;
>
>Are the two times the same?
MS SQL 7:
begin transaction
insert into tst values (CURRENT_TIMESTAMP)
-- wait
insert into tst values (CURRENT_TIMESTAMP)
commit
select * from tst
t
---------------------------
2002-09-24 09:49:58.777
2002-09-24 09:50:14.100
Interbase 6:
SQL> select current_timestamp from rdb$database;
=========================
2002-09-24 22:30:13.0000
SQL> select current_timestamp from rdb$database;
=========================
2002-09-24 22:30:18.0000
SQL> commit;
Servus
Manfred
On Mon, Sep 23, 2002 at 09:02:00PM +0200, Manfred Koizar wrote: > On Mon, 23 Sep 2002 13:05:42 -0400, Tom Lane <tgl@sss.pgh.pa.us> > >We concluded that the spec defines the behavior as > >implementation-dependent, > > AFAICT the spec requires the returned value to meet two conditions. > > C1: If a statement contains more than one <datetime value function>, > they all have to return (maybe different formats of) the same value. > > C2: The returned value has to represent a point in time *during* the > execution of the SQL-statement. > > The only thing an implementor is free to choose is which point in time > "during the execution of the SQL-statement" is to be returned, i.e. a > timestamp in the interval between the start of the statement and the > first time when the value is needed. Well, what I would suggest is that when you wrap several statements into a single transaction with begin/commit, the whole lot could be considered a single statement (since they form an atomic transaction so in a sense they are all executed simultaneously). And hence Postgresql is perfectly compliant. My second point would be: what is the point of a timestamp that keeps changing during a transaction? If you want that, there are other functions that serve that purpose. > I understand that with subselects, functions, triggers, rules etc. it > is not easy to implement the specification. If we can't do it now, we > should at least add a todo and make clear in the documentation that > CURRENT_DATE/TIME/TIMESTAMP is not SQL92/99 compliant. The current definition is, I would say, the most useful definition. Can you give an example where your definition would be more useful? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Bruce Momjian dijo: > Roberto Mello wrote: > > Forgive my ignorance here, but what is GUC? And how would I access the > > query duration? > > GUC is postgresql.conf and SET commands. They are variables that can be > set. Just for the record, GUC is an acronym for "Grand Unified Configuration". -- Alvaro Herrera (<alvherre[a]atentus.com>) "El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)
On Tue, Sep 24, 2002 at 10:33:51AM +0200, Manfred Koizar wrote: > > The people who wrote the specification knew about transactions. If > they had wanted what you describe above, they would have written: > > 3) If a transaction generally contains more than one reference > to one or more <datetime value function>s, then all such ref- > erences are effectively evaluated simultaneously. The time of > evaluation of the <datetime value function> during the execution > of the transaction is implementation-dependent. > > But they wrote "SQL-statement", not "transaction". > > >And hence Postgresql is perfectly compliant. > > I'm not so sure. > > >The current definition is, I would say, the most useful definition. Can you > >give an example where your definition would be more useful? > > I did not write the standard, I'm only reading it. I have no problem > with an implementation that deviates from the standard "because we > know better". But we should users warn about this fact and not tell > them it is compliant. At first, I also found the idea of now() freezing during a transaction odd. But now I seems the right thing to do - I can't really come up with a use-case for current_timestamp to vary. For the relational algebra and transactional logic purists out there, having current_timetamp be a fixed transaction time reinforces the 'atomicity' of a transaction - it's _supposed_ to happen all at once, as far as the rest of the system is concerned. Many parts of the the standard deviate from the ideals, however, probably due to the desire of those with existing software to make it 'standards compliant' by bending the standard, instead of fixing the software. There are places in SQL92, especially, where if you know the exact feature set of some of the big DBs from that era, you can imagine the conversation that lead to inserting specific ambiguities into the document. As you've probably noticed, SQL92 (and '99, from what I've look at in it) are _not_ examples of the clearest, most pristine english in the world. I sometimes wonder if the committee was actually an early attempt at machine generated natural language, then I realize if that were true, it would be clearer and more self-consistent. ;-) All this is a very longwinded way for me to say leave now() as transaction time, and get Peter to interpret this passage, to see what should happen with current_timestamp. He seems to be one of the best at disentagling the standards verbiage. Ross
SQL> create table rbr_foo (a date);
Table created.
SQL> begin
2 insert into rbr_foo select sysdate from dual;
[...wait about 10 seconds...]
3 insert into rbr_foo select sysdate from dual;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select * from rbr_foo;
A
---------------------
SEP 27, 2002 12:57:27
SEP 27, 2002 12:57:27
Note that, as near as I can tell, Oracle 8 does NOT have timestamp or
current_timestamp. Online docs say both are present in Oracle 9i.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
OK, we have two db's returning statement start time, and Oracle 8 not having CURRENT_TIMESTAMP. Have we agreed to make CURRENT_TIMESTAMP statement start, and now() transaction start? Is this an open item or TODO item? --------------------------------------------------------------------------- Manfred Koizar wrote: > On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian > <pgman@candle.pha.pa.us> wrote: > >Can you run a test: > > > > BEGIN; > > SELECT CURRENT_TIMESTAMP; > > wait 5 seconds > > SELECT CURRENT_TIMESTAMP; > > > >Are the two times the same? > > MS SQL 7: > begin transaction > insert into tst values (CURRENT_TIMESTAMP) > -- wait > insert into tst values (CURRENT_TIMESTAMP) > commit > select * from tst > > t > --------------------------- > 2002-09-24 09:49:58.777 > 2002-09-24 09:50:14.100 > > Interbase 6: > SQL> select current_timestamp from rdb$database; > > ========================= > 2002-09-24 22:30:13.0000 > > SQL> select current_timestamp from rdb$database; > > ========================= > 2002-09-24 22:30:18.0000 > > SQL> commit; > > Servus > Manfred > -- 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, Pennsylvania 19073
On Sat, Sep 28, 2002 at 11:28:03PM -0400, Bruce Momjian wrote: > > OK, we have two db's returning statement start time, and Oracle 8 not > having CURRENT_TIMESTAMP. > > Have we agreed to make CURRENT_TIMESTAMP statement start, and now() > transaction start? Is this an open item or TODO item? Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In any case, if it does get changed we'll have to go through the documentation and work out whether we mean current_timestamp or now(). I think most people actually want now(). Fortunatly where I work we only use now() so it won't really matter too much. Is there a compelling reason to change? > --------------------------------------------------------------------------- > > Manfred Koizar wrote: > > On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian > > <pgman@candle.pha.pa.us> wrote: > > >Can you run a test: > > > > > > BEGIN; > > > SELECT CURRENT_TIMESTAMP; > > > wait 5 seconds > > > SELECT CURRENT_TIMESTAMP; > > > > > >Are the two times the same? > > > > MS SQL 7: > > begin transaction > > insert into tst values (CURRENT_TIMESTAMP) > > -- wait > > insert into tst values (CURRENT_TIMESTAMP) > > commit > > select * from tst > > > > t > > --------------------------- > > 2002-09-24 09:49:58.777 > > 2002-09-24 09:50:14.100 > > > > Interbase 6: > > SQL> select current_timestamp from rdb$database; > > > > ========================= > > 2002-09-24 22:30:13.0000 > > > > SQL> select current_timestamp from rdb$database; > > > > ========================= > > 2002-09-24 22:30:18.0000 > > > > SQL> commit; > > > > Servus > > Manfred > > > > -- > 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, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.