Обсуждение: 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;

On Sat, Dec 4, 2010 at 11:45 AM, felix <crucialfelix@gmail.com> wrote:
> 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

Not sure if anyone replied about killing your query, but you can do it like so:

select pg_cancel_backend(5902);  -- assuming 5902 is the pid of the
query you want canceled.

On Mon, Dec 6, 2010 at 1:46 PM, bricklen <bricklen@gmail.com> wrote:
> On Sat, Dec 4, 2010 at 11:45 AM, felix <crucialfelix@gmail.com> wrote:
>> 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
>
> Not sure if anyone replied about killing your query, but you can do it like so:
>
> select pg_cancel_backend(5902);  -- assuming 5902 is the pid of the
> query you want canceled.

How does this differ from just killing the pid?

--
Jon

On Mon, Dec 6, 2010 at 2:48 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> On Mon, Dec 6, 2010 at 1:46 PM, bricklen <bricklen@gmail.com> wrote:
>> Not sure if anyone replied about killing your query, but you can do it like so:
>>
>> select pg_cancel_backend(5902);  -- assuming 5902 is the pid of the
>> query you want canceled.
>
> How does this differ from just killing the pid?

pg_cancel_backend(5902) does the same thing as:
  kill -SIGINT 5902

Josh

On Mon, Dec 06, 2010 at 03:24:31PM -0500, Josh Kupershmidt wrote:
> On Mon, Dec 6, 2010 at 2:48 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> > On Mon, Dec 6, 2010 at 1:46 PM, bricklen <bricklen@gmail.com> wrote:
> >> Not sure if anyone replied about killing your query, but you can do it like so:
> >>
> >> select pg_cancel_backend(5902); ?-- assuming 5902 is the pid of the
> >> query you want canceled.
> >
> > How does this differ from just killing the pid?
>
> pg_cancel_backend(5902) does the same thing as:
>   kill -SIGINT 5902
>
> Josh
>

Yes, but you can use it from within the database. The kill command
requires shell access to the backend.

Cheers,
Ken

On Mon, Dec 6, 2010 at 4:31 PM, felix <crucialfelix@gmail.com> wrote:
>
> thanks for the replies !,
> but actually I did figure out how to kill it
> but pb_cancel_backend didn't work.  here's some notes:
> this has been hung for 5 days:
> ns      |   32681 | nssql   | <IDLE> in transaction | f       | 2010-12-01
> 15

Right, pg_cancel_backend() isn't going to help when the session you're
trying to kill is '<IDLE> in transaction' -- there's no query to be
killed. If this '<IDLE> in transaction' session was causing problems
by blocking other transactions, you should look at the application
running these queries and figure out why it's hanging out in this
state. Staying like that for 5 days is not a good sign, and can cause
also problems with e.g. autovacuum.

[snip]

> but it still will not die
> the docs for pg_ctl state:
> "Use pb_ctl --help to see a list of supported signal names."
> doing so does indeed tell me the names:
> HUP INT QUIT ABRT TERM USR1 USR2
> but nothing about them whatseover :)

I agree this could be better documented. There's a brief mention at:
  http://www.postgresql.org/docs/current/static/app-postgres.html#AEN77350
  "To cancel a running query, send the SIGINT signal to the process
running that command."

though that snippet of information is out-of-place on a page about the
postmaster, and SIGINT vs. SIGTERM for individual backends isn't
discussed there at any rate.

At any rate, as you discovered, you have to send SIGTERM to the
backend to kill off an '<IDLE> in transaction' session. If you're
using 8.4 or newer, you have pg_terminate_backend() as a SQL wrapper
for SIGTERM. If you're using an older version, be careful, see e.g.
  http://archives.postgresql.org/pgsql-admin/2010-04/msg00274.php

Josh