Re: Update problem on large table

On Fri, Nov 26, 2010 at 6:22 AM, felix <crucialfelix@gmail.com> wrote:
>
> Hello,
> I have a very large table that I'm not too fond of.  I'm revising the design
> now.
> Up until now its been insert only, storing tracking codes from incoming
> webtraffic.
> It has 8m rows
> It appears to insert fine, but simple updates using psql are hanging.
> update ONLY traffic_tracking2010 set src_content_type_id = 90 where id =
> 90322;
> I am also now trying to remove the constraints, this also hangs.
> alter table traffic_tracking2010 drop constraint
> traffic_tracking2010_src_content_type_id_fkey;
> thanks in advance for any advice.

Try your update or alter and in another session, run the following
query and look at the "waiting" column. A "true" value means that that
transaction is blocked.

SELECT pg_stat_activity.datname, pg_stat_activity.procpid,
pg_stat_activity.usename, pg_stat_activity.current_query,
pg_stat_activity.waiting,
pg_stat_activity.query_start,pg_stat_activity.client_addr
FROM pg_stat_activity
WHERE ((pg_stat_activity.procpid <> pg_backend_pid())
AND (pg_stat_activity.current_query <> '<IDLE>'))
ORDER BY pg_stat_activity.query_start;