Thread: Unexpected SQL error for UPDATE

Originally I have a table like this<br /><br /><br />create table users ( <br /> userid integer not null, <br />
email_addressvarchar (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
usea 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_textTO email_address;<br />ALTER TABLE users ALTER email_address SET not null;<br /><br /><br />This
worksmostly OK. Until I have one database that has over 1 million records<br />in table user. It fails with an
inexplicableerror:<br /><br /><br />mydb=# UPDATE users set email_address_text=email_address;<br />ERROR: invalid page
headerin block 6776 of relation "users_email_address_text_key" <br /><br /><br />Anyone can shred some light what has
wentwrong?<br /><br />wy<br /><br /><br /><br /> 

On Jul 11, 2006, at 20:47 , aurora wrote:

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

What version of PostgreSQL are you running? In 8.0 and later you can  
use ALTER TABLE ALTER TYPE.

http://www.postgresql.org/docs/current/interactive/sql-altertable.html

Something like this (untested) should work in your case:

ALTER TABLE users ALTER email_addresses TYPE text;

> 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 looks like it should work, so perhaps someone else has an idea  
as to why you're receiving the error. Is the error reproducible? If  
you can put together a reproducible test case, it may help someone  
else be able to figure out what's going on.

Michael Glaesemann
grzm seespotcode net

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

users_email_address_text_key sounds a lot like an index... is it an
index? if so, what happenned if you REINDEX the table or even DROP and
CREATE the index again? it certainly sounds to an index corruption.

-- 
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook

Yes that helped!

  reindex index users_email_address_text_key;
  UPDATE users set email_address_text=email_address;

It kind of works now. Or at least it does not result in error. The UPDATE query is running for more than an hour and still hasn't return. This is inline 
with what we have seen before that coping 1 million fields seems to be 
excessively slow.

About the index, it is created implicitly because of the UNIQUE constraint. At this point right after the column is added, they are all null.

wy

On 7/12/06, aurora <aurora00@gmail.com> wrote:
> Yes that helped!
>
>   reindex index users_email_address_text_key;
>

what version of postgres is this? there have been some bug fixes that
involved indexes on text columns.

wich locale are you using?

-- 
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook