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
Attachments:
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:
Show quoted text
"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