I am using a Azure-hosted PostgreSQL database in which I am operating under the automatically created pg_sql_admin user, which is not a super user though. I created several temp tables from within a script and forgot to delete them in the same connection session, which left me with several pg_toast_temp_x artifacts / schemas, which I no longer need (as you can see in the screenshot, they are all empty). However now, I am unable to delete them, since the schemas owner is azuresu (which is Microsoft and I have no access to) and there is no other superuser. Is there any way to solve this? Already tried dropping it from a script, but that also did not work.

I am using a Azure-hosted PostgreSQL database in which I am operating under the automatically created pg_sql_admin user, which is not a super user though. I created several temp tables from within a script and forgot to delete them in the same connection session, which left me with several pg_toast_temp_x artifacts / schemas, which I no longer need (as you can see in the screenshot, they are all empty). However now, I am unable to delete them, since the schemas owner is azuresu (which is Microsoft and I have no access to) and there is no other superuser. Is there any way to solve this? Already tried dropping it from a script, but that also did not work.

• database
• postgresql
• azure
• temp-tables

• The tables and schemas will be automatically removed when their session ends. Or something is badly broken. I've no idea what azure might be doing behind the scenes though in terms of keeping connections around etc. – Richard Huxton Commented Jan 20 at 10:46
• The connections were properly closed when I created the temp tables and I have also restarted the database several times, but had no effect so far – realbitsurfer Commented Jan 20 at 10:56
• Have you checked with psql? Could it be a glitch in whatever graphical tool you are using - not updating in some way? – Richard Huxton Commented Jan 20 at 11:35
• psql filters it out, which is what I ended up doing in my GUI, but they are definitely still there – realbitsurfer Commented Jan 23 at 13:52

1 Answer 1

I have checked your scenario where the temporary schema is getting created with by default ownership of super user TO Drop it I have tried to terminate sessions , restarting database dropping all object related to the schema still the schema are not getting dropped as here in case of Azure Postgres SQL, only Microsoft is part of the super user role as per this MS document.

To resolve this, you need to raise a support ticket here to get the help from Microsoft end as they are only in superuser role.

As per this doc also, the temp schemas are left around, since there doesn't seem to be much reason to delete catalog entries only to have to create them again.