can't reindex a couple of tables

[I apologize if this message gets out twice. I accidentally sent the first one from the wrong email address]

Hi,

We're having a problem with a couple of our tables.

I have 2 tables, attribute and attribute_value:

                 Table "public.attribute"

        Column         |         Type          | Modifiers

-----------------------+-----------------------+-----------

attribute_id          | integer               | not null

attribute_type        | character varying(32) |

attribute_unit_id     | integer               |

click_count           | integer               |

feature_group         | character varying(5)  |

parent_attribute_id   | integer               |

promote_value         | character varying(5)  |

reference_category_id | integer               |

sort_order            | integer               |

is_visible            | character varying(5)  |

Indexes:

    "attribute_pk" PRIMARY KEY, btree (attribute_id)

    "attribute__attribute_unit_id_fk_idx" btree (attribute_unit_id)

    "attribute__parent_attribute_id_fk_idx" btree (parent_attribute_id)

    "attribute__reference_category_id_fk_idx" btree (reference_category_id)

Foreign-key constraints:

    "attribute_attributeunit_fk" FOREIGN KEY (attribute_unit_id) REFERENCES attribute_unit(attribute_unit_id) DEFERRABLE INITIALLY DEFERRED

    "attribute_parentattribute_fk" FOREIGN KEY (parent_attribute_id) REFERENCES attribute(attribute_id) DEFERRABLE INITIALLY DEFERRED

    "attribute_referencecategory_fk" FOREIGN KEY (reference_category_id) REFERENCES category(category_id) DEFERRABLE INITIALLY DEFERRED

              Table "public.attribute_value"

        Column         |         Type         | Modifiers

-----------------------+----------------------+-----------

attribute_id          | integer              | not null

attribute_unit_id     | integer              |

attribute_value_id    | integer              | not null

boolean_value         | character varying(5) |

click_count           | integer              |

do_keyphrase_matching | character varying(5) |

max_value             | numeric(30,10)       |

min_value             | numeric(30,10)       |

Indexes:

    "attribute_value_pk" PRIMARY KEY, btree (attribute_value_id)

    "attribute_value__attribute_id_fk_idx" btree (attribute_id)

    "attribute_value__attribute_unit_id_fk_idx" btree (attribute_unit_id)

Foreign-key constraints:

    "attribute_value_attribute_fk" FOREIGN KEY (attribute_id) REFERENCES attribute(attribute_id) DEFERRABLE INITIALLY DEFERRED

    "attribute_value_attributeunit_fk" FOREIGN KEY (attribute_unit_id) REFERENCES attribute_unit(attribute_unit_id) DEFERRABLE INITIALLY DEFERRED

They both have a click_count column that we update with "update attribute set click_count = click_count + 1;" and the same for the attribute_value table. Postgres is getting hung up on any transaction that attempts to update the click_count. I've vacuum analyzed both tables and that worked fine. Now I tried to reindex them and Postgres is just locking up and never finishing. I had to cancel the reindex.

The attribute table has only 3434 rows in it.

The attribute_value table has only 548735 in it.

Either one I try to reindex causes that Postgres connection to hang until I cancel the reindex command.

Reindexing other tables works fine.

Are these two tables corrupt or something? Is there a way to fix them? I thought about dumping them and re-loading them, but I don't know how to do that due to all the referential integrity issues from the other tables that reference these two tables.

Thanks,

brendan duddridge | CTO | 403-520-5793 x24 | brendan@shoptoit.ca

Shop To It Inc.

Suite L100, 239 - 10th Ave. SE

Calgary, AB T2G 0V9

Canada's Shopping Search Engine!