Обсуждение: how can we resolving locking state....

Le 05/08/2010 10:52, 노현석 a écrit :
>  [...]
> we test locking PostgreSQL 8.4.4 on x86_64.
>
> 1> session 1
> ###########################
> $ psql mydb
> drop table citytest;
> CREATE TABLE citytest (
>  i_id integer ,
>  city varchar(80)
> ) ;
> ALTER TABLE citytest ADD CONSTRAINT citytest_pkey PRIMARY KEY (i_id);
> insert into citytest values (1,'aaa');
> insert into citytest values (2,'bbb');
> begin;
> delete from citytest;
>  <------- no commit/rollback
>  <------- this will be Blocker
>
> 2> session 2,3,4
> ###########################
> $ psql mydb
> delete from citytest;
>  <--- waiting
>  <--- these are blocked....
>
> 3> os process
> ###########################
> we just kill Blocker process for resolving locking....
>  but, the other process also disappear ...
>
> $ ps -ef| grep postgres
> post1 4921 1 .. /u01/post1/bin/postgres -D /u02/post1data
> post1 4924 4921 .. postgres: writer process
> post1 4925 4921 .. postgres: wal writer process
> post1 4926 4921 .. postgres: autovacuum launcher process
> post1 4927 4921 .. postgres: archiver process
> post1 4928 4921 .. postgres: stats collector process
> post1 5143 4921 .. postgres: post1 mydb [local] idle in transaction <### Blocker
> post1 5591 4921 .. postgres: post1 mydb 127.0.0.1(33982) DELETE waiting <### blocking
> post1 5592 4921 .. postgres: post1 mydb 127.0.0.1(33983) DELETE waiting <### blocking
> post1 5593 4921 .. postgres: post1 mydb 127.0.0.1(33984) DELETE waiting <### blocking
> post1 5738 329 .. grep postgres
> $
> $ kill -9 5143
> $
> $ ps -ef| grep postgres
> post1 4921 1 0 17:35 pts/2 00:00:00 /u01/post1/bin/postgres -D /u02/post1data
> post1 10905 4921 0 17:39 ? 00:00:00 postgres: writer process
> post1 10906 4921 0 17:39 ? 00:00:00 postgres: wal writer process
> post1 10907 4921 0 17:39 ? 00:00:00 postgres: autovacuum launcher process
> post1 10908 4921 0 17:39 ? 00:00:00 postgres: archiver process
> post1 10909 4921 0 17:39 ? 00:00:00 postgres: stats collector process
> post1 10989 329 0 17:39 pts/2 00:00:00 grep postgres
> $
>
> Could you teach me, Is this expected behavior ?? (disapper blocking process not only Blocker process)
> and
> Could you teach me, how can we eliminate just Blocker session...
>

Never use "kill -9" on a PostgreSQL process. If you do this, PostgreSQL
will stop all its processes, and will try to restart.

On 8.4, you can use pg_cancel_backend(pid) to cancel a query, and
pg_terminate_backend(pid) to terminate a connection. See
http://www.postgresql.org/docs/8.4/static/functions-admin.html for details.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

=?EUC-KR?B?s+vH9ryu?=<noh019@naver.com> writes:
> $ kill -9 5143

[ and all the other backends disappear too ]

> Could you teach me, Is this expected behavior ?

Yes.  That is not the recommended way to kill a backend.
Try pg_terminate_backend().

            regards, tom lane

Hi,

On Thu, 05 Aug 2010 17:52:44 +0900 노현석 wrote:

> we test locking PostgreSQL 8.4.4 on x86_64.
>

[...]

> begin;
> delete from citytest;
>  <------- no commit/rollback
>  <------- this will be Blocker
>
> 2> session 2,3,4
> ###########################
> $ psql mydb
> delete from citytest;
>  <--- waiting
>  <--- these are blocked....

[...]

> Could you teach me, Is this expected behavior ?? (disapper blocking
> process not only Blocker process) and
> Could you teach me, how can we eliminate just Blocker session...

Note: the cause for the blocked sessions is in your first session. By
deleting all entries from table "citytest", PG will lock all deleted
entries. Once you try to delete one or all of this rows in another
session, PG waits until the first session is either committed or rolled
back.

This is expected behaviour.


Bye

--
                Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
 (Ferenc Mantfeld)

Please post in plain text if possible.

2010/8/5 노현석 <noh019@naver.com>
>
> hi..
>
> we test   locking      PostgreSQL 8.4.4 on x86_64.
> $
> $   kill -9   5143

What you want is plain old

kill 5143
or
kill -SIGTERM 5143

Kill -SIGTERM is like using a fly swatter to kill a fly.  SIGKILL (-9)
is like lobbing a grenade in the same room to kill said fly.

Note that in 8.4 and up there's a pg function to do this.  8.3 and
before kill -SIGTERM is what you want.