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