Re: how to get id of currently executed query?

hi,
i need something to distinguish two separate calls for some select.

i tried to use c functions GetCurrentTransactionId() and
GetCurrentCommandId(),
but there is a problem:
if i'll make plpgsql function, which uses GetCurrentTransactionId() and
GetCurrentCommandId() - getCurrentCommandId changes ( sql's from
functions also increment the command-id-counter).

so my question is - is there any way, to be able to tell "one query from
another"?

i would like to write a plpgsql function that would be able to
differentiate somehow it's calls from one statement to another.

i'm not sure if my description is correct - if you dont understand
something, please let me know.

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)

On Thu, Aug 16, 2007 at 01:30:11PM +0200, hubert depesz lubaczewski wrote:
> hi,
> i need something to distinguish two separate calls for some select.
>
> i tried to use c functions GetCurrentTransactionId() and
> GetCurrentCommandId(),
> but there is a problem:
> if i'll make plpgsql function, which uses GetCurrentTransactionId() and
> GetCurrentCommandId() - getCurrentCommandId changes ( sql's from
> functions also increment the command-id-counter).

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.

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

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.

> so my question is - is there any way, to be able to tell "one query from
> another"?
>
> i would like to write a plpgsql function that would be able to
> differentiate somehow it's calls from one statement to another.

I think you're going to have to give a more explicit example of what
you're actually trying to do.

> i'm not sure if my description is correct - if you dont understand
> something, please let me know.

Well, I'm not following, but it's early in the morning and I didn't
sleep well, so... :)
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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)

hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> 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.

AFAIR, the only state that's guaranteed to work like that is
statement_timestamp. Of course you have to worry whether your machine
is fast enough to do more than one client interaction within whatever
the clock resolution is.

I think the real question here is why you want this behavior at all;
to me it smells of not having thought the problem through correctly.
As an example of why this bothers me: what if the user's query is
rewritten into several queries by a RULE? Should you consider each
of those to be a separate user-issued SQL command? Does your answer
change if you know that the user himself prepared the RULE? (Do you
think users will be happy if statement X followed by statement Y
acts differently in a rule than elsewhere?)

regards, tom lane

On Thu, Aug 16, 2007 at 11:20:30AM -0400, Tom Lane wrote:
> AFAIR, the only state that's guaranteed to work like that is
> statement_timestamp. Of course you have to worry whether your machine
> is fast enough to do more than one client interaction within whatever
> the clock resolution is.

i'll check it, thanks for tip.

> I think the real question here is why you want this behavior at all;
> to me it smells of not having thought the problem through correctly.
> As an example of why this bothers me: what if the user's query is
> rewritten into several queries by a RULE? Should you consider each
> of those to be a separate user-issued SQL command? Does your answer
> change if you know that the user himself prepared the RULE? (Do you
> think users will be happy if statement X followed by statement Y
> acts differently in a rule than elsewhere?)

definitelly. i need this only for some specific functions operating
within selects. i'll blog it as soon as i'll get all the details :)

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)