Thread: pg_autovacuum crashes when query fails for 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, Pennsylvania19073
> 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
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?
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, Pennsylvania19073
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 > >
> 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 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
>> 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:
> 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
"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 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
> 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
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/>