By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,179 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,179 IT Pros & Developers. It's quick & easy.

Error with table

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.