hi..
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...
Thanks....
noh019님의 블로그
안녕하세요.