Re: Which backend using which pg_temp_N schema?
On 06/06/2018 02:00 PM, Jerry Sievers wrote: Adrian Klaver writes: On 06/06/2018 08:54 AM, Jerry Sievers wrote: Adrian Klaver writes: 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? Yes. Which *session*. Alright, finally got it through my thick skull:) I was fixated on the statement that created the table. Thx 2) Temporary objects that are the playing at being permanent objects? 3) Both of the above? -- Adrian Klaver adrian.kla...@aklaver.com
Re: Which backend using which pg_temp_N schema?
Adrian Klaver writes: > On 06/06/2018 08:54 AM, Jerry Sievers wrote: > >> Adrian Klaver writes: >> >> >> 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? Yes. Which *session*. Thx > > 2) Temporary objects that are the playing at being permanent objects? > > 3) Both of the above? > > >> >> -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: Which backend using which pg_temp_N schema?
On 06/06/2018 08:54 AM, Jerry Sievers wrote: Adrian Klaver writes: On 06/05/2018 04:49 PM, Jerry Sievers wrote: Adrian Klaver 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.kla...@aklaver.com
Re: Which backend using which pg_temp_N schema?
Tom Lane writes: > Jerry Sievers writes: > >> Yep thanks... but IMO something that simply exposes whatever internal >> registry of temp schemas/PIDs (which I presume must exist) > > Not really. There are a couple of ways that one could identify a > session's "BackendId", which is the "N" in the "pg_temp_N" name of > the temp schema that it would use if it uses one. But I do not think > there's any terribly reliable way to tell from outside the session > whether it has actually done anything with the temp schema. If it > hasn't, then at least in principle there could be objects in the > schema that are left over from a crashed session that previously had > the same BackendId. We only make an effort to clean out such objects > at the time that a session first creates a temp object. Hmmm, interesting. > In any case, I think you're right that this isn't exposed at the SQL > level presently. Roger that and what motivated the post was basically to explore that issue. Thx > > regards, tom lane > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: Which backend using which pg_temp_N schema?
Jerry Sievers writes: > Yep thanks... but IMO something that simply exposes whatever internal > registry of temp schemas/PIDs (which I presume must exist) Not really. There are a couple of ways that one could identify a session's "BackendId", which is the "N" in the "pg_temp_N" name of the temp schema that it would use if it uses one. But I do not think there's any terribly reliable way to tell from outside the session whether it has actually done anything with the temp schema. If it hasn't, then at least in principle there could be objects in the schema that are left over from a crashed session that previously had the same BackendId. We only make an effort to clean out such objects at the time that a session first creates a temp object. In any case, I think you're right that this isn't exposed at the SQL level presently. regards, tom lane
Re: Which backend using which pg_temp_N schema?
Adrian Klaver writes: > On 06/05/2018 04:49 PM, Jerry Sievers wrote: > >> Adrian Klaver 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.consult...@comcast.net p: 312.241.7800
Re: Which backend using which pg_temp_N schema?
On 06/05/2018 04:49 PM, Jerry Sievers wrote: Adrian Klaver 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.kla...@aklaver.com
Re: Which backend using which pg_temp_N schema?
Adrian Klaver 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. 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.consult...@comcast.net p: 312.241.7800
Re: Which backend using which pg_temp_N schema?
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? 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.kla...@aklaver.com
Which backend using which pg_temp_N schema?
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. 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.consult...@comcast.net p: 312.241.7800
