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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
