Re: [bug fix] Suppress "autovacuum: found orphan temp table" message

From: "Andres Freund" <andres(at)2ndquadrant(dot)com>
> On 2014-07-18 23:38:09 +0900, MauMau wrote:
>> LOG: autovacuum: found orphan temp table "pg_temp_838"."some_table" in
>> database "some_db"
>> LOG: autovacuum: found orphan temp table "pg_temp_902"."some_table" in
>> database "some_db"
>
> So they had server crashes of some form before - otherwise they
> shouldn't see this because during ordinary shutdown the schema will have
> been dropped. C.f. RemoveTempRelationsCallback().

Yes, they are using streaming replication, and experienced failover.

>> 1. Why and when are these messages are output? Do we have to do
>> something?
>
> Yes, you should investigate how the situation came to be.

Yes, as mentioned before, I know the reason thanks to the past mails of this
community. The situation is like this:

1. The applications were using temporary tables. The rows for temporary
tables were created in pg_namespace (one row for pg_temp_n) and pg_class.
Those rows were replicated to the standby. The data files for the
temporary tables were not replicated.
2. The server crashed the standby was promoted to the primary.
3. The new primary performed recovery, but the rows for temporary tables in
the system catalog were left.
4. The applications resumed processing. However, the workload got lighter,
so the zonbie pg_temp_n entries were not recycled.
5. autovacuum workers found the zonbie temporary table entries in the system
catalog, repeatedly emitting lots of messages.

>> 3. Doesn't the output processing of these messages or its cause affect
>> performance? We happen to be facing a performance problem, the cause of
>> which we haven't found yet.
>
> Meh. If that's the bottleneck you've bigger problems.

I guess the performance problem they are facing is not due to this message
output, but I don't have evidence. Anyway, I think worrying users with lots
of messages is evil itself.

>> So, I propose a simple fix to change the LOG level to DEBUG1. I don't
>> know
>> which of DEBUG1-DEBUG5 is appropriate, and any level is OK. Could you
>> include this in 9.2.9?
>
> Surely that's the wrong end to tackle this from. Hiding actual problems
> is a seriously bad idea.

No, there is no serious problem in the user operation in this situation.
Server crash cannot be avoided, and must be anticipated. The problem is
that PostgreSQL makes users worried about lots of (probably) unnecessary
messages.

> It'd be nice if we had infrastructure to do this at startup, but we
> don't...

Yes, ideally so. It is the responsibility of the database server to clean
up the zombie metadata (catalog entries). But I understand there's not such
infrastracture now. If it's not (easily) possible, the best and only thing
is to not make users concerned. Is there any reason to output the message
in the viewpoint of users, not the viewpoint of developers?

The problem is pressing. The customer is trying to use PostgreSQL for very
mission-critical system, and I wish PostgreSQL will get high reputation.
Could you include this in 9.2.9?

Regards
MauMau