DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=cLgxvSNLzDNDVyDUmADjtgxLYralu/9tp1SA9QppQjh91cs5/hrgw7a/ULsSXXXiJ+R/90hoy5KrJwP6NpiKDibvh+uLGptGuywwbmJjYC65dGsSwmf5NnLm1KAMUXUnDtJFo7nSciFe3w92qj0Pk6lnWGmZLWAvOOAxSYX6lwI=
Message-ID: <cbd177510607111747h7542382bke12a6fc152338fe6@mail.gmail.com>
Date: Tue, 11 Jul 2006 17:47:31 -0700
From: aurora <aurora00@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Unexpected SQL error for UPDATE
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_17756_4936150.1152665251583"

------=_Part_17756_4936150.1152665251583
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Originally I have a table like this


create table users (
userid integer not null,
email_address varchar (255) unique not null,
PRIMARY KEY (userid)
);


Later I find that varchar(255) is not enough. I designed to change the type
to
text. There is no simple SQL to alter the type. So I use a series of SQLs to
create a new column, copy the data over and then replace the old column with
the
new.

ALTER TABLE users ADD email_address_text text UNIQUE;
UPDATE users set email_address_text=email_address;
ALTER TABLE users DROP email_address;
ALTER TABLE users RENAME email_address_text TO email_address;
ALTER TABLE users ALTER email_address SET not null;


This works mostly OK. Until I have one database that has over 1 million
records
in table user. It fails with an inexplicable error:


mydb=# UPDATE users set email_address_text=email_address;
ERROR: invalid page header in block 6776 of relation
"users_email_address_text_key"


Anyone can shred some light what has went wrong?

wy

------=_Part_17756_4936150.1152665251583
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Originally I have a table like this<br><br><br>create table users ( <br>                                                                                                                                                          userid integer  not null, 
<br>                                                                                                                                                 email_address varchar (255) unique not null,<br>    PRIMARY KEY (userid) 
<br>                                                                                                                                                  ); <br>                                                                                                                                                                        
<br><br>Later I find that varchar(255) is not enough. I designed to change the type to<br>text. There is no simple SQL to alter the type. So I use a series of SQLs to<br>create a new column, copy the data over and then replace the old column with the
<br>new.<br><br>                                                                                                                                                                           ALTER TABLE users ADD email_address_text text UNIQUE;
<br>UPDATE users set email_address_text=email_address;<br>ALTER TABLE users DROP email_address;<br>ALTER TABLE users RENAME email_address_text TO email_address;<br>ALTER TABLE users ALTER email_address SET not null;<br><br>
<br>This works mostly OK. Until I have one database that has over 1 million records<br>in table user. It fails with an inexplicable error:<br><br><br>mydb=# UPDATE users set email_address_text=email_address;<br>ERROR:  invalid page header in block 6776 of relation &quot;users_email_address_text_key&quot;
<br><br><br>Anyone can shred some light what has went wrong?<br><br>wy<br><br><br><br>

------=_Part_17756_4936150.1152665251583--