Getting process id of a connection?

-----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.