Thread: Is autovacuum too noisy about orphan temp tables?

Is autovacuum too noisy about orphan temp tables?

From
Heikki Linnakangas
Date:
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


Re: Is autovacuum too noisy about orphan temp tables?

From
Tom Lane
Date:
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


Re: Is autovacuum too noisy about orphan temp tables?

From
ITAGAKI Takahiro
Date:
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




Re: Is autovacuum too noisy about orphan temp tables?

From
Tom Lane
Date:
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


Re: Is autovacuum too noisy about orphan temp tables?

From
Alvaro Herrera
Date:
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


Re: Is autovacuum too noisy about orphan temp tables?

From
Decibel!
Date:
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



Re: Is autovacuum too noisy about orphan temp tables?

From
Tom Lane
Date:
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


Re: Is autovacuum too noisy about orphan temp tables?

From
"Robert Haas"
Date:
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


Re: Is autovacuum too noisy about orphan temp tables?

From
Tom Lane
Date:
"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


Re: Is autovacuum too noisy about orphan temp tables?

From
Greg Stark
Date:
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


Re: Is autovacuum too noisy about orphan temp tables?

From
Alvaro Herrera
Date:
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