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