Re: Which backend using which pg_temp_N schema?

On 06/05/2018 04:49 PM, Jerry Sievers wrote:
> 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.

Just thinking out loud here. The issues I see are:

1) A temporary table is tied to a session and therefore its existence
will be some degree of fleeting.

2) A PID will not exist unless an action is done against the table.

3) Said action maybe contain references to other objects which are in
the temporary schema and objects that are out of it. So you would have
to parse the action statement to determine whether the temporary
table/schema is actually involved.

To me the solution would be to work from the other direction. When you
query pg_class/pg_namespace to determine that temporary tables are
present, then query pg_stat_activity to see what statements are being run:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

For a longer term view there is pg_stat_statements:

https://www.postgresql.org/docs/10/static/pgstatstatements.html

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com