From: "Andres Freund" <and...@2ndquadrant.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
