Re: bgwriter, inherited temp tables TODO items?

On Jul 21, 2005, at 1:22 PM, Bruce Momjian wrote:

> Thomas F. O'Connell wrote:
>
>> I'm switching the aftermath of this thread -- http://
>> archives.postgresql.org/pgsql-general/2005-07/msg00501.php -- to -
>> hackers since it raised issues of potential concern to developers.
>>
>> At various points in the thread, Tom Lane said the following:
>>
>> "I have an old note to myself that persistent write errors could
>> "clog"
>> the bgwriter, because I was worried that after an error it would
>> stupidly try to write the same buffer again instead of trying to make
>> progress elsewhere. (CVS tip might be better about this, I'm not
>> sure.)
>> A dirty buffer for a file that doesn't exist anymore would certainly
>> qualify as a persistent failure."
>>
>> and
>>
>> "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."
>>
>> I don't immediately see TODO items correpsonding to these. Should
>> there be some? Or do these qualify as bugs and should they be
>> submitted to that queue?
>>
>
> Would you show a query that causes the problem so I can properly word
> the TODO item for inheritance and temp tables?

It's really more of a timing issue than a specific query issue.
Here's a scenario:

CREATE TABLE parent ( ... );

begin thread1:
CREATE TEMP TABLE child ( ... ) INHERITS FROM ( parent );

begin thread2:
while( 1 ) {
SELECT ... FROM parent WHERE ...;
}

end thread1 (thereby dropping the temp table at the end of session)

At this point, the file is gone, but, as I understand it, the planner
not ignoring temp tables of other backends means that thread2 is
inappropriately accessing the temp table "child" as it performs
SELECTS, thus causing potential dirty buffers in bgwriter, which at
some point during the heavy activity of the tight SELECT loop, will
have the file yanked out from under it and will throw a "No such
file" error.

So I guess the core issue is the failure of the planner to limit
access to temp tables.

Tom seems to come pretty close to a TODO item in his analysis in my
opinion. Something like:

"Make the planner ignore temp tables of other backends when expanding
an inheritance list."

--
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