Re: how to get id of currently executed query?

On Thu, Aug 16, 2007 at 09:14:24AM -0500, Decibel! wrote:
> Well of course, if you're running it in a separate command. If you run
> the function twice from one query I'd expect both to return the same.

no. if i run one query with function i get sifferend commandid's inside
the function.

example:

c function:
Datum
current_command_id(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32( GetCurrentCommandId() );
}

plpgsql function
CREATE OR REPLACE FUNCTION test() RETURNS INT4 as $BODY$
declare
BEGIN
RETURN current_command_id();
END;
$BODY$ language plpgsql;

so, let's check how it works:
# select current_command_id(), test(), i from generate_series(1,10) i;
current_command_id | test | i
--------------------+------+----
0 | 1 | 1
1 | 2 | 2
2 | 3 | 3
3 | 4 | 4
4 | 5 | 5
5 | 6 | 6
6 | 7 | 7
7 | 8 | 8
8 | 9 | 9
9 | 10 | 10
(10 rows)

so - as you can see in one query, the value changes in plpgsql. and i need some
id that will be unchanged within one end-user-supplied query.

> Of course you can easily get the same XID back from multiple commands if
> you're in an explicit transaction.

yes, but xid doesn't change for queries in one transaction. so it is not
acceptable for me as it means that 2 separate queries can give the same
id - which i dont like.

> BTW, it would likely be useful to others if you posted your code for
> those functions somewhere. Just yesterday I was thinking of something
> where it would be handy to know what your current XID is.

sure, function to get xid:
Datum
current_transaction_id(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32( GetCurrentTransactionId() );
}

> Well, I'm not following, but it's early in the morning and I didn't
> sleep well, so... :)

maybe now it will be more understandable.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)