Thread: 291 pg_toast_temp schemas?
summary: database has 291 empty pg_toast_temp schemas. severity: head-scratcher version: 9.0.7 platform: RHEL6 description: * medium-large production database (300GB) * does data collection and analytics * lately has been having chronic lock-blocking issues * does many, daily batch jobs which involve the creation of many temp tables. Today, while investigating the lock-blocking issues, I noticed that the db has pg_toast_temp_1 through 291. All of these schema are completely empty: breakpad=# select schemaname, relname from pg_stat_all_tables where schemaname LIKE 'pg_toast_temp_%'; schemaname | relname ------------+--------- ... so, apparently we still have an issue with cleaning up pg_toast_temp schema? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> summary: database has 291 empty pg_toast_temp schemas.
If your max_connections is 300 or more, this isn't surprising in the
least.
> ... so, apparently we still have an issue with cleaning up pg_toast_temp
> schema?
If they are empty, no we don't have a problem with cleaning them up.
The pg_namespace entries are deliberately left there across sessions,
to avoid useless catalog churn.
regards, tom lane
On 4/26/12 11:22 AM, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: >> summary: database has 291 empty pg_toast_temp schemas. > > If your max_connections is 300 or more, this isn't surprising in the > least. Yes, they are. >> ... so, apparently we still have an issue with cleaning up pg_toast_temp >> schema? > > If they are empty, no we don't have a problem with cleaning them up. > The pg_namespace entries are deliberately left there across sessions, > to avoid useless catalog churn. Aha, ok. Should I add a FAQ item about this? Also, have we discussed maybe hiding these schemas from \dn? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Thu, Apr 26, 2012 at 5:35 PM, Josh Berkus <josh@agliodbs.com> wrote: > > Also, have we discussed maybe hiding these schemas from \dn? > +1 from this idea... maybe do them visible only on \dn+ --=20 Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
Josh Berkus <josh@agliodbs.com> writes: > Also, have we discussed maybe hiding these schemas from \dn? We've done more than discuss it: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e43fb604d6db229d70d3101aa53348cc16a5473a I take it you're using something older than 9.1. regards, tom lane
On Thu, Apr 26, 2012 at 5:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Berkus <josh@agliodbs.com> writes: >> Also, have we discussed maybe hiding these schemas from \dn? > > We've done more than discuss it: > http://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=3De= 43fb604d6db229d70d3101aa53348cc16a5473a > > I take it you're using something older than 9.1. > that's a quick reaction :D --=20 Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
On 4/26/12 3:48 PM, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: >> Also, have we discussed maybe hiding these schemas from \dn? > > We've done more than discuss it: > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e43fb604d6db229d70d3101aa53348cc16a5473a > > I take it you're using something older than 9.1. Yep, 9.0.7 per bug. Thanks! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com