Обсуждение: Vacuum error message

Raymond McKolay <RMcKolay@brittenbanners.com> wrote:

> I am new to postgresql and hope this is the correct list to mail
> to.

Sure!  pgsql-general would work, too, but here is fine.

> I have a DB that is out of control huge

Meaning what?  Could you give that in MB, GB, or TB?  What is out
of control about it?

> I tried running a Vacuum on it and received the following error
> Error: could not read block 512591 of relation 1663/16396/16768:
> Result too large

We need more information, especially PostgreSQL version number.
Please post the results of running this query:

http://wiki.postgresql.org/wiki/Server_Configuration

> Any ideas on what this error message means and how I could
> successfully vacuum the DB?

I can't find this error, as you have shown it, in current sources --
so I can't check on what could cause it.  Please double-check that
it matches *exactly* with the message you received, and with a
version number we should be able to track that down.  Oh, and that
last part of the message probably came from the OS, so knowing
exactly what that is might help.

-Kevin

Raymond McKolay <RMcKolay@brittenbanners.com> writes:
> I am new to postgresql and hope this is the correct list to mail to.
> I have a DB that is out of control huge and I tried running a Vacuum on it and received the following error
> Error: could not read block 512591 of relation 1663/16396/16768: Result too large

That's really bizarre --- it apparently means that read() returned
ERANGE, which is not a documented error code for that system call.

What platform are you on, and what filesystem is the database stored on,
and what PG version is this anyway?

If it's OS X, we have heard before of ERANGE occurring on corrupted HFS+
filesystems:
http://archives.postgresql.org/pgsql-hackers/2012-05/msg00745.php
... or at least, we theorized that this was a symptom of filesystem
corruption, but I don't see anything in the thread about whether that
was proven to be true or not.

If it is a filesystem problem, you'd probably get the same error from
(say) attempting to copy that file to someplace else --- I'd suggest
trying that as a first diagnostic experiment.

As for recovery, if you're really lucky this is just a kernel bug that
has been fixed in some more recent kernel ... are you up to date on OS
updates?  Otherwise, depending on how widespread the damage is, you
might be forced to revert to your last backups (I hope you have some,
if the data is valuable).  But you could try extracting what you can
first.  There's information about dealing with data corruption on our
wiki and in our mailing list archives.

            regards, tom lane

________________________________________
From: Kevin Grittner [Kevin.Grittner@wicourts.gov]
Sent: Friday, August 17, 2012 2:41 PM
To: Raymond McKolay; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Vacuum error message

Raymond McKolay <RMcKolay@brittenbanners.com> wrote:

> I am new to postgresql and hope this is the correct list to mail
> to.

Sure!  pgsql-general would work, too, but here is fine.

> I have a DB that is out of control huge

Meaning what?  Could you give that in MB, GB, or TB?  What is out
of control about it?

> I tried running a Vacuum on it and received the following error
> Error: could not read block 512591 of relation 1663/16396/16768:
> Result too large

We need more information, especially PostgreSQL version number.
Please post the results of running this query:

http://wiki.postgresql.org/wiki/Server_Configuration


"version";"PostgreSQL 8.3.5 on powerpc-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple
Computer,Inc. build 5370)" 
"client_encoding";"UNICODE"
"lc_collate";"C"
"lc_ctype";"C"
"listen_addresses";"*"
"log_destination";"stderr"
"logging_collector";"on"
"max_connections";"400"
"max_fsm_pages";"204800"
"max_stack_depth";"2MB"
"port";"5433"
"server_encoding";"UTF8"
"shared_buffers";"32MB"
"TimeZone";"US/Michigan"


> Any ideas on what this error message means and how I could
> successfully vacuum the DB?

I can't find this error, as you have shown it, in current sources --
so I can't check on what could cause it.  Please double-check that
it matches *exactly* with the message you received, and with a
version number we should be able to track that down.  Oh, and that
last part of the message probably came from the OS, so knowing
exactly what that is might help.

The error is character for character exact.

Ray

-Kevin

Raymond McKolay <RMcKolay@brittenbanners.com> wrote:

> "version";"PostgreSQL 8.3.5 on powerpc-apple-darwin, compiled by
> GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
> build 5370)"

Tom suggested this might be from filesystem corruption and suggested
you try to copy the identified file to somewhere else as a
diagnostic step.  Let us know how that goes.  Oh, and please let us
know what type of filesystem it is on.

-Kevin

The filesystem is HFS+
And here is where my lack of knowledge of postgresql comes into play.
When you say copy the identified file what file are you referring too?  Are you referring to the 1663/16396/16768?  If
sothe file 1663 is nowhere to be found within any directories under the postgresql/data/base/ path.  The 16396 is a
directoryunder the postgresql/data/base path and within it are file files named 16768, 16768.1, 16768.2, 16768.3 and
16768.4

Also I did find this message in the postgesql message log
Relation "pg_catalog.pg_largeobject" contains more than "max_fsm_pages" pages with useful free space.
Hint: consider using VACUUM FULL on the relation or increasing the configuration parameter "max_fsm_pages".

I'm not sure if that is referring to the free disk space on the server or something else.
The current value for the max_fsm_pages is 204800.

Thanks for all the help
Ray

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Friday, August 17, 2012 5:22 PM
To: Raymond McKolay; pgsql-novice@postgresql.org
Subject: RE: [NOVICE] Vacuum error message

Raymond McKolay <RMcKolay@brittenbanners.com> wrote:

> "version";"PostgreSQL 8.3.5 on powerpc-apple-darwin, compiled by GCC
> i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
> build 5370)"

Tom suggested this might be from filesystem corruption and suggested you try to copy the identified file to somewhere
elseas a diagnostic step.  Let us know how that goes.  Oh, and please let us know what type of filesystem it is on. 

-Kevin

Raymond McKolay <RMcKolay@brittenbanners.com> writes:
> And here is where my lack of knowledge of postgresql comes into play.
> When you say copy the identified file what file are you referring too?  Are you referring to the 1663/16396/16768?
Ifso the file 1663 is nowhere to be found within any directories under the postgresql/data/base/ path. 

Yeah, sorry, 1663 is the OID of the pg_default tablespace, so it
corresponds to the data/base/ directory.

> The 16396 is a directory under the postgresql/data/base path and within it are file files named 16768, 16768.1,
16768.2,16768.3 and 16768.4 

And can you copy those somewhere else?

            regards, tom lane

> The 16396 is a directory under the postgresql/data/base path and
> within it are file files named 16768, 16768.1, 16768.2, 16768.3 and
> 16768.4

>And can you copy those somewhere else?

I moved the files to the desktop of the server and changed the max_fsm_pages to 819200 and the same error message still
comesup. 
Do I need more free space on the server than what the size of the DB is in order to Vacuum the DB?
The reason I changed the fsm_pages vaule was due to a hint in the message log.

Ray

Raymond McKolay <RMcKolay@brittenbanners.com> wrote:

>>> The 16396 is a directory under the postgresql/data/base path and
>>> within it are file files named 16768, 16768.1, 16768.2, 16768.3
>>> and 16768.4
>>
>> And can you copy those somewhere else?
>
> I moved the files to the desktop of the server [...] and the same
> error message still comes up.

I'm not sure what you mean by "moved" -- the point was to copy the
contents of the files so that you could determine whether the OS
could read all the pages in the file.  Under some definitions of
"move" you would only have touched the directory entries for the
files, which wouldn't give us the information we were looking for.

> Do I need more free space on the server than what the size of the
> DB is in order to Vacuum the DB?

A normal vacuum of the database doesn't require much (if any) disk
space.  VACUUM FULL is another story, but you probably don't want to
go there.

-Kevin

> I moved the files to the desktop of the server [...] and the same
> error message still comes up.

>I'm not sure what you mean by "moved" -- the point was to copy the contents of the files so that you could determine
whetherthe OS could read all the pages in the file.  Under >some definitions of "move" you would only have touched the
directoryentries for the files, which wouldn't give us the information we were looking for. 

I took the files from the directory they were in and changed the path.
I guess I don't understand how to copy the contents of the file to accomplish what you are asking.
I presumed that by changing the path of the files the vacuum operation would either skip them or error out saying that
theywere missing. 
Could you help with that please?

> Do I need more free space on the server than what the size of the DB
> is in order to Vacuum the DB?

>A normal vacuum of the database doesn't require much (if any) disk space.  VACUUM FULL is another story, but you
probablydon't want to go there. 

I am pretty sure that a vacuum full is eventually what needs to happen since the DB contains useless records and I need
thedisk space given back to the OS. 
But then again I'm no expert with postgres so I am only guessing.

Ray

Raymond McKolay <RMcKolay@brittenbanners.com> writes:
> I took the files from the directory they were in and changed the path.
> I guess I don't understand how to copy the contents of the file to accomplish what you are asking.
> I presumed that by changing the path of the files the vacuum operation would either skip them or error out saying
thatthey were missing. 

I'd have expected a missing-files failure, too.  Are you sure the error
message was the same thing for the same files?  If this is a filesystem
corruption, it's quite possible it affects more than one file.  There's
no very good reason to assume VACUUM would visit the files in the same
order every time, either, so it's possible it would fail on some other
table before getting to this one.

But anyway, what Kevin and I were suggesting was that you try to
duplicate the files, using "cp" on the command line, or Duplicate
in the Finder.

> I am pretty sure that a vacuum full is eventually what needs to happen since the DB contains useless records and I
needthe disk space given back to the OS. 
> But then again I'm no expert with postgres so I am only guessing.

The error message you have told us about is *entirely* unrelated to
needing extra disk space.  It's about being unable to read a file that
is already there.

            regards, tom lane

Solved.  It was a combination of OS corruption and limits on max_fsm_pages being too small.
Thanks for all of your help.  It was a learning experience.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 22, 2012 10:51 AM
To: Raymond McKolay
Cc: Kevin Grittner; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Vacuum error message

Raymond McKolay <RMcKolay@brittenbanners.com> writes:
> I took the files from the directory they were in and changed the path.
> I guess I don't understand how to copy the contents of the file to accomplish what you are asking.
> I presumed that by changing the path of the files the vacuum operation would either skip them or error out saying
thatthey were missing. 

I'd have expected a missing-files failure, too.  Are you sure the error message was the same thing for the same files?
Ifthis is a filesystem corruption, it's quite possible it affects more than one file.  There's no very good reason to
assumeVACUUM would visit the files in the same order every time, either, so it's possible it would fail on some other
tablebefore getting to this one. 

But anyway, what Kevin and I were suggesting was that you try to duplicate the files, using "cp" on the command line,
orDuplicate in the Finder. 

> I am pretty sure that a vacuum full is eventually what needs to happen since the DB contains useless records and I
needthe disk space given back to the OS. 
> But then again I'm no expert with postgres so I am only guessing.

The error message you have told us about is *entirely* unrelated to needing extra disk space.  It's about being unable
toread a file that is already there. 

            regards, tom lane