Thread: 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!
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
Brendan Duddridge <brendan@clickspace.com> writes: > 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. I'd wonder about some open transaction with a weak lock on these tables. REINDEX needs exclusive lock, vacuum doesn't. Look in pg_locks ... regards, tom lane
Hi Tom, There must have been something like that because when we stopped and started postgres again, it worked. Thanks, ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On May 5, 2006, at 2:06 PM, Tom Lane wrote: > Brendan Duddridge <brendan@clickspace.com> writes: >> 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. > > I'd wonder about some open transaction with a weak lock on these > tables. REINDEX needs exclusive lock, vacuum doesn't. > Look in pg_locks ... > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >