Thread: autovacuum and temporary tables
Hi all, I have a postgres 8.0 installation, and I'm running autovacuum against it. I have noticed that it processes temporary tables too, which is in itself a bit curious, but the problem is that autovacuum might even crash if a temporary table is suddenly gone while it tries to vacuum it... that's what happened once here. I'm not sure what are the necessary conditions to trigger this, it only happened once. I searched in the release notes and googled a bit, but could not (quickly) find what's the status of autovacuum vs. temporary tables in 8.1. I have seen that that was one of Tom's outstanding issues with autovacuum to be included in the core, but was this resolved ? Thanks, Csaba.
Exactly which version of 8.0.x? There was a bug fixed around 8.0.5 or so "Prevent core dump in contrib version of autovacuum when a table has been dropped. Per report from daveg (not his patch, though)." The version of autovacuum in 8.1 is a fairly different beast than the contrib version, and since it was integrated, I think all of Tom's concerns were dealt with. I highly recommend the 8.1 version over the contrib version for many reason. Matt Csaba Nagy wrote: > Hi all, > > I have a postgres 8.0 installation, and I'm running autovacuum against > it. I have noticed that it processes temporary tables too, which is in > itself a bit curious, but the problem is that autovacuum might even > crash if a temporary table is suddenly gone while it tries to vacuum > it... that's what happened once here. I'm not sure what are the > necessary conditions to trigger this, it only happened once. > > I searched in the release notes and googled a bit, but could not > (quickly) find what's the status of autovacuum vs. temporary tables in > 8.1. I have seen that that was one of Tom's outstanding issues with > autovacuum to be included in the core, but was this resolved ? > > Thanks, > Csaba. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
It's version 8.0.almost3, meaning that I used the 8.0 stable CVS branch just before 8.0.3 was released. I will upgrade this data base to 8.1.x (the latest released version at the time of upgrade) soon, so if the 8.1 version has the temporary table thing fixed that would be very nice :-) I also have an instance running 8.0.almost5, so if that has a fix for the core dump, that would be also nice, cause that instance will not be migrated to 8.1 very soon. BTW, did you actually mean to use the 8.1 autovacuum with 8.0 data base ? I can't imagine how that would work :-) Thanks, Csaba. On Fri, 2006-01-20 at 16:05, Matthew T. O'Connor wrote: > Exactly which version of 8.0.x? There was a bug fixed around 8.0.5 or > so "Prevent core dump in contrib version of autovacuum when a table has > been dropped. Per report from daveg (not his patch, though)." > > The version of autovacuum in 8.1 is a fairly different beast than the > contrib version, and since it was integrated, I think all of Tom's > concerns were dealt with. I highly recommend the 8.1 version over the > contrib version for many reason. > > Matt > > > Csaba Nagy wrote: > > Hi all, > > > > I have a postgres 8.0 installation, and I'm running autovacuum against > > it. I have noticed that it processes temporary tables too, which is in > > itself a bit curious, but the problem is that autovacuum might even > > crash if a temporary table is suddenly gone while it tries to vacuum > > it... that's what happened once here. I'm not sure what are the > > necessary conditions to trigger this, it only happened once. > > > > I searched in the release notes and googled a bit, but could not > > (quickly) find what's the status of autovacuum vs. temporary tables in > > 8.1. I have seen that that was one of Tom's outstanding issues with > > autovacuum to be included in the core, but was this resolved ? > > > > Thanks, > > Csaba. > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > >
Csaba Nagy <nagy@ecircle-ag.com> writes: > I have a postgres 8.0 installation, and I'm running autovacuum against > it. I have noticed that it processes temporary tables too, which is in > itself a bit curious, but the problem is that autovacuum might even > crash if a temporary table is suddenly gone while it tries to vacuum > it... that's what happened once here. 8.0.what? I'm wondering if you are missing this 8.0.5 fix: 2005-10-20 12:14 tgl * contrib/pg_autovacuum/pg_autovacuum.c (REL8_0_STABLE): Prevent core dump in contrib version of autovacuum when a table has been dropped. Per report from daveg (not his patch, though). regards, tom lane
Csaba Nagy wrote: > It's version 8.0.almost3, meaning that I used the 8.0 stable CVS branch > just before 8.0.3 was released. I will upgrade this data base to 8.1.x > (the latest released version at the time of upgrade) soon, so if the 8.1 > version has the temporary table thing fixed that would be very nice :-) > > I also have an instance running 8.0.almost5, so if that has a fix for > the core dump, that would be also nice, cause that instance will not be > migrated to 8.1 very soon. > > BTW, did you actually mean to use the 8.1 autovacuum with 8.0 data base > ? I can't imagine how that would work :-) No I didn't mean to use the 8.1 autovac with 8.0, that can't work. Please upgrade your 8.0.x to at least 8.0.5 and see if that fixes the problem.
OK, I have an 8.0.almost 5 installation which did not have any such problems yet. The 8.0.~3 instance will soon be migrated to 8.1.latest, so I will skip the 8.0.5 step, even if it only means install/restart/no dump - after all I had a single crash in a few months of operation. I take it granted that the crash situation on table drop is fixed. I wonder if temporary tables are still added to the autovacuum list in 8.1 or not ? Thanks, Csaba. On Fri, 2006-01-20 at 17:07, Matthew T. O'Connor wrote: > No I didn't mean to use the 8.1 autovac with 8.0, that can't work. > Please upgrade your 8.0.x to at least 8.0.5 and see if that fixes the > problem.