Обсуждение: can't reindex a couple of tables

Поиск
Список
Период
Сортировка

can't reindex a couple of tables

От
Brendan Duddridge
Дата:
[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 

Re: can't reindex a couple of tables

От
Tom Lane
Дата:
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

Re: can't reindex a couple of tables

От
Brendan Duddridge
Дата:
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
>