On Jul 14, 2005, at 12:51 PM, Tom Lane wrote:
"Thomas F. O'Connell" <tfo@xxxxxxxxxxxx> writes:
Unfortunately, this is a system where the interloper is superuser
(and, yes, changing this has been a TODO). But even so, I need help
understanding how one backend could access the temp table of another.
You'd have to do it pretty explicitly:
select * from pg_temp_NNN.foo ...
but it's certainly possible. I wouldn't expect any application to try
this sort of thing, but if someone was manually poking around on the
box, they might have been tempted to do it.
I can almost guarantee this is not the cause of the problem.
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.
--
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
