So my first instinct was to avoid use of temp tables in this scenario
altogether, but now I'm thinking all I might need to do is unhook the
temp tables from inheritance.
But I just want to raise a basic reliability issu raised in the
nearby "Autovacuum loose ends" thread issue before I conclude that
this approach is safe enough to prevent any more bgwriter errors:
does pg_autovacuum as currently written in contrib vacuum temp
tables, and, in 8.0, is this then able (however unlikely) to cause
the sort of error I encountered yesterday? Or was that thread only
talking about the new integrated version of the code as far as access
to temp tables are concerned?
If contrib/pg_autovacuum, temp tables, and bgwriter don't mix well,
I'll need to rethink our vacuum strategy.
Thanks!
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jul 14, 2005, at 2:53 PM, Thomas F. O'Connell wrote:
>>> Several temporary tables (some of which inherit from actual tables)
>>> are constructed.
>>>
>>
>> Hmm ... a SELECT from one of the "actual tables" would then scan the
>> temp tables too, no?
>>
>> Thinking about this, I seem to recall that we had agreed to make the
>> planner ignore temp tables of other backends when expanding an
>> inheritance list --- but I don't see anything in the code
>> implementing
>> that, so it evidently didn't get done yet.
>>
>
> In which case, my guess is that we either need to disconnect the
> temporary tables and not use inheritance or revert to a version of
> the application that does not use temporary tables at all.
> Otherwise, there's a risk of any query on a parent of the temp
> tables not restricted by ONLY causing this to occur again, no?
>
> I guess we've been dodging bullets this whole time and were
> affected by two issues in postgres simultaneously: 1) bgwriter
> clogging and 2) inheritance ignorance of other backend temp tables.
>
> Nice perfect storm, eh? An unrestricted (e.g., non-ONLY) query run
> against a _parent_ of a temporary table. Too bad it puts postgres
> in an unusable state...
>
> Thanks to everyone for assistance in the sleuthing process.