Still trouble reindexing

"Henrik Steffen" <steffen@city-map.de> writes:

this night I got the following error message while
reindexing the same table that hat non-unique values
the last days:

DBD::Pg::st execute failed:
ERROR: index_formtuple: data takes 536870936 bytes, max is 8191 [for
statement ``REINDEX TABLE kundenstatistik''])

Any hint what that could mean?

Ugh :-( ... looks like corrupt data to me, specifically a bad length
word in a variable-length field. Have you checked your disk hardware
lately?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

hi tom,

the table consists of a unique char(9) column
with about 250.000 rows, and some integer columns.

it was not possible to dump the table nor
to drop and recreate the index (same error
as reindex)

it was not even possible to insert into newtable select * from table

the copy command delivered only about 100.000 rows
and stopped then with an error.

i then tried to
select from table where kundennummer like '01%' order by kundennummer;
which worked fine, however
select from table where kundennummer like '14%' order by kundennummer;
did run into an error

so I tried to come closer to the error adding more and more numbers
to the like-part. leading to kundennummer like '14010157%', which
I then deleted. I lost only 9 rows. Better then 250.000 rows.

then everything worked fine again.

guess postgres saved some invalid data into these particular rows.

shouldn't it be necessary to prevent postgres from inserting
a 536870936 bytes long value in to a 8192 byte column?

I am not sure if this (again) is a hardware issue...

--

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Wednesday, July 02, 2003 8:57 AM
Subject: Re: [GENERAL] Still trouble reindexing

"Henrik Steffen" <steffen@city-map.de> writes:

this night I got the following error message while
reindexing the same table that hat non-unique values
the last days:

DBD::Pg::st execute failed:
ERROR: index_formtuple: data takes 536870936 bytes, max is 8191 [for
statement ``REINDEX TABLE kundenstatistik''])

Any hint what that could mean?

Ugh :-( ... looks like corrupt data to me, specifically a bad length
word in a variable-length field. Have you checked your disk hardware
lately?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

hi tom,

the table consists of a unique char(9) column
with about 250.000 rows, and some integer columns.

it was not possible to dump the table nor
to drop and recreate the index (same error
as reindex)

it was not even possible to insert into newtable select * from table

the copy command delivered only about 100.000 rows
and stopped then with an error.

i then tried to
select from table where kundennummer like '01%' order by kundennummer;
which worked fine, however
select from table where kundennummer like '14%' order by kundennummer;
did run into an error

so I tried to come closer to the error adding more and more numbers
to the like-part. leading to kundennummer like '14010157%', which
I then deleted. I lost only 9 rows. Better then 250.000 rows.

then everything worked fine again.

guess postgres saved some invalid data into these particular rows.

shouldn't it be necessary to prevent postgres from inserting
a 536870936 bytes long value in to a 8192 byte column?

I am not sure if this (again) is a hardware issue...

--

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Wednesday, July 02, 2003 8:57 AM
Subject: Re: [GENERAL] Still trouble reindexing

"Henrik Steffen" <steffen@city-map.de> writes:

this night I got the following error message while
reindexing the same table that hat non-unique values
the last days:

DBD::Pg::st execute failed:
ERROR: index_formtuple: data takes 536870936 bytes, max is 8191 [for
statement ``REINDEX TABLE kundenstatistik''])

Any hint what that could mean?

Ugh :-( ... looks like corrupt data to me, specifically a bad length
word in a variable-length field. Have you checked your disk hardware
lately?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org