Re: Which backend using which pg_temp_N schema? - Mailing list pgsql-general

On 06/06/2018 08:54 AM, Jerry Sievers wrote:
> 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.

True assuming there is no activity against the objects or no open 
transactions.

So what you interested in:

1) What created a temporary object?

2) Temporary objects that are the playing at being permanent objects?

3) Both of the above?


> 
>>
>> 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.klaver@aklaver.com