Hello,
My customer reported a problem that the following message is output too
often.
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"
They are using PostgreSQL 9.2.8 on a large Linux machine which has 32 cores,
hundreds of GB of RAM, and powerful storage (striped flash memory). They
run a heavy read/write workload.
These messages appear in the server log once per second. This is because
autovacuum_naptime is set to 1s. I'm not sure if the setting is reasonable,
but they are running a write-intensive workload on a powerful machine. So,
the frequent fine-grained autovacuum may make sense to prevent the table
bloat.
They asked questions like this:
1. Why and when are these messages are output? Do we have to do something?
2. Won't they use up disk space?
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.
I could answer the question 1, because I found some discussions in the
community ML, the oldest of which was in 2008. But I haven't answered the
rest yet.
Their concern is plausible. If users don't have to see this message, I
think we should not worry them with a flood of messages.
BTW, showing a hint message that directs the user to drop the schema is not
user-friendly. First, the plenty of messages may have a bad impact on disk
space and performance until the user notices the message. Second, it's
not necessarily safe to manually drop the pg_temp_n schema, because a new
session may have just reused that schema. Third, if dropping the schema is
the solution, the user would say "then, let the database server do it."
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?
Regards
MauMau