Re: App very unresponsive while performing simple update

On Sun, May 28, 2006 at 07:20:59PM -0400, Greg Stark wrote:
> Brendan Duddridge <brendan(at)clickspace(dot)com> writes:
>
> > We do have foreign keys on other tables that reference the product table.
> > Also, there will be updates going on at the same time as this update. When
> > anyone clicks on a product details link, we issue an update statement to
> > increment the click_count on the product. e.g. update product set click_count
> > = click_count + 1;
>
> You should realize this will produce a lot of garbage records and mean you'll
> have to be running vacuum very frequently. You might consider instead of
> updating the main table inserting into a separate clickstream table. That
> trades off not getting instantaneous live totals with isolating the
> maintenance headache in a single place. That table will grow large but you can
> prune it at your leisure without impacting query performance on your main
> tables.

Actually, you can still get instant results, you just have to hit two
tables to do it.

> More likely you were blocking on some lock. Until that other query holding
> that lock tries to commit Postgres won't actually detect a deadlock, it'll
> just sit waiting until the lock becomes available.

Wow, are you sure that's how it works? I would think it would be able to
detect deadlocks as soon as both processes are waiting on each other's
locks.
--
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