From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrus <kobruleht2@hot.ee>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Huge number of pg_temp and pg_toast_temp schemas
Date: Fri, 10 Apr 2020 13:27:45 -0400
Message-ID: <8353.1586539665@sss.pgh.pa.us> (raw)
In-Reply-To: <6F99845517EB402E9269BD6767CCE53C@dell2>
References: <6F99845517EB402E9269BD6767CCE53C@dell2>
"Andrus" <kobruleht2@hot.ee> writes:
> Hi!
> Postgres 12 database contains huge number of pg_temp and pg_toast_temp schemas named
> pg_temp_1 .. pg_temp_126
> and
> pg_toast_temp_1 .. pg_toast_temp_126
This is an unsurprising state, if you routinely have a hundred-plus
connections that sometimes create temp tables. Each such session
needs schemas to keep its temp tables in. The temp tables are
deleted at session exit, but we don't bother to remove the schema
entries, figuring that they'll probably be needed again later, and
a couple of rows in pg_namespace is negligible overhead anyway.
> How to delete them automatically and prevent appear in future?
You can't (well, if you're a superuser you could drop those schemas,
but you can't prevent them from being recreated when needed). And it's
really not worth trying. If you do try you're pretty likely to break
concurrent usage of temp tables.
> Cluster stopped since disk decomes full and streaming async replication is used. Maybe one of those or some other factor created or
> continues to create those schemas. Postgres 12.2 in Debian 10 is used.
It's entirely possible that some temp table took up too much disk
space, but ~250 rows in pg_namespace is not the cause of that problem.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, kobruleht2@hot.ee
Subject: Re: Huge number of pg_temp and pg_toast_temp schemas
In-Reply-To: <8353.1586539665@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox