[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@xxxxxxxxxxx Shop To It Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 Canada's Shopping Search Engine! ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@xxxxxxxxxxxxxx ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com |
