From: Mike Williams <mike.williams@comodo.com>
To: pgsql-admin@postgresql.org
Subject: ERROR: could not open segment 1 of relation 1663/743352/743420
	(target block 6407642): No such file or directory
Date: Sun, 28 Mar 2010 17:41:41 +0000
User-Agent: KMail/1.12.4 (Linux/2.6.31-gentoo-r6; KDE/4.3.5; i686; ; )
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201003281841.41865.mike.williams@comodo.com>

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