Hi guys,
We upgraded from 8.3.5 to 8.3.9 yesterday, after an extended period of
testing, to 8.3.10 today in hope of fixing it, and we're getting the following
error on occasion.
ERROR: could not open segment 1 of relation 1663/743352/743420 (target block
2171336): No such file or directory
CONTEXT: SQL statement "SELECT day FROM min_stats WHERE day<= $1 AND
unique_ip=-1 ORDER BY day DESC LIMIT 1"
PL/pgSQL function "update_unique_ips" line 13 at SQL statement
update_unique_ips() is called once per minute, but we get anywhere from one
error per minute, to one error per hour.
Relation 743420 in database 743352 is 441270272 bytes long and growing by a
few thousand bytes here and there.
Obviously, as we've gone from 8.3.9 to 8.3.10, we're restarted postgres a few
times. We have also REINDEXd each table, the database, dropped and re-created
all the indexes.
I've even pg_dump'd the database, and loaded it into a fresh database. The
relation numbers changed, but the 1663 bit remained the same.
Best I can tell it's this bit of code from the beginning of
update_unique_ips() that's doing it, but it seems completely innocuous to me.
CREATE FUNCTION update_unique_ips() RETURNS timestamp without time zone
AS $$
DECLARE
last_day timestamp;
day_for_update timestamp;
BEGIN
-- declare last_day
SELECT INTO last_day day-interval '30 min' as day
FROM min_stats
ORDER BY day DESC
LIMIT 1;
SELECT INTO day_for_update day
FROM min_stats
WHERE day<=last_day AND unique_ip=-1
ORDER BY day DESC
LIMIT 1;
Could anyone help us out here please?
Thanks
--
Mike Williams
