Re: can't reindex a couple of tables

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461