Thread: timestamp parse error
Hello! i'm using PostgreSQL 7.2.1 and got strange parse errors.. could somebody tell me what's wrong with this timestamp query example? PostgreSQL said: ERROR: parser: parse error at or near "date" Your query: select timestamp(date '1998-02-24', time '23:07') example is from PostgreSQL help and certainly worked in previous versions of pgsql.. but in 7.2.1 it does not. had anything changed and not been updated in pgsql manuals or is it a bug? thanx for any help Tomas Lehuta
On Fri, 20 Sep 2002, Tomas Lehuta wrote: > Hello! > > i'm using PostgreSQL 7.2.1 and got strange parse errors.. > could somebody tell me what's wrong with this timestamp query example? > > PostgreSQL said: ERROR: parser: parse error at or near "date" > Your query: > > select timestamp(date '1998-02-24', time '23:07') > > example is from PostgreSQL help and certainly worked in previous versions of > pgsql.. but in 7.2.1 it does not. had anything changed and not been updated > in pgsql manuals or is it a bug? Presumably it's a manual example that didn't get changed. Timestamp(...) is now a specifier for the type with a given precision. You can use "timestamp"(date '1998-02-24', time '23:07') or datetime math (probably something like date '1998-02-24' + time '23:07' and possibly a cast)
"Tomas Lehuta" <lharp@aurius.sk> writes:
> could somebody tell me what's wrong with this timestamp query example?
> select timestamp(date '1998-02-24', time '23:07')
> PostgreSQL said: ERROR: parser: parse error at or near "date"
> example is from PostgreSQL help
From where exactly? I don't see any such example in current sources.
Although you could make this work by double-quoting the name "timestamp"
(which is a reserved word now, per SQL spec), I'd recommend sidestepping
the problem by using the equivalent + operator instead:
regression=# select "timestamp"(date '1998-02-24', time '23:07');
timestamp
---------------------
1998-02-24 23:07:00
(1 row)
regression=# select date '1998-02-24' + time '23:07';
?column?
---------------------
1998-02-24 23:07:00
(1 row)
regards, tom lane
Is there any way to monitor a long running query?
I have stats turned on and I can see my queries, but is there any better
measure of the progress?
Thanks,
-Aaron Held
select current_query from pg_stat_activity;
current_query
<IDLE>
<IDLE>
<IDLE>
<IDLE>
<IDLE> in transaction
FETCH ALL FROM PgSQL_470AEE94
<IDLE> in transaction
select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
'7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
<IDLE>
<IDLE>
<IDLE>
Aaron Held wrote: > Is there any way to monitor a long running query? > > I have stats turned on and I can see my queries, but is there any better > measure of the progress? 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. Sorry. -- 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
There is pgmonitor:
http://gborg.postgresql.org/project/pgmonitor
---------------------------------------------------------------------------
Aaron Held wrote:
> Is there any way to monitor a long running query?
>
> I have stats turned on and I can see my queries, but is there any better
> measure of the progress?
>
> Thanks,
> -Aaron Held
>
> select current_query from pg_stat_activity;
> current_query
>
> <IDLE>
> <IDLE>
> <IDLE>
> <IDLE>
> <IDLE> in transaction
> FETCH ALL FROM PgSQL_470AEE94
> <IDLE> in transaction
> select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" =
> '7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
> <IDLE>
> <IDLE>
> <IDLE>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
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
Hi all developpers, This is just a idea. How about making available the MVCC last version number just like oid is available. This would simplify a lot of table design. You know, having to add a field "updated::timestamp" to detect when a record was updated while viewing it (a la pgaccess). That way, if the version number do not match, one would know that the reccord was updated since last retrieved. What do think? JLL
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? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Jean-Luc Lachance <jllachan@nsd.ca> writes:
> How about making available the MVCC last version number just like oid is
> available. This would simplify a lot of table design. You know, having
> to add a field "updated::timestamp" to detect when a record was updated
> while viewing it (a la pgaccess).
> That way, if the version number do not match, one would know that the
> reccord was updated since last retrieved.
> What do think?
I think it's already there: see xmin and cmin. Depending on your needs,
testing xmin might be enough (you'd only need to pay attention to cmin
if you wanted to notice changes within your own transaction).
regards, tom lane
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
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 >
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
That is great! Thanks for the info. Tom Lane wrote: > > Jean-Luc Lachance <jllachan@nsd.ca> writes: > > How about making available the MVCC last version number just like oid is > > available. This would simplify a lot of table design. You know, having > > to add a field "updated::timestamp" to detect when a record was updated > > while viewing it (a la pgaccess). > > That way, if the version number do not match, one would know that the > > reccord was updated since last retrieved. > > > What do think? > > I think it's already there: see xmin and cmin. Depending on your needs, > testing xmin might be enough (you'd only need to pay attention to cmin > if you wanted to notice changes within your own transaction). > > regards, tom lane
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
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.
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
>>>>> "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
>>>>> "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
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.
Martijn van Oosterhout wrote: > 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(). Well, I think we have to offer statement start time somewhere, and it seems the standard probably requires that. Two other databases do it that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone test on 9.X? > Fortunatly where I work we only use now() so it won't really matter too > much. Is there a compelling reason to change? Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be happy with STATEMENT_TIMESTAMP, but because the standard requires it we may just have to fix 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, Pennsylvania 19073
On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote: > Martijn van Oosterhout wrote: > > 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(). > > Well, I think we have to offer statement start time somewhere, and it > seems the standard probably requires that. Two other databases do it > that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone > test on 9.X? Hmm, well having a statement start time could be conceivably useful. > > Fortunatly where I work we only use now() so it won't really matter too > > much. Is there a compelling reason to change? > > Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be > happy with STATEMENT_TIMESTAMP, but because the standard requires it we > may just have to fix CURRENT_TIMESTAMP. Well, my vote would be for STATEMENT_TIMESTAMP. Is there really no other database that does it the way we do? Perhaps it could be matched with a TRANSACTION_TIMESTAMP and we can sort out CURRENT_TIMESTAMP some other way. -- 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.
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote:
>> Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be
>> happy with STATEMENT_TIMESTAMP, but because the standard requires it we
>> may just have to fix CURRENT_TIMESTAMP.
> Well, my vote would be for STATEMENT_TIMESTAMP.
One problem with inventing STATEMENT_TIMESTAMP is that (if spelled that
way, without parens) it would have to become a fully-reserved keyword,
thus possibly breaking some applications that use that name now.
But the real point, I think, is that the folks pushing for this think
that the standard requires CURRENT_TIMESTAMP to be statement timestamp.
Inventing some other keyword isn't going to satisfy them.
I don't personally find the "it's required by the spec" argument
compelling, because the spec specifically says that the exact behavior
is implementation-dependent --- so anyone who assumes CURRENT_TIMESTAMP
will behave as start-of-statement timestamp is going to have portability
problems anyway. Oracle didn't seem to find the argument compelling
either; at last report they have no statement-timestamp function.
I'd be happier with the whole thing if anyone had exhibited a convincing
use-case for statement timestamp. So far I've not seen any actual
examples of situations that are not better served by either transaction
timestamp or true current time. And the spec is perfectly clear that
CURRENT_TIMESTAMP does not mean true current time...
regards, tom lane
Josh Berkus <josh@agliodbs.com> writes:
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
I have no objection to doing that. What seems to be contentious is
whether we should change the current behavior of CURRENT_TIMESTAMP.
regards, tom lane
How can you make a difference between now('statement'), and
now('immediate').
To me they are the same thing. Why not simply now() for transaction, and
now('CLOCK') or better yet system_clock() or clock() for curent time.
JLL
Josh Berkus wrote:
>
> Tom,
>
> > I'd be happier with the whole thing if anyone had exhibited a convincing
> > use-case for statement timestamp. So far I've not seen any actual
> > examples of situations that are not better served by either transaction
> > timestamp or true current time. And the spec is perfectly clear that
> > CURRENT_TIMESTAMP does not mean true current time...
>
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
>
> I still think it's a good idea, provided that we have some easy means to
> determine now('statement').
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
OK, forget system_clock() or clock() timeofday() will do.
Jean-Luc Lachance wrote:
>
> How can you make a difference between now('statement'), and
> now('immediate').
> To me they are the same thing. Why not simply now() for transaction, and
> now('CLOCK') or better yet system_clock() or clock() for curent time.
>
> JLL
Tom,
> I'd be happier with the whole thing if anyone had exhibited a convincing
> use-case for statement timestamp. So far I've not seen any actual
> examples of situations that are not better served by either transaction
> timestamp or true current time. And the spec is perfectly clear that
> CURRENT_TIMESTAMP does not mean true current time...
Are we still planning on putting the three different versions of now() on the
TODO? I.e.,
now('transaction'),
now('statement'), and
now('immediate')
With now() = now('transaction')?
I still think it's a good idea, provided that we have some easy means to
determine now('statement').
--
-Josh Berkus
Aglio Database Solutions
San Francisco