Thread: Getting process id of a connection?
Hi all, Is there a way to determine the pid of a database connection from within that connection? As a hypothetical example, I would like to be able to do the following: $ps x PID TTY STAT TIME COMMAND 11674 ? S 0:00 sshd: webbs@pts/1 11675 pts/1 Ss 0:00 -bash 11682 pts/1 T 0:00 psql 11685 pts/1 R+ 0:00 ps x psql=# select CURRENT_PID; 11682 I want this so that I can log the psycopg2 connection pid, and kill it to test reconnection code. Thanks! -W
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 4 Jan 2008 14:59:47 -0800
"Webb Sprague" <webb.sprague@gmail.com> wrote:
> Hi all,
>
> Is there a way to determine the pid of a database connection from
> within that connection?
>
> As a hypothetical example, I would like to be able to do the
> following:
>
> $ps x
> PID TTY STAT TIME COMMAND
> 11674 ? S 0:00 sshd: webbs@pts/1
> 11675 pts/1 Ss 0:00 -bash
> 11682 pts/1 T 0:00 psql
> 11685 pts/1 R+ 0:00 ps x
>
> psql=# select CURRENT_PID;
> 11682
>
> I want this so that I can log the psycopg2 connection pid, and kill it
> to test reconnection code.
postgres=# select procpid from pg_stat_activity;
procpid
- ---------
30851
17510
4496
20237
1305
(5 rows)
Sincerely,
Joshua D. Drake
- --
The PostgreSQL Company: Since 1997, http://www.commandprompt.com/
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD'
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHfrwSATb/zqfZUUQRAq9XAKCnuyYcmAu/kmeoHGzJ9B2HDCS63ACffPGA
zXb5dnHQImYtoE89WK3CyuI=
=jQmT
-----END PGP SIGNATURE-----
Webb Sprague wrote: > Hi all, > > Is there a way to determine the pid of a database connection from > within that connection? > > As a hypothetical example, I would like to be able to do the following: > > $ps x > PID TTY STAT TIME COMMAND > 11674 ? S 0:00 sshd: webbs@pts/1 > 11675 pts/1 Ss 0:00 -bash > 11682 pts/1 T 0:00 psql > 11685 pts/1 R+ 0:00 ps x > > psql=# select CURRENT_PID; > 11682 > > I want this so that I can log the psycopg2 connection pid, and kill it > to test reconnection code. > > Thanks! > -W I think select pg_backend_pid(); will do that.
Joshua D. Drake wrote:
> On Fri, 4 Jan 2008 14:59:47 -0800
> "Webb Sprague" <webb.sprague@gmail.com> wrote:
>
>> Hi all,
>>
>> Is there a way to determine the pid of a database connection from
>> within that connection?
>>
>> As a hypothetical example, I would like to be able to do the
>> following:
>>
>> $ps x
>> PID TTY STAT TIME COMMAND
>> 11674 ? S 0:00 sshd: webbs@pts/1
>> 11675 pts/1 Ss 0:00 -bash
>> 11682 pts/1 T 0:00 psql
>> 11685 pts/1 R+ 0:00 ps x
>>
>> psql=# select CURRENT_PID;
>> 11682
>>
>> I want this so that I can log the psycopg2 connection pid, and kill it
>> to test reconnection code.
>
> postgres=# select procpid from pg_stat_activity;
> procpid
> - ---------
> 30851
> 17510
> 4496
> 20237
> 1305
> (5 rows)
I think he's looking for the pid of the client, not the server.
cww=# select procpid from pg_stat_activity;
procpid
---------
8902
(1 row)
8902 ? Ss 0:00 \_ postgres: cww cww 192.168.171.100(40424)
idle
Colin
> > I think select pg_backend_pid(); will do that. Perfect. I tried googling but I didn't try \df *pid* which would have found it I tried to figure out the pg_stat_activity, but I can't think of a WHERE condition that would make it give me the info I wanted. Thx again to everyone.