On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier <michael@paquier.xyz> wrote:
On Wed, Feb 13, 2019 at 12:38:51AM +0000, Andrew Gierth wrote: > Doesn't work - that function's idea of "backend id" doesn't match the > real one, since it's looking at a local copy of the stats from which > unused slots have been removed.
The temporary namespace OID is added to PGPROC since v11, so it could be easy enough to add a system function which maps a temp schema to a PID. Now, it could actually make sense to add this information into pg_stat_get_activity() and that would be cheaper.
I think that would be useful and make sense.
And while at it, what would in this particular case have been even more useful to the OP would be to actually identify that there is a temp table *and which xid it's blocking at*. For regular transactions we can look at backend_xid, but IIRC that doesn't work for temp tables (unless they are inside a transaction). Maybe we can find a way to expose that type of relevant information at a similar level while poking around that code?