Error with table

[Jon Earle]

Hi,

Had a problem with PsotgreSQL v7.3.4. I had a table that, after a while,
decided to give me a fit:

db=> insert into blocklist values ('2', 'km4n7s28ehiFizeYupm93Q',
'1','2','3');
ERROR: Index idx_blk_id_addr is not a btree

When I deleted and recreated the table, it worked fine. Any ideas?

Table was initially defined thus:

db=> \d blocklist
Table "public.blocklist"
Column | Type | Modifiers
------------+-------------------+-------------
id | integer | not null
id_addr | character varying |
wl | integer | default '0'
bl | integer | default '0'
bl_gen | integer | default '0'
Indexes: idx_blk_usr_adr unique btree (id, id_addr),
idx_blk_id btree (id),
idx_blk_id_addr btree (id_addr)
Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES users(id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (id_addr) REFERENCES
addresses(id_addr) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE

After I recreated it, it appears the same:

db=> \d blocklist
Table "public.blocklist"
Column | Type | Modifiers
------------+-------------------+-------------
id | integer | not null
id_addr | character varying |
wl | integer | default '0'
bl | integer | default '0'
bl_gen | integer | default '0'
Indexes: idx_blk_usr_adr unique btree (id, id_addr),
idx_blk_id btree (id),
idx_blk_id_addr btree (id_addr)
Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES users(id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (id_addr) REFERENCES
addresses(id_addr) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE

This table's definition has not changed, but there were a few scripted
operations to update other tables in the db. Those operations completed
successfully.

Cheers!
Jon

--
Jon Earle

SAVE FARSCAPE http://www.savefarscape.com/

Vegetarian - an old Indian word meaning 'lousy hunter'.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[scott.marlowe]

such errors are almost always a result of on disk corruption, i.e. bad
blocks or bad memory, and every so often a little known bug or two that
pgsql has had. There are no known data loss bugs in 7.3.4 I know of,
except with hash indexes MAYBE in certain full moon circumstances.

You might wanna check your hardware (disks and memory) to make sure it
doesn't get little errors every now and then.

On Fri, 12 Dec 2003, Jon Earle wrote:

Hi,

Had a problem with PsotgreSQL v7.3.4. I had a table that, after a while,
decided to give me a fit:

db=> insert into blocklist values ('2', 'km4n7s28ehiFizeYupm93Q',
'1','2','3');
ERROR: Index idx_blk_id_addr is not a btree

When I deleted and recreated the table, it worked fine. Any ideas?

Table was initially defined thus:

db=> \d blocklist
Table "public.blocklist"
Column | Type | Modifiers
------------+-------------------+-------------
id | integer | not null
id_addr | character varying |
wl | integer | default '0'
bl | integer | default '0'
bl_gen | integer | default '0'
Indexes: idx_blk_usr_adr unique btree (id, id_addr),
idx_blk_id btree (id),
idx_blk_id_addr btree (id_addr)
Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES users(id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (id_addr) REFERENCES
addresses(id_addr) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE

After I recreated it, it appears the same:

db=> \d blocklist
Table "public.blocklist"
Column | Type | Modifiers
------------+-------------------+-------------
id | integer | not null
id_addr | character varying |
wl | integer | default '0'
bl | integer | default '0'
bl_gen | integer | default '0'
Indexes: idx_blk_usr_adr unique btree (id, id_addr),
idx_blk_id btree (id),
idx_blk_id_addr btree (id_addr)
Foreign Key constraints: $1 FOREIGN KEY (id) REFERENCES users(id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (id_addr) REFERENCES
addresses(id_addr) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE

This table's definition has not changed, but there were a few scripted
operations to update other tables in the db. Those operations completed
successfully.

Cheers!
Jon

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend