pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsession_id
11+ messages / 5 participants
[nested] [flat]
* session_id
@ 2004-11-17 13:13 Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 11+ messages in thread
From: Riccardo G. Facchini @ 2004-11-17 13:13 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
@ 2004-11-17 13:49 ` Richard Huxton <dev@archonet.com>
2004-11-17 14:25 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 14:33 ` Re: session_id Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 11+ messages in thread
From: Richard Huxton @ 2004-11-17 13:49 UTC (permalink / raw)
To: abief_ag_-postgresql@yahoo.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
@ 2004-11-17 14:25 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 15:19 ` Re: session_id Michael Fuhr <mike@fuhr.org>
2004-11-17 15:24 ` Re: session_id Richard Huxton <dev@archonet.com>
1 sibling, 2 replies; 11+ messages in thread
From: Riccardo G. Facchini @ 2004-11-17 14:25 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; abief_ag_-postgresql@yahoo.com; +Cc: pgsql-sql
--- 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,
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
2004-11-17 14:25 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
@ 2004-11-17 15:19 ` Michael Fuhr <mike@fuhr.org>
2004-11-18 12:38 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Michael Fuhr @ 2004-11-17 15:19 UTC (permalink / raw)
To: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
2004-11-17 14:25 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 15:19 ` Re: session_id Michael Fuhr <mike@fuhr.org>
@ 2004-11-18 12:38 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-22 04:31 ` Re: session_id Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Riccardo G. Facchini @ 2004-11-18 12:38 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; pgsql-sql
--- 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!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
2004-11-17 14:25 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 15:19 ` Re: session_id Michael Fuhr <mike@fuhr.org>
2004-11-18 12:38 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
@ 2004-11-22 04:31 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Bruce Momjian @ 2004-11-22 04:31 UTC (permalink / raw)
To: abief_ag_-postgresql@yahoo.com; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
2004-11-17 14:25 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
@ 2004-11-17 15:24 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2004-11-17 15:24 UTC (permalink / raw)
To: abief_ag_-postgresql@yahoo.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
@ 2004-11-17 14:33 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-17 14:27 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 14:44 ` Re: session_id Richard Huxton <dev@archonet.com>
1 sibling, 2 replies; 11+ messages in thread
From: Achilleus Mantzios @ 2004-11-17 14:33 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
2004-11-17 14:33 ` Re: session_id Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-17 14:27 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
1 sibling, 0 replies; 11+ messages in thread
From: Riccardo G. Facchini @ 2004-11-17 14:27 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Richard Huxton <dev@archonet.com>; +Cc: abief_ag_-postgresql@yahoo.com; pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
2004-11-17 14:33 ` Re: session_id Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-17 14:44 ` Richard Huxton <dev@archonet.com>
2004-11-17 15:04 ` Re: session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Richard Huxton @ 2004-11-17 14:44 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: abief_ag_-postgresql@yahoo.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: session_id
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Re: session_id Richard Huxton <dev@archonet.com>
2004-11-17 14:33 ` Re: session_id Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-17 14:44 ` Re: session_id Richard Huxton <dev@archonet.com>
@ 2004-11-17 15:04 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
0 siblings, 0 replies; 11+ messages in thread
From: Riccardo G. Facchini @ 2004-11-17 15:04 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: abief_ag_-postgresql@yahoo.com; pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2004-11-22 04:31 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-17 13:13 session_id Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 13:49 ` Richard Huxton <dev@archonet.com>
2004-11-17 14:25 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 15:19 ` Michael Fuhr <mike@fuhr.org>
2004-11-18 12:38 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-22 04:31 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-11-17 15:24 ` Richard Huxton <dev@archonet.com>
2004-11-17 14:33 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-17 14:27 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-11-17 14:44 ` Richard Huxton <dev@archonet.com>
2004-11-17 15:04 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox