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

abruck's profile image abruck posted 5 years ago in General Permalink

I have over 100 of these temp schemas.

How can I hide or can I delete them?

1 attachment(s):
  • pg_temp
ansgar's profile image ansgar posted 5 years ago Permalink

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's profile image abruck posted 5 years ago Permalink

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's profile image abruck posted 5 years ago Permalink

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's profile image ansgar posted 5 years ago Permalink

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's profile image abruck posted 5 years ago Permalink

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's profile image ansgar posted 5 years ago Permalink

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's profile image abruck posted 5 years ago Permalink

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's profile image abruck posted 5 years ago Permalink

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

abruck's profile image abruck posted 5 years ago Permalink

?

ansgar's profile image ansgar posted 5 years ago Permalink

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.