Thread: Still trouble reindexing

Still trouble reindexing

From

"Henrik Steffen"

Date:

02 July 2003, 06:07:35

Hello all,

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?

--

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
--------------------------------------------------------

Re: Still trouble reindexing

From

Tom Lane

Date:

02 July 2003, 06:58:35

"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

Re: Still trouble reindexing

From

"Henrik Steffen"

Date:

03 July 2003, 16:57:03

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

Re: Still trouble reindexing

From

"Henrik Steffen"

Date:

04 July 2003, 06:01:41

gosh, this night there was a new error message:

DBD::Pg::st execute failed: ERROR:  tuplesort: unexpected end of data [for
statement ``REINDEX TABLE tage200307''])

...starting to believe that this is a hardware error...

--

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: "Henrik Steffen" <steffen@city-map.de>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Thursday, July 03, 2003 12:26 PM
Subject: Re: [GENERAL] Still trouble reindexing


> 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
>

Re: Still trouble reindexing

From

"Henrik Steffen"

Date:

04 July 2003, 06:03:23

however, reindexing manually works fine this morning...

strange things happenning here...

--

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: "Henrik Steffen" <steffen@city-map.de>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Thursday, July 03, 2003 12:26 PM
Subject: Re: [GENERAL] Still trouble reindexing


> 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
>