timestamp parse error

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>

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

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

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

Uh, no, not yet. There is a non-X version of tcl but I don't think
pgaccess will work under that.

---------------------------------------------------------------------------

wsheldah@lexmark.com wrote:

I just downloaded and installed pgmonitor on my dev. machine after seeing
your post, and it looks nifty. Only problem is I really want to avoid
running X on the database server to conserve the RAM it uses, and this
appears to require X. Any terminal applications to monitor database
activity, perhaps loosely analagous to mtop for MySQL?
(http://mtop.sf.net/)

Wes Sheldahl

Bruce Momjian <pgman@candle.pha.pa.us>@postgresql.org on 09/20/2002
12:18:06 PM

Sent by: pgsql-general-owner@postgresql.org

To: Aaron Held <aaron@MetroNY.com>
cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Monitoring a Query

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.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

There are some good views and functions you can use to get at the SQL
query being executed

try turning on the stats collector and running
select * from pg_stat_activity;

(See http://www.postgresql.org/idocs/index.php?monitoring-stats.html )

You can also see the procID.
From Python I can use this info to get a lot of details about the
running query, CPU and memory use.

But I can't tell how far along it actually is.

-Aaron

wsheldah@lexmark.com 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?

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

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:

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

That is great! Thanks for the info.

Tom Lane wrote: