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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Tobias Lott <tobias(dot)lott(at)devoteam(dot)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(dot)lott(at)devoteam(dot)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>