permission denied for pg_temp_XX when vacuuming
Hi, I have a database in a PostgreSQL 12 instance. When running vacuumdb -z on the database with the user set as owner of the DB, I sometimes, not always, get errors such as: error: vacuuming of table "pg_temp_93.vacuum_l" in database "10050" failed: ERROR: permission denied for schema pg_temp_93 The specific number of the pg_temp schema (pg_temp_XX) is always changing. What could be the reason for this error? Thanks and best regards, Tobias
Re: permission denied for pg_temp_XX when vacuuming
Hi Laurenz, good to know, thank you very much! Best regards, Tobias On Wed, 3 Mar 2021 at 12:10, Laurenz Albe wrote: > On Wed, 2021-03-03 at 09:58 +0100, Tobias Lott wrote: > > I have a database in a PostgreSQL 12 instance. > > When running vacuumdb -z on the database with the user set as owner of > the DB, I sometimes, not always, get errors such as: > > error: vacuuming of table "pg_temp_93.vacuum_l" in database "10050" > failed: ERROR: permission denied for schema pg_temp_93 > > The specific number of the pg_temp schema (pg_temp_XX) is always > changing. What could be the reason for this error? > > Those are schemas for temporary tables, which you cannot VACUUM outside the > session that created them, so you can safely ignore these errors. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > -- Tobias Lott Technical Consultant Region South West +49 151 23649035 <+49+151+23649035> tobias.l...@devoteam.com [image: Banner EVP Intelligence] [image: Devoteam at Linkedin] <https://www.linkedin.com/company/devoteam> [image: Devoteam at Youtube] <https://www.youtube.com/watch?v=Lrk4lQJ82bI&t=3s> [image: Devoteam at Twitter] <https://twitter.com/devoteam>
Re: permission denied for pg_temp_XX when vacuuming
Yes that's strange. A lot of pg_XX tables are skipped, but some of these pg_temp schemas cause errors. Could it be connected to a migration of the database (from an instance running PostgreSQL 9.6 to an instance running PostgreSQL 12) done a few weeks ago? Regards, Tobias On Wed, 3 Mar 2021 at 16:15, Tom Lane wrote: > Laurenz Albe writes: > > On Wed, 2021-03-03 at 09:58 +0100, Tobias Lott wrote: > >> I have a database in a PostgreSQL 12 instance. > >> When running vacuumdb -z on the database with the user set as owner of > the DB, I sometimes, not always, get errors such as: > >> error: vacuuming of table "pg_temp_93.vacuum_l" in database "10050" > failed: ERROR: permission denied for schema pg_temp_93 > >> The specific number of the pg_temp schema (pg_temp_XX) is always > changing. What could be the reason for this error? > > > Those are schemas for temporary tables, which you cannot VACUUM outside > the > > session that created them, so you can safely ignore these errors. > > Indeed, but I'm wondering why vacuumdb isn't skipping them > automatically. Something fishy seems to be happening there. > > regards, tom lane >
Re: permission denied for pg_temp_XX when vacuuming
okay, thank you Tom. There were no crashes of the instance, but some issues with the connected application, resulting in 'could not receive data from client: Connection reset by peer' and 'unexpected EOF on client connection with an open transaction'. So if this might have left behind temp tables causing the errors, should I try to delete these pg_temp tables? Regards, Tobias On Wed, 3 Mar 2021 at 17:10, Tom Lane wrote: > Tobias Lott writes: > > Yes that's strange. A lot of pg_XX tables are skipped, but some of these > > pg_temp schemas cause errors. > > Could it be connected to a migration of the database (from an instance > > running PostgreSQL 9.6 to an instance running PostgreSQL 12) done a few > > weeks ago? > > I wouldn't have expected a migration to bring any temp tables forward. > > Have you had any crashes on this instance (post-migration)? A possible > theory is that crashed backend(s) left behind some temp tables, and then > if vacuumdb's backend process re-uses the backend ID (which determines > the NN in pg_temp_NN) of one of those sessions, it would think those > temp tables are its own and try to vacuum them. Or at least I think > it might. That still fails to explain the permissions errors in any > detail, but at least it offers a reason why vacuumdb would even be > going anywhere near a temp table. > > regards, tom lane > -- Tobias Lott Technical Consultant Region South West +49 151 23649035 <+49+151+23649035> tobias.l...@devoteam.com [image: Banner EVP Intelligence] [image: Devoteam at Linkedin] <https://www.linkedin.com/company/devoteam> [image: Devoteam at Youtube] <https://www.youtube.com/watch?v=Lrk4lQJ82bI&t=3s> [image: Devoteam at Twitter] <https://twitter.com/devoteam>
Re: permission denied for pg_temp_XX when vacuuming
hm ok, thank you Tom. As you mentioned, I think it is not really possible to find out which of the sessions should be there and which not. Also, as it is a Cloud SQL instance in GCP, I don't have access to a user with superuser attributes. Best regards, Tobias On Wed, 3 Mar 2021 at 17:33, Tom Lane wrote: > Tobias Lott writes: > > There were no crashes of the instance, but some issues with the connected > > application, resulting in 'could not receive data from client: Connection > > reset by peer' and 'unexpected EOF on client connection with an open > > transaction'. > > No, that sort of thing would still allow the backend to shut down cleanly. > > > So if this might have left behind temp tables causing the errors, should > I > > try to delete these pg_temp tables? > > It would definitely be worth looking around to see if you have any temp > tables that shouldn't be there. I think you can just do > \dt pg_temp_*.* > in psql (as superuser) to see what's out there. Figuring out which ones > are live sessions' temp tables might be harder, though. > > regards, tom lane > -- Tobias Lott Technical Consultant Region South West +49 151 23649035 <+49+151+23649035> tobias.l...@devoteam.com [image: Banner EVP Intelligence] [image: Devoteam at Linkedin] <https://www.linkedin.com/company/devoteam> [image: Devoteam at Youtube] <https://www.youtube.com/watch?v=Lrk4lQJ82bI&t=3s> [image: Devoteam at Twitter] <https://twitter.com/devoteam>
