Thread: ERROR: unexpected hash relation size:
... x should be y
I do get these messages one 2 of my servers running mostly on identical
data. The servers are unix-based and are running 8.1.8 under linux (Gentoo).
On one server the message appears only after vacuum-ing, on the other I
had some errors after inserting into the table too (about 12 inserts, no
more messages except while vacuuming since then)
The affected table has only one index based upon an integer.
The table definition is:
p_code character varying(10)
a_nr integer
ch_nr integer
and some other columns
the Index is a hash base upon a_nr. The table stores protocols for
batches with batch number ch_nr and order number a_nr and product
identifier code p_code. None of these columns are primary keys.
On the backup_server I got eg. errors while inserting
INSERT INTO d_kochmi (p_code,a_nr,ch_nr ....)
VALUES('613CL8D110',56117,1 ...)
batch number (column 3) varied from 1 to 9.
Should I do some more inverstigation ?
On one server i dropped and recreated the index
(CREATE INDEX idx_d_kochmi ON d_kochmi (a_nr) ) and the error while
running VACCUM was gone.
I'm wondering if the use of the hash index is disabled when such an
error occurs or if the index is used with probably wrong data ?
Regards
Gerhard
On Mon, Jul 02, 2007 at 11:46:54AM +0200, Gerhard Hintermayer wrote: > ... x should be y > > I do get these messages one 2 of my servers running mostly on identical > data. The servers are unix-based and are running 8.1.8 under linux (Gentoo). > On one server the message appears only after vacuum-ing, on the other I > had some errors after inserting into the table too (about 12 inserts, no > more messages except while vacuuming since then) Has the server crashed recently? Hash indexes are known not to be crash safe. Using REINDEX on the index should fix it. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Gerhard Hintermayer <g.hintermayer@inode.at> writes: > ... x should be y > I do get these messages one 2 of my servers running mostly on identical > data. The servers are unix-based and are running 8.1.8 under linux (Gentoo). Update. http://developer.postgresql.org/pgdocs/postgres/release-8-1-9.html regards, tom lane
Martijn van Oosterhout wrote: > On Mon, Jul 02, 2007 at 11:46:54AM +0200, Gerhard Hintermayer wrote: >> ... x should be y >> >> I do get these messages one 2 of my servers running mostly on identical >> data. The servers are unix-based and are running 8.1.8 under linux (Gentoo). >> On one server the message appears only after vacuum-ing, on the other I >> had some errors after inserting into the table too (about 12 inserts, no >> more messages except while vacuuming since then) > > Has the server crashed recently? Hash indexes are known not to be crash > safe. Using REINDEX on the index should fix it. > > Have a nice day, No, the server(s) did not crash. Will do the update Tom Lane sugested ASAP (unfortunately server is running 7*24 :-( ) Strange, that the error is encountered after more that 2 month running flawless. Thanks
Tom Lane wrote: > Gerhard Hintermayer <g.hintermayer@inode.at> writes: >> ... x should be y >> I do get these messages one 2 of my servers running mostly on identical >> data. The servers are unix-based and are running 8.1.8 under linux (Gentoo). > > Update. > > http://developer.postgresql.org/pgdocs/postgres/release-8-1-9.html > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > Thanks, will do that ASAP. But strange, that this problem is encoutered after 2 month running 8.1.8 flawless (without any changes to indices and database scheme) Regards Gerhard
Tom Lane wrote: > Gerhard Hintermayer <g.hintermayer@inode.at> writes: >> ... x should be y >> I do get these messages one 2 of my servers running mostly on identical >> data. The servers are unix-based and are running 8.1.8 under linux (Gentoo). > > Update. > I thought the fixed error was related to running VACUUM FULL, so I disabled running it once a week to prevent currupting my indices. (btw I do not use autovacuum) But I still get occasional error log entries of this kind (one attempt to insert), is the fixed error in 8.1.9 also related to growing index size or is there any other bug burried somewhere. I'm planning to upgrade the upcoming weekend, so I could investigate some more. regards Gerhard
Gerhard Hintermayer wrote: > Tom Lane wrote: >> Gerhard Hintermayer <g.hintermayer@inode.at> writes: >>> ... x should be y >>> I do get these messages one 2 of my servers running mostly on >>> identical data. The servers are unix-based and are running 8.1.8 >>> under linux (Gentoo). >> >> Update. >> > I thought the fixed error was related to running VACUUM FULL, so I > disabled running it once a week to prevent currupting my indices. (btw I > do not use autovacuum) > But I still get occasional error log entries of this kind (one attempt > to insert), is the fixed error in 8.1.9 also related to growing index > size or is there any other bug burried somewhere. I'm planning to > upgrade the upcoming weekend, so I could investigate some more. > > regards > Gerhard Ah, must have been the changes in backend/access/hash/hashovfl.c. Although i never got a PANIC, only an ERROR. I cross my fingers to keep de DB up and running without any data corruption (besides of indices) until the weekend. So a PANIC and backend crash would be possible ? Hopefully under rare circumstances ? Can somebody confirm this ? regards Gerhard