Re: [GENERAL] 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/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/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

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.