pg.ddx.io pgsql-general@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Huge number of pg_temp and pg_toast_temp schemas 4+ messages / 3 participants [nested] [flat]
* Huge number of pg_temp and pg_toast_temp schemas @ 2020-04-10 16:32 Andrus <kobruleht2@hot.ee> 2020-04-10 17:27 ` Re: Huge number of pg_temp and pg_toast_temp schemas Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 4+ messages in thread From: Andrus @ 2020-04-10 16:32 UTC (permalink / raw) To: pgsql-general 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 There are total 2 * 126 = 252 unnessecary schemas in one database. Those schemas seems not contain any objects. Other databases in cluster similar schemas but in smaller amount. How to delete them automatically and prevent appear in future? 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. Andrus. Attachments:^ permalink raw reply [nested|flat] 4+ messages in threadskeem.PNG (image/png, 41.9K) download
view image
* Re: Huge number of pg_temp and pg_toast_temp schemas 2020-04-10 16:32 Huge number of pg_temp and pg_toast_temp schemas Andrus <kobruleht2@hot.ee> @ 2020-04-10 17:27 ` Tom Lane <tgl@sss.pgh.pa.us> 2020-04-10 18:06 ` Re: Huge number of pg_temp and pg_toast_temp schemas Andrus <kobruleht2@hot.ee> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2020-04-10 17:27 UTC (permalink / raw) To: Andrus <kobruleht2@hot.ee>; +Cc: pgsql-general "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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Huge number of pg_temp and pg_toast_temp schemas 2020-04-10 16:32 Huge number of pg_temp and pg_toast_temp schemas Andrus <kobruleht2@hot.ee> 2020-04-10 17:27 ` Re: Huge number of pg_temp and pg_toast_temp schemas Tom Lane <tgl@sss.pgh.pa.us> @ 2020-04-10 18:06 ` Andrus <kobruleht2@hot.ee> 2020-04-10 18:30 ` Re: Huge number of pg_temp and pg_toast_temp schemas Adrian Klaver <adrian.klaver@aklaver.com> 0 siblings, 1 reply; 4+ messages in thread From: Andrus @ 2020-04-10 18:06 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general Hi! Thank you. >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 hide temp schemas from pgAdmin 4 tree? For such large number of temporary schemas pgAdmin schema tree view becomes polluted and makes database management difficult. I turned most of Nodes options in PgAdmin options off but pgadmin still shows them. It shows also pg_toast schema. Andrus. Attachments:^ permalink raw reply [nested|flat] 4+ messages in threadskeem.PNG (image/png, 35.8K) download
view image
* Re: Huge number of pg_temp and pg_toast_temp schemas 2020-04-10 16:32 Huge number of pg_temp and pg_toast_temp schemas Andrus <kobruleht2@hot.ee> 2020-04-10 17:27 ` Re: Huge number of pg_temp and pg_toast_temp schemas Tom Lane <tgl@sss.pgh.pa.us> 2020-04-10 18:06 ` Re: Huge number of pg_temp and pg_toast_temp schemas Andrus <kobruleht2@hot.ee> @ 2020-04-10 18:30 ` Adrian Klaver <adrian.klaver@aklaver.com> 0 siblings, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2020-04-10 18:30 UTC (permalink / raw) To: Andrus <kobruleht2@hot.ee>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general On 4/10/20 11:06 AM, Andrus wrote: > Hi! > > Thank you. > >> 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 hide temp schemas from pgAdmin 4 tree? > For such large number of temporary schemas pgAdmin schema tree view > becomes polluted and makes database management difficult. > I turned most of Nodes options in PgAdmin options off but pgadmin still > shows them. It shows also pg_toast schema. That's something you probably need to ask the pgAdmin folks: https://www.postgresql.org/list/pgadmin-support/ > > Andrus. -- Adrian Klaver adrian.klaver@aklaver.com ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-04-10 18:30 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-04-10 16:32 Huge number of pg_temp and pg_toast_temp schemas Andrus <kobruleht2@hot.ee> 2020-04-10 17:27 ` Tom Lane <tgl@sss.pgh.pa.us> 2020-04-10 18:06 ` Andrus <kobruleht2@hot.ee> 2020-04-10 18:30 ` Adrian Klaver <adrian.klaver@aklaver.com>
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