Thread: Error with table

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

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