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

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?

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

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?

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

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

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/&gt;

"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

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

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.

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

"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

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

"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

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

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

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.

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

Joe Conway wrote:

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.

My apologies for being too lazy to copy and paste the email address into
a new email. I forgot hitting reply would add it to thread rather than
starting a new one.

With the large number of variables declared in that block I was thinking
that someone was entering it off a list in another file and accidently
did it twice.

With it being legal C I will ignore any others that might have shown up
in the list.

I believe it is explicitly legal C, in fact.

The C faq at http://www.faqs.org/faqs/C-faq/faq/ states:

First, though there can be many "declarations" (and in many
translation units) of a single "global" (strictly speaking,
"external") variable or function, there must be exactly one
"definition".

cheers

andrew