Thread: ERROR: XX001: could not read block 2354 of relation…

On Tue, Mar 31, 2009 at 2:10 PM, Patrick Desjardins
<mrdesjardins@gmail.com> wrote:
> Hi,
>
> I have the error ERROR: XX001: could not read block 2354 of relation
> 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying
> to Insert data into a table. I would say that 99% of Insert works and 100%
> of read works. This is only happenning since few weeks. I have done Vaccum
> Analyze without any success (the vaccum take 65 minutes but nothing is
> fixed, still have the ERROR XXX001). I have this error in the backend
> application and when I do some Insert Query in the PgAdmin tool.

What OS, pg version etc are you running?

If windows, are you running any anti-virus software?  A lot of
anti-virus packages are dumb as a brick and lock files when checking
them and cause these problems.  If so, either exclude the pg
directories from virus checking or turn it off altogether.

Just a guess.  Not enough info to really know.

Patrick Desjardins wrote:

I am on Windows Server 2003 and humm I will have to check tommorow morning but I do not think any Anti-Virus is scanning.

Sometimes even an antivirus package that has its "realtime protection" features disabled will still cause problems. This comes back to what Scott Marlowe said: "A lot of anti-virus packages are dumb as a brick." They often fail to unload hook DLLs when resident protection is disabled, and sometimes even keep on scanning, just ignoring the results! (I've seen this multiple times).

I remain of the opinion that antivirus software has no place on a database server. There should be no way a virus can get near it, because you're NEVER granting users access to it except via the database engine, and the only hole in the Windows Firewall should be for the database.

Since this issue keeps on cropping up, I wonder how the other DB vendors that support Windows handle it? Do antivirus products have standard APIs for exceptions - "don't scan me" ? If so, isn't that a gaping security hole? And if not, how do other DBs manage to get anything done when some half-wit dodgy AV software is installed? Or do the other folks (Oracle etc) just have these sorts of issues too?

Proposed FAQ entry:

---------------------
Q: I'm getting weird, intermittent errors when starting PostgreSQL or executing SQL statements. My PostgreSQL server runs on Windows.

A(1): If you are running a version of PostgreSQL less than 8.3, upgrade. Remember to dump your database (you can use PgAdmin for this) BEFORE uninstalling the old version of PostgreSQL.

A(2): If you have any antivirus software installed, COMPLETELY UNINSTALL it (at least as a test to see if it is the problem). Many anti-virus packages are written without considering the needs of databases, and do things that will interfere with the way a database accesses its files. Some have implementation problems that mean that even disabling their real-time protection is insufficient, since they STILL interfere with the database even when supposedly disabled. Lots of AV packages also cause severe performance problems with a database even when they appear to work fine. To see if your antivirus software is causing your problems, completely uninstall it and reboot your computer before re-testing.

Q: I'm getting inexplicable network connection errors or network performance problems with PostgreSQL. My PostgreSQL server runs on Windows.

A(1): If you are running a version of PostgreSQL less than 8.3, upgrade. Remember to dump your database (you can use PgAdmin for this) BEFORE uninstalling the old version of PostgreSQL.

A(2): If you have any 3rd party firewall software installed, COMPLETELY UNINSTALL it. Disabling it is not good enough, as many firewall packages continue to interfere with Windows' networking even when disabled. 3rd party firewall packages should not be necessary on any version of Windows with a built-in firewall, and tend to cause more problems than they solve. They are unsuitable for use on a machine intended for server use. <b>If, after uninstalling your firewall, you lose your network connection or have other networking problems</b>, run the following command:
   netsh ip interface reset %HOMEPATH%\Desktop\resetlog.txt
which should clean up any mess left by the poorly written firewall package's failure to cleanly uninstall its self.



[Needs link to section in server admin docs "PostgreSQL Server Administration for Windows" that discusses AV scanning, isolated server, firewall, datadir location, permissions, etc - I'm happy to write at least a basic version of this if folks here agree it'd be useful.]
---------------------



--
Craig Ringer

2009/4/1 Emanuel Calvo Franco <postgres.arg@gmail.com>:

> 2009/4/1 Patrick Desjardins <mrdesjardins@gmail.com>:

> Did you try to make a reindex?
>

If you get an error with that, try to drop indexes and create
again.

>
> --

>      Emanuel Calvo Franco
>        Sumate al ARPUG !
>      (www.postgres-arg.org -
>         www.arpug.com.ar)
>    ArPUG / AOSUG Member
>   Postgresql Support & Admin
>



--
     Emanuel Calvo Franco
       Sumate al ARPUG !
     (www.postgres-arg.org -
        www.arpug.com.ar)
   ArPUG / AOSUG Member
  Postgresql Support & Admin

2009/3/31 Patrick Desjardins <mrdesjardins@gmail.com>:

> Hi,
>
> I have the error ERROR: XX001: could not read block 2354 of relation
> 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying
> to Insert data into a table. I would say that 99% of Insert works and 100%
> of read works. This is only happenning since few weeks. I have done Vaccum
> Analyze without any success (the vaccum take 65 minutes but nothing is
> fixed, still have the ERROR XXX001). I have this error in the backend
> application and when I do some Insert Query in the PgAdmin tool.
>

This kind of error occurs generally on 7.x versions or if you are using
fsync =off and you have a surprised system down.

Try to do a pg_dump of the database or clean (no delete) del wal files
(this is not
recommendable for new users).

> What can I do to fix that problem?
>
> Thank you,
>

--
     Emanuel Calvo Franco
       Sumate al ARPUG !
     (www.postgres-arg.org -
        www.arpug.com.ar)
   ArPUG / AOSUG Member
  Postgresql Support & Admin

If you are using the EnterpriseDB release, and paying for support, you can always take this to them.

Sent from my mobile device

---

From: Patrick Desjardins
Date: Wed, 1 Apr 2009 11:53:59 -0400
To: Emanuel Calvo Franco<postgres.arg@gmail.com>
Subject: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation…

We are using EnterpriseDB PostGresql 8.3. I can't simply take a good backup because weeks have pass since the first error occurs.

I will try to Vacumm Full first, if nothing is fixed, I will try to pg_dump like you suggest.

On Wed, Apr 1, 2009 at 11:49 AM, Emanuel Calvo Franco <postgres.arg@gmail.com> wrote:

2009/3/31 Patrick Desjardins <mrdesjardins@gmail.com>:

> Hi,
>
> I have the error ERROR: XX001: could not read block 2354 of relation
> 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying
> to Insert data into a table. I would say that 99% of Insert works and 100%
> of read works. This is only happenning since few weeks. I have done Vaccum
> Analyze without any success (the vaccum take 65 minutes but nothing is
> fixed, still have the ERROR XXX001). I have this error in the backend
> application and when I do some Insert Query in the PgAdmin tool.
>

This kind of error occurs generally on 7.x versions or if you are using
fsync =off and you have a surprised system down.

Try to do a pg_dump of the database or clean (no delete) del wal files
(this is not
recommendable for new users).

> What can I do to fix that problem?
>
> Thank you,
>

--
     Emanuel Calvo Franco
       Sumate al ARPUG !
     (www.postgres-arg.org -
        www.arpug.com.ar)
   ArPUG / AOSUG Member
  Postgresql Support & Admin

2009/3/31 Patrick Desjardins <mrdesjardins@gmail.com>:

> Hi,
>
> I have the error ERROR: XX001: could not read block 2354 of relation
> 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying
> to Insert data into a table. I would say that 99% of Insert works and 100%
> of read works. This is only happenning since few weeks. I have done Vaccum
> Analyze without any success (the vaccum take 65 minutes but nothing is
> fixed, still have the ERROR XXX001). I have this error in the backend
> application and when I do some Insert Query in the PgAdmin tool.
>

This kind of error occurs generally on 7.x versions or if you are using
fsync =off and you have a surprised system down.

Try to do a pg_dump of the database or clean (no delete) del wal files
(this is not
recommendable for new users).

> What can I do to fix that problem?
>
> Thank you,
>

--
     Emanuel Calvo Franco
       Sumate al ARPUG !
     (www.postgres-arg.org -
        www.arpug.com.ar)
   ArPUG / AOSUG Member
  Postgresql Support & Admin

On Wed, Apr 1, 2009 at 10:32 AM, Patrick Desjardins
<mrdesjardins@gmail.com> wrote:
> I have not reindexes. I will try to use the Reindex command
> (http://www.postgresql.org/docs/8.3/interactive/sql-reindex.html). If I get
> error I will try to drop them. If it doesn't solve I will pg_dump. I still
> need to wait the IT to remove the Anti-virus. Will give you more news later.

You may be tilting at windmills until they do.  I'd go stand behind
somebody in IT until they came and fixed it.  Seriously.