Re: Which backend using which pg_temp_N schema?

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:

> On 06/05/2018 02:53 PM, Jerry Sievers wrote:
>
>> Was just studying a legacy DB to learn about temp table activity.
>>
>> Felt like being able to tie temp schemas to live backends s/b useful but
>> then didn't find a function/view for doing this.
>
> I don't understand what the above is getting at.
> Can you explain more about what you are trying to do?

Sure... A backend may or not have a pg_temp_N schema assigned to it
depending whether or not it ever needs one for temp objects...

Suppose we query pg_class and pg_namespace to see what temp tables exist
at some particular time. We find some tables and thus have info about
which role ownes them and the usual.

But it's a complex and monolithic app with too many aspects all running
as same role.

Having a way to relate PID to such a temp schema then gives us perhaps a
lot more info about the app behavior. To wit; source IP might lead us
to know that this is a batching aspect of the app and not the OLTP
aspect etc.

Yes of course there might be folks somewhere around this organization
that can answer some of those questions but IMO knowing which PID is
doing temp stuff in some schema tells us a lot that I am not sure can be
machine-gotten any other way.

>
>>
>> A quic \df for functions with names likely to be fruitful revealed
>> nothing. Did likewise for sysinfo views.
>>
>> Am I missing it or does feature not exist?
>>
>> Thx
>>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800