Recently I installed and started pg_autovacuum against my new Pg 7.4.1 
installation. We use a fairly large number of temporary tables within an 
application (that is, several copies of this application may be running, 
and each creates and drops several temp tables as they cycle through 
their workload). Here's what I think happened, based on the log 
(pg_autovacuum's and the postmaster's):

pg_autovacuum.log:
[2004-02-15 08:10:01 AM] Performing: ANALYZE "pg_temp_13"."tmp_targs"
[2004-02-15 08:10:01 AM] Can not refresh statistics information from the 
database nexcerpt.
[2004-02-15 08:10:01 AM] The error is [ERROR:  relation 
"pg_temp_13.tmp_targs" does not exist

postmaster.log:
2004-02-15 08:10:01 [31563] ERROR:  relation "pg_temp_13.tmp_targs" does 
not exist
2004-02-15 08:10:01 [31563] LOG:  unexpected EOF on client connection


It appears that pg_autovacuum collected the name of a temp table, and 
later tried to analyze it. The table was gone by then, and this caused 
the daemon to exit. As this happened on a Sunday morning, my weekend 
experiment to see how pg_autovacuum would maintain our test database was 
rather spoiled ... 8-(

-- 
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise


Tue, 17 Feb 2004 10:26:20 -0500
Jeff Boes <jboes@nexcerpt.com>
pg_autovacuum bug with temp tables?

I looked into this and I see a number of cases where pg_autovacuum calls
send_query(), but doesn't test for a NULL return from the function.

Matthew, would you look into this and submit a patch?  Thanks.

---------------------------------------------------------------------------

Jeff Boes wrote:
> Recently I installed and started pg_autovacuum against my new Pg 7.4.1 
> installation. We use a fairly large number of temporary tables within an 
> application (that is, several copies of this application may be running, 
> and each creates and drops several temp tables as they cycle through 
> their workload). Here's what I think happened, based on the log 
> (pg_autovacuum's and the postmaster's):
> 
> pg_autovacuum.log:
> [2004-02-15 08:10:01 AM] Performing: ANALYZE "pg_temp_13"."tmp_targs"
> [2004-02-15 08:10:01 AM] Can not refresh statistics information from the 
> database nexcerpt.
> [2004-02-15 08:10:01 AM] The error is [ERROR:  relation 
> "pg_temp_13.tmp_targs" does not exist
> 
> postmaster.log:
> 2004-02-15 08:10:01 [31563] ERROR:  relation "pg_temp_13.tmp_targs" does 
> not exist
> 2004-02-15 08:10:01 [31563] LOG:  unexpected EOF on client connection
> 
> 
> It appears that pg_autovacuum collected the name of a temp table, and 
> later tried to analyze it. The table was gone by then, and this caused 
> the daemon to exit. As this happened on a Sunday morning, my weekend 
> experiment to see how pg_autovacuum would maintain our test database was 
> rather spoiled ... 8-(
> 
> -- 
> Jeff Boes                                      vox 269.226.9550 ext 24
> Database Engineer                                     fax 269.349.9076
> Nexcerpt, Inc.                                 http://www.nexcerpt.com
>            ...Nexcerpt... Extend your Expertise
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 20 Apr 2004 11:25:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
pg_autovacuum crashes when query fails for temp tables
Yeah, I will, I just don't know when.  I have been trying to get to this
and lots of other pg_autovacuum tasks, but my schedule has been quite
crazy as of late.  Anyway, this should probably be a pretty simple patch,
so I can probably find some time to look at it soon.

Any idea on the 7.4.3 release timeline?

>
> I looked into this and I see a number of cases where pg_autovacuum calls
> send_query(), but doesn't test for a NULL return from the function.
>
> Matthew, would you look into this and submit a patch?  Thanks.
>
> ---------------------------------------------------------------------------
>
> Jeff Boes wrote:
>> Recently I installed and started pg_autovacuum against my new Pg 7.4.1
>> installation. We use a fairly large number of temporary tables within an
>> application (that is, several copies of this application may be running,
>> and each creates and drops several temp tables as they cycle through
>> their workload). Here's what I think happened, based on the log
>> (pg_autovacuum's and the postmaster's):
>>
>> pg_autovacuum.log:
>> [2004-02-15 08:10:01 AM] Performing: ANALYZE "pg_temp_13"."tmp_targs"
>> [2004-02-15 08:10:01 AM] Can not refresh statistics information from the
>> database nexcerpt.
>> [2004-02-15 08:10:01 AM] The error is [ERROR:  relation
>> "pg_temp_13.tmp_targs" does not exist
>>
>> postmaster.log:
>> 2004-02-15 08:10:01 [31563] ERROR:  relation "pg_temp_13.tmp_targs" does
>> not exist
>> 2004-02-15 08:10:01 [31563] LOG:  unexpected EOF on client connection
>>
>>
>> It appears that pg_autovacuum collected the name of a temp table, and
>> later tried to analyze it. The table was gone by then, and this caused
>> the daemon to exit. As this happened on a Sunday morning, my weekend
>> experiment to see how pg_autovacuum would maintain our test database was
>> rather spoiled ... 8-(
>>
>> --
>> Jeff Boes                                      vox 269.226.9550 ext 24
>> Database Engineer                                     fax 269.349.9076
>> Nexcerpt, Inc.                                 http://www.nexcerpt.com
>>            ...Nexcerpt... Extend your Expertise
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> 19073
>
>


Tue, 20 Apr 2004 11:48:05 -0400 (EDT)
"Matthew T. O'Connor" <matthew@zeut.net>
Re: pg_autovacuum crashes when query fails for temp tables
Matthew T. O'Connor wrote:
> Yeah, I will, I just don't know when.  I have been trying to get to this
> and lots of other pg_autovacuum tasks, but my schedule has been quite
> crazy as of late.  Anyway, this should probably be a pretty simple patch,
> so I can probably find some time to look at it soon.
> 
> Any idea on the 7.4.3 release timeline?

No, I have not heard of a 7.4.3 timeline, but we certainly want your
eventual fixes in that release.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 20 Apr 2004 12:50:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_autovacuum crashes when query fails for temp tables
Bruce Momjian wrote:

>I looked into this and I see a number of cases where pg_autovacuum calls
>send_query(), but doesn't test for a NULL return from the function.
>
>Matthew, would you look into this and submit a patch?  Thanks.
>
>  
>
Should pg_autovacuum be vacuuming temporary tables?  Secondly, why would 
a temporary table for another session be visible to pg_autovacuum?  I 
know these may sound like stupid questions, but I'm a little shocked 
that it found a temp table.  Did someone make a permanent table, delete 
it, and pg_autovacuum found it in between those operations?


Tue, 20 Apr 2004 12:29:20 -0500
Thomas Swan <tswan@idigx.com>
Re: pg_autovacuum crashes when query fails for temp tables
> Bruce Momjian wrote:
> Should pg_autovacuum be vacuuming temporary tables?

This is a good question, and I would like some opinions from some other
people more informed than I.

> Secondly, why would
> a temporary table for another session be visible to pg_autovacuum?  I
> know these may sound like stupid questions, but I'm a little shocked
> that it found a temp table.  Did someone make a permanent table, delete
> it, and pg_autovacuum found it in between those operations?

I will look into this when I have time, it certainly would be possible
that pg_autovacuum could be tripped up by a sequence of events like you
describe above.  The more general problem is that it shouldn't crash even
if it's vacuuming tables it shouldn't.

Matthew


Tue, 20 Apr 2004 14:19:09 -0400 (EDT)
"Matthew T. O'Connor" <matthew@zeut.net>
Re: pg_autovacuum crashes when query fails for temp 
> I looked into this and I see a number of cases where pg_autovacuum calls
> send_query(), but doesn't test for a NULL return from the function.
> 
> Matthew, would you look into this and submit a patch?  Thanks.

Does pg_autovacuum vacuum and analyze system catalog and TOAST tables 
properly?

Chris


Wed, 21 Apr 2004 09:58:44 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_autovacuum crashes when query fails for temp tables
matthew@zeut.net ("Matthew T. O'Connor") wrote:
>> Bruce Momjian wrote:
>> Should pg_autovacuum be vacuuming temporary tables?
>
> This is a good question, and I would like some opinions from some other
> people more informed than I.
>
>> Secondly, why would
>> a temporary table for another session be visible to pg_autovacuum?  I
>> know these may sound like stupid questions, but I'm a little shocked
>> that it found a temp table.  Did someone make a permanent table, delete
>> it, and pg_autovacuum found it in between those operations?
>
> I will look into this when I have time, it certainly would be possible
> that pg_autovacuum could be tripped up by a sequence of events like you
> describe above.  The more general problem is that it shouldn't crash even
> if it's vacuuming tables it shouldn't.

Well, there's an entry in pg_class even for temporary tables; that
means that even though a separate session has no ability to vacuum the
table, there is still a way to get at its name.

I would think that temp tables are TERRIBLE candidates for
auto-vacuuming; they are likely to be created via INSERT or SELECT
INTO, and if there is a need to analyze such a table, it is likely
needful to do so under strict application control.
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/advocacy.html
Rules of  the Evil Overlord #196.  "I will hire an  expert marksman to
stand by the entrance to my  fortress. His job will be to shoot anyone
who rides up to challenge me."  <http://www.eviloverlord.com/>

Tue, 20 Apr 2004 22:24:21 -0400
Christopher Browne <cbbrowne@acm.org>
Re: pg_autovacuum crashes when query fails for temp
"Matthew T. O'Connor" <matthew@zeut.net> writes:
>> Bruce Momjian wrote:
>> Should pg_autovacuum be vacuuming temporary tables?

> This is a good question, and I would like some opinions from some other
> people more informed than I.

You *can not* vacuum other sessions' temp tables; you don't have access
to the data.  (You have no way to get at pages that are modified in
someone else's local buffer manager.)  You could vacuum your own temp
tables, if you had any, but I would hardly expect autovacuum to have
any.

In reference to Chris' followup question, you *should* be vacuuming
system catalogs, and you *should not* be vacuuming TOAST tables.  VACUUM
on a regular table automatically hits the associated TOAST table.

			regards, tom lane

Tue, 20 Apr 2004 23:29:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_autovacuum crashes when query fails for temp 
Christopher Kings-Lynne wrote:

> Does pg_autovacuum vacuum and analyze system catalog and TOAST tables 
> properly?


Properly?  I think so, that is to the best of my knowledge which is a 
bit limited :-)

Toast Tables:  pg_autovacuum doesn't do anything to toast tables 
explicitly.  I am not aware that they need to be considered, I believe 
that if you vacuum table_foo which has an underlying toast table, then 
vacuum "does the right thing".  Am I wrong?  Someone please enlighten me 
if I am and I will fix.

System Tables:  pg_autovacuum treats non-shared system tables just like 
any other table.  It monitors the activity and vacuums when it deems it 
appropriate.  As for shared system tables: In user databases they are 
only analyzed by pg_autovacuum, while connected to template1, 
pg_autovacuum will treat the shared tables as normal tables and vacuum 
when appropriate.

Does all this sound kosher?

Thanks,

Matthew


Tue, 20 Apr 2004 23:46:09 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: pg_autovacuum crashes when query fails for temp tables
Bruce Momjian wrote:

>No, I have not heard of a 7.4.3 timeline, but we certainly want your
>eventual fixes in that release.
>  
>

Right, and along these lines there are a few other pg_autovacuum bugs 
that were fixed just after 7.4.2.

Tue, 20 Apr 2004 23:47:42 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: pg_autovacuum crashes when query fails for temp tables
Tom Lane wrote:

>"Matthew T. O'Connor" <matthew@zeut.net> writes:
>  
>
>>This is a good question, and I would like some opinions from some other
>>people more informed than I.
>>    
>>
>
>You *can not* vacuum other sessions' temp tables; you don't have access
>to the data.  (You have no way to get at pages that are modified in
>someone else's local buffer manager.)  You could vacuum your own temp
>tables, if you had any, but I would hardly expect autovacuum to have
>any.
>  
>

Ok, so I will change pg_autovacuum to explicitly ignore temp tables.  
Just to be sure, I can do this by avoiding anything found in the pg_temp 
schemea, or is there a better way?  Is it possible that a user could or 
would put a non-temp table the pg_temp schemea?

>In reference to Chris' followup question, you *should* be vacuuming
>system catalogs, and you *should not* be vacuuming TOAST tables.  VACUUM
>on a regular table automatically hits the associated TOAST table.
>

Ok, this is as I thought, pg_autovacuum is already doing the right thing 
here. (see the post I just sent a few minutes ago for more details). 

Thanks Tom,

Matthew


Tue, 20 Apr 2004 23:51:31 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: pg_autovacuum crashes when query fails for temp
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Just to be sure, I can do this by avoiding anything found in the pg_temp 
> schemea, or is there a better way?  Is it possible that a user could or 
> would put a non-temp table the pg_temp schemea?

The pg_temp_NN schemas are the temp objects, by definition and by
implementation.  (Essentially, the reason a temp table is temp is that
its backend does "DROP SCHEMA pg_temp_NN" on exit.)  See namespace.c,
particularly the isTempNamespace and isOtherTempNamespace functions, for
the gory details.

			regards, tom lane

Wed, 21 Apr 2004 00:00:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_autovacuum crashes when query fails for temp 
>> No, I have not heard of a 7.4.3 timeline, but we certainly want your
>> eventual fixes in that release.
> 
> Right, and along these lines there are a few other pg_autovacuum bugs 
> that were fixed just after 7.4.2.

A rollable log solution would be nice :)  Syslog? :)

Chris

Wed, 21 Apr 2004 12:05:26 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_autovacuum crashes when query fails for temp tables
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> System Tables:  pg_autovacuum treats non-shared system tables just like 
> any other table.  It monitors the activity and vacuums when it deems it 
> appropriate.  As for shared system tables: In user databases they are 
> only analyzed by pg_autovacuum, while connected to template1, 
> pg_autovacuum will treat the shared tables as normal tables and vacuum 
> when appropriate.

As long as you hit template1 reasonably often, this will work.  But I'm
a bit concerned about the possibility that some maverick will decide he
doesn't need template1.  (It's at least theoretically possible to run
without it.)

Plan B would be to ignore the sharedness issue and vacuum/analyze shared
catalogs the same as anything else.  While this would certainly result
in more vacuums than really necessary, these tables are probably small
enough that it'd hardly matter ...

Comments?

			regards, tom lane

Wed, 21 Apr 2004 00:38:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_autovacuum crashes when query fails for temp tables 
Hello,

In file postgresql-7.4.2/src/backend/utils/fmgrtab.c

line 336 and line 337 are both equal to "extern Datum array_push
(PG_FUNCTION_ARGS);"

I have created a simple perl script that examines files line by line and
if 2 in a row match prints a result.

Sometimes it catches bad cvs checkins or copy/paste mistakes.

I will continue to scan the output.

Hope this helps.

Mike

On Tue, 2004-04-20 at 22:51, Matthew T. O'Connor wrote:
> Tom Lane wrote:
> 
> >"Matthew T. O'Connor" <matthew@zeut.net> writes:
> >  
> >
> >>This is a good question, and I would like some opinions from some other
> >>people more informed than I.
> >>    
> >>
> >
> >You *can not* vacuum other sessions' temp tables; you don't have access
> >to the data.  (You have no way to get at pages that are modified in
> >someone else's local buffer manager.)  You could vacuum your own temp
> >tables, if you had any, but I would hardly expect autovacuum to have
> >any.
> >  
> >
> 
> Ok, so I will change pg_autovacuum to explicitly ignore temp tables.  
> Just to be sure, I can do this by avoiding anything found in the pg_temp 
> schemea, or is there a better way?  Is it possible that a user could or 
> would put a non-temp table the pg_temp schemea?
> 
> >In reference to Chris' followup question, you *should* be vacuuming
> >system catalogs, and you *should not* be vacuuming TOAST tables.  VACUUM
> >on a regular table automatically hits the associated TOAST table.
> >
> 
> Ok, this is as I thought, pg_autovacuum is already doing the right thing 
> here. (see the post I just sent a few minutes ago for more details). 
> 
> Thanks Tom,
> 
> Matthew
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Tue, 20 Apr 2004 23:48:49 -0500
mike g <mike@thegodshalls.com>
Duplicate variable declared?
> Ok, so I will change pg_autovacuum to explicitly ignore temp tables.  
> Just to be sure, I can do this by avoiding anything found in the pg_temp 
> schemea, or is there a better way?  Is it possible that a user could or 
> would put a non-temp table the pg_temp schemea?

There's no such thing as the pg_temp schema, you will get lots of 
pg_temp_xxx schemas I think.

Chris


Wed, 21 Apr 2004 13:17:11 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_autovacuum crashes when query fails for temp
mike g wrote:
> In file postgresql-7.4.2/src/backend/utils/fmgrtab.c

This is an automatically generated file. The reason for duplicate 
array_push declarations is that one-and-the-same array_push function is 
used to implement two SQL functions, array_append and array_prepend. I 
don't imagine the duplicate declaration hurts anything.

Joe

> Mike
> 
> On Tue, 2004-04-20 at 22:51, Matthew T. O'Connor wrote:
> 
>>Tom Lane wrote:
>>>"Matthew T. O'Connor" <matthew@zeut.net> writes:
>>>>This is a good question, and I would like some opinions from some other
>>>>people more informed than I.

p.s. It is generally considered bad form to reply to an existing thread 
with an entirely new topic.



Tue, 20 Apr 2004 22:23:05 -0700
Joe Conway <mail@joeconway.com>
Re: Duplicate variable declared?
On Wednesday 21 April 2004 12:05 am, Christopher Kings-Lynne wrote:
> >> No, I have not heard of a 7.4.3 timeline, but we certainly want your
> >> eventual fixes in that release.
> >
> > Right, and along these lines there are a few other pg_autovacuum bugs
> > that were fixed just after 7.4.2.
>
> A rollable log solution would be nice :)  Syslog? :)

Agreed, but I don't see that being added into the 7.4 release, I think the 
patch would be bounced if I tried it.  

For 7.5 pg_autovacumm will be integrated in to the backend allowing the use of 
the logging functions already available there.

Matthew

Wed, 21 Apr 2004 07:59:45 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: pg_autovacuum crashes when query fails for temp tables