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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
--- 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,
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--- 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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--- 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
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/
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--- 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!
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
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, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org