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

http://www.shoptoit.ca

Canada's Shopping Search Engine!




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Fri, May 05, 2006 at 12:41:56PM -0600, Brendan Duddridge wrote:
> 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'm sure it would finish if you gave it enough time. Why are you
reindexing?

> 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.

Try looking in pg_locks. Reindex needs an exclusive lock IIRC, so
anything else that's hitting the table will have to finish before the
reindex can start.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hi Jim,

I must have had a corrupt index as vacuum analyze verbose came back
with an error and crapped out the postgres instance. Once I re-
indexed the problem went away. This issue hasn't returned since re-
indexing.

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

http://www.shoptoit.ca

Canada's Shopping Search Engine!


On May 15, 2006, at 2:27 PM, Jim C. Nasby wrote:

> On Fri, May 05, 2006 at 12:41:56PM -0600, Brendan Duddridge wrote:
>> 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'm sure it would finish if you gave it enough time. Why are you
> reindexing?
>
>> 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.
>
> Try looking in pg_locks. Reindex needs an exclusive lock IIRC, so
> anything else that's hitting the table will have to finish before the
> reindex can start.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org