Re: Update problem on large table

Ok, I caught one : an update that is stuck in waiting.

the first one blocks the second one.

ns | 5902 | nssql | UPDATE "fastadder_fastadderstatus" SET "built"
= false WHERE "fastadder_fastadderstatus"."service_id" = 1

| f |
2010-12-04 13:44:38.5228-05 | 127.0.0.1

ns | 7000 | nssql | UPDATE "fastadder_fastadderstatus" SET
"last_sent" = E'2010-12-04 13:50:51.452800', "sent" = true WHERE
("fastadder_fastadderstatus"."built" = true AND
"fastadder_fastadderstatus"."service_id" = 1 )

| t | 2010-12-04 13:50:51.4628-05
| 127.0.0.1

is it possible to release the lock and/or cancel the query ? the process
that initiated the first one is long ceased.

On Fri, Nov 26, 2010 at 6:02 PM, bricklen <bricklen(at)gmail(dot)com> wrote:

> No problem!
>
> On Fri, Nov 26, 2010 at 8:34 AM, felix <crucialfelix(at)gmail(dot)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(at)gmail(dot)com> wrote:
> >>
> >> On Fri, Nov 26, 2010 at 6:22 AM, felix <crucialfelix(at)gmail(dot)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(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-performance
> >
> >
>