Re: Fun fact about autovacuum and orphan temp tables

Grigory Smolkin wrote:
>
> On 09/05/2016 04:34 PM, Alvaro Herrera wrote:
> >Grigory Smolkin wrote:
> >
> >>Funny part is that it never drops them. So when backend is finally
> >>terminated, it tries to drop them and fails with error:
> >>
> >>FATAL: out of shared memory
> >>HINT: You might need to increase max_locks_per_transaction
> >>
> >>If I understand that rigth, we are trying to drop all these temp tables in
> >>one transaction and running out of locks to do so.
> >Hmm, yeah, I suppose it does that, and it does seem pretty inconvenient.
> >It is certainly pointless to hold onto these locks for temp tables. I
> >wonder how ugly would be to fix this problem ...
> >
>
> Thank you for your interest in this problem.
> I dont think this is a source of problem. Ugly fix here would only force
> backend to terminate properly.
> It will not help at all in cause of server crash or power outage.
> We need a way to tell autovacuum, that we don`t need orphan temp tables, so
> they can be removed using existing routine.

It is always possible to drop the containing schemas; and as soon as
some other backend uses the BackendId 15 (in your example) the tables
would be removed anyway. This only becomes a longstanding problem when
the crashing backend uses a high-numbered BackendId that's not reused
promptly enough.

> The least invasive solution would be to have a guc, something like
> 'keep_orphan_temp_tables' with boolean value.
> Which would determine a autovacuum worker policy toward encountered orphan
> temp tables.

The stated reason for keeping them around is to ensure you have time to
do some forensics research in case there was something useful in the
crashing backend. My feeling is that if the reason they are kept around
is not a crash but rather some implementation defect that broke end-time
cleanup, then they don't have their purported value and I would rather
just remove them.

I have certainly faced my fair share of customers with dangling temp
tables, and would like to see this changed in some way or another.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services