SEO

#1 (**permalink**) 04-12-2008, 03:16 AM

>>> Hi, I cant find any function, which tells me something like session
>>> id. Is there something like that? I need it in my AM, because I need
>>> to know, if something which I wrote in file was written in this
>>> current session or previously.
>>
>> How about
>> select procpid||' '||backend_start from pg_stat_activity;
>> Yours,
>> Laurenz Albe
>
>Something like this would be maybe possible, but this select can
>return more rows, when the user is connected with more instances...
>
>David Hoksza

You could do this:

SELECT procpid||' '||backend_start
FROM pg_stat_activity
WHERE datname = current_database()
AND usename = session_user
AND client_addr = inet_client_addr()
AND client_port = inet_client_port();

Regards,

Rodrigo

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#2 (**permalink**) 04-12-2008, 03:16 AM

Rodrigo De Leon wrote:

>>>> Hi, I cant find any function, which tells me something like session
>>>> id. Is there something like that? I need it in my AM, because I need
>>>> to know, if something which I wrote in file was written in this
>>>> current session or previously.
>>>
>>>
>>> How about
>>> select procpid||' '||backend_start from pg_stat_activity;
>>> Yours,
>>> Laurenz Albe
>>
>>
>> Something like this would be maybe possible, but this select can
>> return more rows, when the user is connected with more instances...
>>
>
> You could do this:
>
> SELECT procpid||' '||backend_start
> FROM pg_stat_activity
> WHERE datname = current_database()
> AND usename = session_user
> AND client_addr = inet_client_addr()
> AND client_port = inet_client_port();
>

That's pretty roundabout. We already expose (hex coded) pid.starttime as
a session identifier in log_line_prefix (it's the %c escape) so I don't
see any reason not to provide either the same thing directly in a
function, or at least to expose the backend pid.

If you need it in backend C code, the data can be fetched from MyProcPid
and MyProcPort->session_start.tv_sec

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#3 (**permalink**) 04-12-2008, 03:16 AM

> That's pretty roundabout. We already expose (hex coded) pid.starttime as
> a session identifier in log_line_prefix (it's the %c escape) so I don't
> see any reason not to provide either the same thing directly in a
> function, or at least to expose the backend pid.

That would be nice.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#4 (**permalink**) 04-12-2008, 03:16 AM

Andrew Dunstan <andrew@dunslane.net> writes:
> Rodrigo De Leon wrote:
>> You could do this:
>>
>> SELECT procpid||' '||backend_start
>> FROM pg_stat_activity
>> WHERE datname = current_database()
>> AND usename = session_user
>> AND client_addr = inet_client_addr()
>> AND client_port = inet_client_port();

> That's pretty roundabout.

Indeed. Use pg_backend_pid() instead:

SELECT whatever FROM pg_stat_activity WHERE procpid = pg_backend_pid();

A difficulty with this in existing releases is that pg_stat_activity
lags behind reality, so that you won't see your session listed in it
until you've been connected at least half a second or so. 8.2 won't
have that problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: session id and global storage