Sorry, I didn't have the evidence about the bgwriter before. It was
based on conjecture on IRC last night and newly gathered evidence
from this morning.
Here's a list of current postgres processes on the box.
postgres 1186 2.8 5.0 437812 417624 ? S Jul13 22:37
postgres: writer process
postgres 1187 0.3 0.0 5940 2688 ? S Jul13 2:54
postgres: stats buffer process
postgres 1188 3.1 0.1 13456 8856 ? S Jul13 25:16
postgres: stats collector process
My assumption is that it's typically the case that these three
processes generally get allocated sequential pids when postgres starts.
In the postgres log, we see these two types of errors, which were the
only ones that did not report an IP address:
2005-07-12 01:53:31 CDT 13390 :LOG: statistics buffer is full
2005-07-13 17:44:51 CDT 13389 :ERROR: could not open relation
1663/32019395/94144936: No such file or directory
So if we assume that pid 13390 referred to the stats collector from
yesterday, then presumably 13389 was the bgwriter.
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.
Which is what brings me to vacuum or some other system process as a
culprit.
Recognizing that the application code will execute as superuser in
postgres, here is what is happening in a session:
Several temporary tables (some of which inherit from actual tables)
are constructed.
Data is loaded in.
If the data includes updates, in the same session, a VACUUM is
performed, else an ANALYZE is performed.
So we know these things:
1. This import process was running.
2. It had started the vacuum, which occurs in the same session as
temp tables that inherit from the table being vacuumed.
3. bgwriter reported an error about a missing relation file (I guess
this is a strong suspicion more than knowledge, but we strongly
suspect).
So could this be somehow related to the fact that VACUUM, as a result
of the inheritance relationship in the temp tables, is explicitly
attempting to access them?
--
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 11:07 AM, Tom Lane wrote:
Do you have some evidence that the bgwriter was what was reporting the
error? You didn't say that before.
The bgwriter only works on dirty shared buffers, so the only way this
could be happening is if a page of a temp table had gotten loaded into
a shared buffer, which isn't supposed to happen really. Is it
possible
that you had some backend deliberately trying to read a temp table
created by another backend? (You don't have to assume that the
interloper tried to modify the table; a mere SELECT could have created
the dirty-buffer condition due to hint-bit update. You do have to
assume that the interloper was superuser, though, else permissions
would have stopped him from accessing someone else's temp table.)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
