Обсуждение: 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
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
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
Hi all, I just read it's possible to get the MVCC last version numbers. Is it also possible to get the current transaction id? Would it be possible to check later if that transaction has been commited? This would be nice for a distributed application to enforce an "exactly once" semantics for transactions (even if there are network related errors while the server sends ack for commiting a transaction). And if it's possible, how long would that information be valid, i.e. when do transaction id's get reused? If it's not working I will have to implement my own transactions table. Thanks in advance, Michael Paesold -- Werden Sie mit uns zum "OnlineStar 2002"! Jetzt GMX wählen - und tolle Preise absahnen! http://www.onlinestar.de
Michael Paesold <mpaesold@gmx.at> writes:
> I just read it's possible to get the MVCC last version numbers. Is it also
> possible to get the current transaction id?
Well, there's the brute force way: insert a tuple in some table and look
at its xmin. Offhand I don't think we provide a SQL function to read
current transaction id, though it'd surely be a trivial addition.
> Would it be possible to check
> later if that transaction has been commited? This would be nice for a distributed
> application to enforce an "exactly once" semantics for transactions (even if
> there are network related errors while the server sends ack for commiting a
> transaction).
Again, it's not an exported operation, though you could add a SQL function
that called TransactionIdDidCommit().
> And if it's possible, how long would that information be valid, i.e. when do
> transaction id's get reused?
That would be the tricky part. The ID would be reused after 4 billion
transactions, which is long enough that you probably don't care ... but
the segment of the transaction log that has the associated commit bit
will be recycled as soon as the server has no internal use for it
anymore, which could be as early as the next database-wide VACUUM.
If you tried to call TransactionIdDidCommit() after that, you'd get the
infamous "can't open pg_clog/nnnn" error.
> If it's not working I will have to implement my own transactions table.
That's what I'd recommend. Transaction IDs are internal to the database
and are not designed for users to rely on.
regards, tom lane
Tom Lane wrote: > Michael Paesold <mpaesold@gmx.at> writes: [snip] > > If it's not working I will have to implement my own transactions table. > > That's what I'd recommend. Transaction IDs are internal to the database > and are not designed for users to rely on. > > regards, tom lane Well, after reading your explanation I agree with you that it is better to have my own transaction table. I appreciate your detailed response. Thanks very much! Best Regards, Michael Paesold
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