How do I hide/delete pg_temp_* and pg_toast_temp_* schemas

abruck posted 3 weeks ago in General

I have over 100 of these temp schemas.

How can I hide or can I delete them?

1 attachment(s):
  • pg_temp
ansgar posted 3 weeks ago

The session manager has a "Databases" setting, which accepts a semicolon separated list of databases you want to see. Maybe you can use that to restrict the tree to those you want to see.

abruck posted 3 weeks ago

Thank you.

That is an acceptable workaround.

However, when I use another utility like pgAdmin, those temp schemas do not evn show up.

That would be the best approach, if we could just say "No temp schemas, please..."

abruck posted 3 weeks ago

OK, so as I saw doing that (setting the "Databases"), I realized that if any other schemas were created, they would not show up.

So, realistically, this will not work in the long run.

We need a way to hide these useless schemas.

What about deleting them?

ansgar posted 3 weeks ago

In an early stage of PostgreSQL support in Heidi, I had the following query to get the databases (not schemata):

SELECT datname FROM pg_database WHERE datistemplate=FALSE

That query would also filter your temp schemata away, but the whole approach is wrong.

I am wondering where these "temp" schemata come from? What if you delete them?

abruck posted 3 weeks ago

I was going to delete them and decided to do some googling first.

I ran across warnings that indicated that these where system entities and that it was not a good idea to delete any of those, as we do not know the implications of doing so ...

What do you think?

ansgar posted 3 weeks ago

It seems these pg_temp schemata are auto-created when your application uses temorary tables. And I read that there is roughly 1 schema per simultanous connection. So you can normally delete these, but they're always generated again.

This leads me to the conclusion that HeidiSQL could indeed get some setting for ignoring database names. Or maybe when using the "Databases" setting, that could support regular expression including some negating syntax, like so: "!_temp"

abruck posted 2 weeks ago

I just shut down Heidi and when it comes up, I need to re-enter all the Databases that I would like to see.

There must be a better way...

abruck posted 2 weeks ago

So, when I try to drop any of them it says that it does not exist!

abruck posted 1 week ago


ansgar posted 1 week ago

I suppose you did not store the session in which you entered your "Databases" setting. Store it, so you don't have to reenter them the next time.

Please login to leave a reply, or register at first.