Обсуждение: session_id
hi all, is there a way to determine the session id on a database session? I would need to have a unique number whenever a session is started, and have this available as a function or view result. thanks.
Riccardo G. Facchini wrote:
> hi all,
>
> is there a way to determine the session id on a database session?
>
> I would need to have a unique number whenever a session is started, and
> have this available as a function or view result.
Add a new sequence to your database: CREATE SEQUENCE my_session_id;
Then, at the start of every session: SELECT nextval('my_session_id');
and whenever you need the value: SELECT currval('my_session_id');
Sequences are concurrency-safe, so you're OK with multiple clients. They
return INT8 values, so you should be good for unique numbers for a while.
The only thing is, you need to remember to call nextval() every time you
connect.
HTH
-- Richard Huxton Archonet Ltd
O Richard Huxton έγραψε στις Nov 17, 2004 :
> Riccardo G. Facchini wrote:
> > hi all,
> >
> > is there a way to determine the session id on a database session?
> >
> > I would need to have a unique number whenever a session is started, and
> > have this available as a function or view result.
Why not SELECT pg_backend_pid();
??
>
> Add a new sequence to your database:
> CREATE SEQUENCE my_session_id;
>
> Then, at the start of every session:
> SELECT nextval('my_session_id');
>
> and whenever you need the value:
> SELECT currval('my_session_id');
>
> Sequences are concurrency-safe, so you're OK with multiple clients. They
> return INT8 values, so you should be good for unique numbers for a while.
>
> The only thing is, you need to remember to call nextval() every time you
> connect.
>
> HTH
>
--
-Achilleus
--- Richard Huxton <__> wrote:
> Riccardo G. Facchini wrote:
> > hi all,
> >
> > is there a way to determine the session id on a database session?
> >
> > I would need to have a unique number whenever a session is started,
> and
> > have this available as a function or view result.
>
> Add a new sequence to your database:
> CREATE SEQUENCE my_session_id;
>
> Then, at the start of every session:
> SELECT nextval('my_session_id');
>
> and whenever you need the value:
> SELECT currval('my_session_id');
>
> Sequences are concurrency-safe, so you're OK with multiple clients.
> They
> return INT8 values, so you should be good for unique numbers for a
> while.
>
> The only thing is, you need to remember to call nextval() every time
> you
> connect.
>
> HTH
> --
> Richard Huxton
> Archonet Ltd
>
Good idea, but it won't work for what I need.
I'll be able to do get the nextval('my_session_id') as soon as the
session initiates, but my problem is that I need to make all the
subsecuent actions aware of that particular value. using
currval('my_session_id') is not good, as any other session is likely to
also change my_session_id to another value.
I was looking more on the pg_stat_activity view, but the problem I face
is that I'm not sure on how to retrieve the unique
pg_stat_get_backend_pid that corresponds to my own job...
thank you,
any other suggestion?
regards,
--- Achilleus Mantzios <__> wrote: > O Richard Huxton ������ ���� Nov 17, 2004 : > > > Riccardo G. Facchini wrote: > > > hi all, > > > > > > is there a way to determine the session id on a database session? > > > > > > I would need to have a unique number whenever a session is > started, and > > > have this available as a function or view result. > > Why not SELECT pg_backend_pid(); > ?? > > > [..] that could work. Is this the unique value for my own session? can somebody confirm it? regards, R.
Achilleus Mantzios wrote: > O Richard Huxton έγραψε στις Nov 17, 2004 : > > >>Riccardo G. Facchini wrote: >> >>>hi all, >>> >>>is there a way to determine the session id on a database session? >>> >>>I would need to have a unique number whenever a session is started, and >>>have this available as a function or view result. > > > Why not SELECT pg_backend_pid(); > ?? This is guaranteed to be unique while connected, but if you want to track sessions over time isn't guaranteed to be. So, if you might have pid=1234 now and also a month ago in a different session (especially if you had a server reboot in-between). Of course, if Riccardo doesn't need that, the pid is fine. -- Richard Huxton Archonet Ltd
--- Richard Huxton <__> wrote: > Achilleus Mantzios wrote: > > O Richard Huxton ������ ���� Nov 17, 2004 : > > > > > >>Riccardo G. Facchini wrote: > >> > >>>hi all, > >>> > >>>is there a way to determine the session id on a database session? > >>> > >>>I would need to have a unique number whenever a session is > started, and > >>>have this available as a function or view result. > > > > > > Why not SELECT pg_backend_pid(); > > ?? > > This is guaranteed to be unique while connected, but if you want to > track sessions over time isn't guaranteed to be. So, if you might > have > pid=1234 now and also a month ago in a different session (especially > if > you had a server reboot in-between). > > Of course, if Riccardo doesn't need that, the pid is fine. > > -- > Richard Huxton > Archonet Ltd > Ok. Taken note. No. I don't need that, as I only need it during the session itself. Once ended, the session info is not required over time. thanks to all for your suggestions, Riccardo
On Wed, Nov 17, 2004 at 06:25:25AM -0800, Riccardo G. Facchini wrote:
>
> --- Richard Huxton <__> wrote:
> >
> > Add a new sequence to your database:
[snip]
> Good idea, but it won't work for what I need.
> I'll be able to do get the nextval('my_session_id') as soon as the
> session initiates, but my problem is that I need to make all the
> subsecuent actions aware of that particular value. using
> currval('my_session_id') is not good, as any other session is likely to
> also change my_session_id to another value.
Where did you get the idea that currval() would be affected by other
sessions? Richard mentioned that sequences are concurrency-safe,
as does the documentation.
http://www.postgresql.org/docs/7.4/static/functions-sequence.html
Can you give us an example of where this wouldn't work?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Riccardo G. Facchini wrote:
>>Add a new sequence to your database:
>> CREATE SEQUENCE my_session_id;
>>
>>Then, at the start of every session:
>> SELECT nextval('my_session_id');
>>
>>and whenever you need the value:
>> SELECT currval('my_session_id');
> Good idea, but it won't work for what I need.
> I'll be able to do get the nextval('my_session_id') as soon as the
> session initiates, but my problem is that I need to make all the
> subsecuent actions aware of that particular value. using
> currval('my_session_id') is not good, as any other session is likely to
> also change my_session_id to another value.
No - other sessions will see different values. Test it and see.
-- Richard Huxton Archonet Ltd
--- Michael Fuhr <__> wrote:
> On Wed, Nov 17, 2004 at 06:25:25AM -0800, Riccardo G. Facchini wrote:
> >
> > --- Richard Huxton <__> wrote:
> > >
> > > Add a new sequence to your database:
>
> [snip]
>
> > Good idea, but it won't work for what I need.
> > I'll be able to do get the nextval('my_session_id') as soon as the
> > session initiates, but my problem is that I need to make all the
> > subsecuent actions aware of that particular value. using
> > currval('my_session_id') is not good, as any other session is
> likely to
> > also change my_session_id to another value.
>
> Where did you get the idea that currval() would be affected by other
> sessions? Richard mentioned that sequences are concurrency-safe,
> as does the documentation.
>
> http://www.postgresql.org/docs/7.4/static/functions-sequence.html
>
> Can you give us an example of where this wouldn't work?
>
No, I can't provide it because your'e right. currval() is NOT affected
by other sessions.
Thanks anyway for the suggestion, using pg_backend_pid() solved the
uniqueness I needed. I'll keep the nextval/currval for another
opportunity.
thanks to all for the support!
Riccardo G. Facchini wrote: > No, I can't provide it because your'e right. currval() is NOT affected > by other sessions. > > Thanks anyway for the suggestion, using pg_backend_pid() solved the > uniqueness I needed. I'll keep the nextval/currval for another > opportunity. FYI, we needed a unique-through-time session id for the log_line_prefix so we used the seconds-since-1970-dot-pid. -- 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, Pennsylvania19073