Re: ERROR: could not open relation

On Jul 14, 2005, at 12:51 PM, Tom Lane wrote:

> "Thomas F. O'Connell" <tfo@sitening.com> 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