permission denied for pg_temp_XX when vacuuming

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

Hi Laurenz,

good to know, thank you very much!

Best regards,
Tobias

On Wed, 3 Mar 2021 at 12:10, Laurenz Albe <laurenz.albe@cybertec.at> 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.lott@devoteam.com
[image: Banner EVP Intelligence]
[image: Devoteam at Linkedin] <https://www.linkedin.com/company/devoteam&gt;
[image: Devoteam at Youtube]
<https://www.youtube.com/watch?v=Lrk4lQJ82bI&amp;t=3s&gt;
[image: Devoteam at Twitter] <https://twitter.com/devoteam&gt;

Laurenz Albe <laurenz.albe@cybertec.at> 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

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 <tgl@sss.pgh.pa.us> wrote:

Tobias Lott <tobias.lott@devoteam.com> 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

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 <tgl@sss.pgh.pa.us> wrote:

Tobias Lott <tobias.lott@devoteam.com> 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.lott@devoteam.com
[image: Banner EVP Intelligence]
[image: Devoteam at Linkedin] <https://www.linkedin.com/company/devoteam&gt;
[image: Devoteam at Youtube]
<https://www.youtube.com/watch?v=Lrk4lQJ82bI&amp;t=3s&gt;
[image: Devoteam at Twitter] <https://twitter.com/devoteam&gt;

Tobias Lott <tobias.lott@devoteam.com> 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

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 <tgl@sss.pgh.pa.us> wrote:

Tobias Lott <tobias.lott@devoteam.com> 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.lott@devoteam.com
[image: Banner EVP Intelligence]
[image: Devoteam at Linkedin] <https://www.linkedin.com/company/devoteam&gt;
[image: Devoteam at Youtube]
<https://www.youtube.com/watch?v=Lrk4lQJ82bI&amp;t=3s&gt;
[image: Devoteam at Twitter] <https://twitter.com/devoteam&gt;