On 7th of July, Andres Freund committed patch:

Add psql PROMPT variable showing the pid of the connected to backend.
 
The substitution for the pid is %p.
 
Author: Julien Rouhaud
Discussion: <a href="http://git.postgresql.org/gitweb/?p=postgresql.git;a=object;h=116262CF971C844FB6E793F8809B51C6E99D48">116262CF971C844FB6E793F8809B51C6E99D48</a>@BPXM02GP.gisp.nec.co.jp

It was over a month ago, but in the mean time I was on vacation, and I wanted to test it with some specific case.

Anyway – the description seems to be obvious. Just in case it isn't, let me show you how it works. Normally when you start psql, you'll get prompt looking like:

depesz=#

where “depesz" is the name of database you're connected to.

You can of course modify it, using many variables, described in the manual

I, for example, configured my psql, so that my default prompt shows:

(depesz@[LOCAL]:5960) 20:39:33 [depesz] 
$

Which shows my username in database, where the database is (localhost, over unix socket, port 5960), current time, and name of the database that I'm connected to.

Now, since 9.6, we will have one more addition – ability to add pid of backend.

Let's see:

$ \SET PROMPT1 '%n@%/ : %p $ '
depesz@depesz : 26853 $

Nice. What good is it? Well, I can immediately see it, even in history, so I can easily grep for it in logs. Or use the value in queries, like:

depesz@depesz : 26853 $ SELECT COUNT(*) FROM pg_stat_activity WHERE pid <> 26853;
 COUNT
-------
     0
(1 ROW)

So far, so good. But, I want to test it in harsh conditions. What will happen if the pid of backend would change? This could happen if we're using pgbouncer in transaction/statement pooling modes.

So, I connected to pgbouncer, and ran:

depesz=# SELECT pg_backend_pid();
 pg_backend_pid 
----------------
          27632
(1 ROW)
 
depesz=# \SET PROMPT1 '%n@%/ : %p $ '
depesz@depesz : 1658680808 $

Whoa. Clearly the value is not what we expected. Even in session pooling, the received pid is meaningless.

I take it that it's because pgbouncer takes over the field in returned data, and inserts there something that can be used to track this connection in pgbouncer itself, but I have no idea what.

In any case – pgbouncer is external tool, and there are no warranties that psql will work with it to 100% of its capabilities.

I for one, like it, and will most likely use in many places, thank guys.