pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnexpected SQL error for UPDATE
5+ messages / 3 participants
[nested] [flat]
* Unexpected SQL error for UPDATE
@ 2006-07-12 00:47 aurora <aurora00@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: aurora @ 2006-07-12 00:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unexpected SQL error for UPDATE
@ 2006-07-12 12:18 Michael Glaesemann <grzm@seespotcode.net>
parent: aurora <aurora00@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2006-07-12 12:18 UTC (permalink / raw)
To: aurora <aurora00@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unexpected SQL error for UPDATE
@ 2006-07-12 13:12 Jaime Casanova <systemguards@gmail.com>
parent: aurora <aurora00@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Jaime Casanova @ 2006-07-12 13:12 UTC (permalink / raw)
To: aurora <aurora00@gmail.com>; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unexpected SQL error for UPDATE
@ 2006-07-12 18:39 aurora <aurora00@gmail.com>
parent: Jaime Casanova <systemguards@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: aurora @ 2006-07-12 18:39 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-sql
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, Jaime Casanova <systemguards@gmail.com> wrote:
>
> 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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Unexpected SQL error for UPDATE
@ 2006-07-13 01:48 Jaime Casanova <systemguards@gmail.com>
parent: aurora <aurora00@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Jaime Casanova @ 2006-07-13 01:48 UTC (permalink / raw)
To: aurora <aurora00@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-13 01:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-12 00:47 Unexpected SQL error for UPDATE aurora <aurora00@gmail.com>
2006-07-12 12:18 ` Michael Glaesemann <grzm@seespotcode.net>
2006-07-12 13:12 ` Jaime Casanova <systemguards@gmail.com>
2006-07-12 18:39 ` aurora <aurora00@gmail.com>
2006-07-13 01:48 ` Jaime Casanova <systemguards@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox