Re: Update problem on large table

No problem!

On Fri, Nov 26, 2010 at 8:34 AM, felix <crucialfelix@gmail.com> wrote:
> thanks !
> of course now, 2 hours later, the queries run fine.
> the first one was locked up for so long that I interrupted it.
> maybe that caused it to get blocked
> saved your query for future reference, thanks again !
> On Fri, Nov 26, 2010 at 5:00 PM, bricklen <bricklen@gmail.com> wrote:
>>
>> 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;
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>