Re: Getting current transaction id

От:

"Michael Paesold" <mpaesold@gmx.at>

Дата:

25 сентября 2002 г. в 16:19:26

Tom Lane wrote:

> Michael Paesold  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

Re: Getting current transaction id

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

25 сентября 2002 г. в 13:12:34

Michael Paesold  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

Re: [GENERAL] timestamp parse error

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

20 сентября 2002 г. в 11:24:19

"Tomas Lehuta"  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

Re: [GENERAL] timestamp parse error

От:

Stephan Szabo <sszabo@megazone23.bigpanda.com>

Дата:

20 сентября 2002 г. в 10:38:35

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)

Re: [GENERAL] Monitoring a Query

От:

Bruce Momjian <pgman@candle.pha.pa.us>

Дата:

20 сентября 2002 г. в 13:06:14


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
> 
> 
> 
> 
> 
>  in transaction
> FETCH ALL FROM PgSQL_470AEE94
>  in transaction
> select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" = 
> '7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');
> 
> 
> 
> 
> 
> ---------------------------(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

Re: [GENERAL] Monitoring a Query

От:

Bruce Momjian <pgman@candle.pha.pa.us>

Дата:

20 сентября 2002 г. в 13:01:24

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

Re: [GENERAL] Getting acces to MVCC version number

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

20 сентября 2002 г. в 17:24:50

Jean-Luc Lachance  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

Getting current transaction id

От:

Michael Paesold <mpaesold@gmx.at>

Дата:

23 сентября 2002 г. в 12:03:53

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

Monitoring a Query

От:

Aaron Held <aaron@MetroNY.com>

Дата:

20 сентября 2002 г. в 11:35:50

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





 in transaction
FETCH ALL FROM PgSQL_470AEE94
 in transaction
select * from "Calls" WHERE "DurationOfCall" = 2.5 AND "DateOfCall" = 
'7/01/02' AND ("GroupCode" = 'MIAMI' OR "GroupCode" = 'Salt Lake');

Getting acces to MVCC version number

От:

Jean-Luc Lachance <jllachan@nsd.ca>

Дата:

20 сентября 2002 г. в 14:16:38

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

Re: [GENERAL] Getting acces to MVCC version number

От:

Jean-Luc Lachance <jllachan@nsd.ca>

Дата:

23 сентября 2002 г. в 11:39:37

That is great!  Thanks for the info.

Tom Lane wrote:
> 
> Jean-Luc Lachance  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

Re: [GENERAL] Monitoring a Query

От:

Neil Conway <neilc@samurai.com>

Дата:

27 сентября 2002 г. в 08:20:43

Bruce Momjian  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  || PGP Key ID: DB3C29FC

timestamp parse error

От:

"Tomas Lehuta" <lharp@aurius.sk>

Дата:

20 сентября 2002 г. в 08:30:55

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