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
Message-ID: <201003281841.41865.mike.williams@comodo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mike.williams@comodo.com
Subject: Re: ERROR: could not open segment 1 of relation 1663/743352/743420 (target block 6407642): No such file or directory
In-Reply-To: <201003281841.41865.mike.williams@comodo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox