Thread: Is autovacuum too noisy about orphan temp tables?
While playing around, I got into the situation that I have an "orphaned temp table" in my database. The log is now slowly filling with these messages: LOG: autovacuum: found orphan temp table "pg_temp_2"."foo" in database "postgres" LOG: autovacuum: found orphan temp table "pg_temp_2"."foo" in database "postgres" ... I remember the discussion when that was put in, but I'm starting to think that printing that every time autovacuum wakes up, which is once per minute by default, is too noisy. Also, it doesn't give me any hint on what I can do to get rid of the message; I had to search the archives to find out that I can "DROP SCHEMA pg_temp_2". -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> LOG: autovacuum: found orphan temp table "pg_temp_2"."foo" in database
> "postgres"
> I remember the discussion when that was put in, but I'm starting to
> think that printing that every time autovacuum wakes up, which is once
> per minute by default, is too noisy.
What else would you do? I can't see adding state to remember when we
printed it last.
> Also, it doesn't give me any hint
> on what I can do to get rid of the message; I had to search the archives
> to find out that I can "DROP SCHEMA pg_temp_2".
We could surely add an errhint() to the message.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes: > > LOG: autovacuum: found orphan temp table "pg_temp_2"."foo" in database > > "postgres" > > What else would you do? I can't see adding state to remember when we > printed it last. Why can't we drop orphan temp tables automatically? > > Also, it doesn't give me any hint > > on what I can do to get rid of the message; I had to search the archives > > to find out that I can "DROP SCHEMA pg_temp_2". > > We could surely add an errhint() to the message. Standard DBAs are blind to LOG level messages. If the message is important, we should use WARNING level. Monitoring middleware notifies DBAs of WARNING or higher messages, but discards LOG or lower messages. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> Why can't we drop orphan temp tables automatically?
See prior discussion --- it was deemed too risky. What if there's a bug
in the determination of what's an orphan temp table?
> Standard DBAs are blind to LOG level messages.
Indeed, which is why I'm not too concerned about Heikki's complaint.
regards, tom lane
Tom Lane wrote: > ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: > > Standard DBAs are blind to LOG level messages. > > Indeed, which is why I'm not too concerned about Heikki's complaint. Well, if the disk fills up due to excessive LOG entries, they won't be so blind. I think just adding the HINT is good enough. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Oct 14, 2008, at 4:04 PM, Alvaro Herrera wrote: > Tom Lane wrote: >> ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: > >>> Standard DBAs are blind to LOG level messages. >> >> Indeed, which is why I'm not too concerned about Heikki's complaint. > > Well, if the disk fills up due to excessive LOG entries, they won't be > so blind. I think just adding the HINT is good enough. Since this is something that's not supposed to happen, making it a WARNING might be appropriate too... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Decibel! <decibel@decibel.org> writes:
> Since this is something that's not supposed to happen, making it a
> WARNING might be appropriate too...
Uh, the complaint was that the message is too noisy, not that it isn't
noisy enough.
regards, tom lane
On Wed, Oct 15, 2008 at 11:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Decibel! <decibel@decibel.org> writes: >> Since this is something that's not supposed to happen, making it a >> WARNING might be appropriate too... > > Uh, the complaint was that the message is too noisy, not that it isn't > noisy enough. I think you're confusing the volume of the noise with the frequency of the noise. Once a minute is too often to spit out a message like this regardless of the log level, but at least at a higher log level someone may have a better chance of noticing before the disk fills up. A much better solution would be to not print the warning every time. I think the right solution is to do exactly what you rejected upthread, namely adding some kind of stack to track the last time this was printed. It doesn't need to be real granular, or real exact. Don't track each table separately, just add a static integer. If a particular autovac run sees the problem at least once, increment the integer and print out warnings for all tables found in that run if (variable % 60) == 1. ...Robert
"Robert Haas" <robertmhaas@gmail.com> writes:
> A much better solution would be to not print the warning every time.
> I think the right solution is to do exactly what you rejected
> upthread, namely adding some kind of stack to track the last time this
> was printed.
I really doubt that the problem is worth so much effort. Your handwavy
solution doesn't work, I think, because you are ignoring the problem
that this code is executed in relatively short-lived processes that
aren't all examining the same database. By the time you got to a
solution that did work it'd be pretty complicated.
regards, tom lane
Can autovacuum just set a flag on the orphaned temp table's pg_class record indicating it's been determined to be an orphan? Then other tools could easily list orphaned tables and offer to delete them. greg On 15 Oct 2008, at 10:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Robert Haas" <robertmhaas@gmail.com> writes: >> A much better solution would be to not print the warning every time. >> I think the right solution is to do exactly what you rejected >> upthread, namely adding some kind of stack to track the last time >> this >> was printed. > > I really doubt that the problem is worth so much effort. Your > handwavy > solution doesn't work, I think, because you are ignoring the problem > that this code is executed in relatively short-lived processes that > aren't all examining the same database. By the time you got to a > solution that did work it'd be pretty complicated. > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
Greg Stark escribió: > Can autovacuum just set a flag on the orphaned temp table's pg_class > record indicating it's been determined to be an orphan? Then other tools > could easily list orphaned tables and offer to delete them. Add a new column to pg_class just for orphan tables? Sure sounds overkill. Maybe a bit in the pgstats entry for the table ...? It still seems like useless bloat to me. I wonder if we can find out whether a temp table is orphaned by some other means (a query against catalogs + list of active backends? It still needs to know whether the table was created by this backend or a previous one.) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support