Re: Which backend using which pg_temp_N schema?

Adrian Klaver <adrian.klaver@aklaver.com> writes:

> On 06/05/2018 04:49 PM, Jerry Sievers wrote:
>
>> Adrian Klaver <adrian.klaver@aklaver.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:

Yep thanks... but IMO something that simply exposes whatever internal
registry of temp schemas/PIDs (which I presume must exist) to DBA SQL
avoids any perhaps unreliable hackery such as having to scrape query
text from pg_stat_activity or similar.

To wit; A long standing session might have any number of temp objects
existing for which records of same in the aforementioned views has long
since been overwritten.

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

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800