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.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
