年夜神请进,JDBC能获取到数据库某个毗连的PID吗?

发布网友 发布时间：2022-05-04 12:00

我来回答

共6个回答

懂视网 时间：2022-05-04 16:22

先看下两个函数的官方解释： pg_cancel_backend() 取消后台操作，回滚未提交事物 pg_terminate_backend() 中断session，回滚未提交事物 这里和oracle类似kill session的操作是pg_terminate_backend() pg_cancel_backend() 举例： session A: postgres=# creat

先看下两个函数的官方解释：



pg_cancel_backend() 取消后台操作，回滚未提交事物 pg_terminate_backend() 中断session，回滚未提交事物 这里和oracle类似kill session的操作是pg_terminate_backend() pg_cancel_backend() 举例：

session A:

postgres=# create table tb1 (a int); CREATE TABLE postgres=# begin;

postgres=# insert into tb1 select generate_series(1,100000000);

session B：

postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+------------------------------------------------------ 12699 | postgres | 10305 | 10 | postgres | psql | | | -1 | 2013-12-03 14:38:05.887116+08 | 2013-12-03 15:12:17 .773935+08 | 2013-12-03 15:14:31.454816+08 | f | insert into tb1 select generate_series(1,100000000);

postgres=# select pg_cancel_backend(10305); pg_cancel_backend ------------------- t

session A： ERROR: canceling statement due to user request postgres=# commit; ROLLBACK postgres=# select * from tb1; a --- (0 rows)

session B:

postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+--------------------------------- 12699 | postgres | 10305 | 10 | postgres | psql | | | -1 | 2013-12-03 14:38:05.887116+08 |

pg_terminate_backend() 举例：

session A：

postgres=# create table tb2 (a int); CREATE TABLE postgres=# begin; BEGIN postgres=# insert into tb2 select generate_series(1,100000000);

session B：

postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+------------------------------------------------------ 12699 | postgres | 10305 | 10 | postgres | psql | | | -1 | 2013-12-03 14:38:05.887116+08 | 2013-12-03 15:24:22 .45073+08 | 2013-12-03 15:24:33.362185+08 | f | insert into tb2 select generate_series(1,100000000);

postgres=# select pg_terminate_backend(10305); pg_terminate_backend ---------------------- t (1 row)

session A：

postgres=# insert into tb2 select generate_series(1,100000000); FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command The connection to the server was lost. Attempting reset: Succeeded. postgres=# commit; WARNING: there is no transaction in progress COMMIT postgres=# select * from tb2; a --- (0 rows)

postgres=# select * from pg_stat_activity;

datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+--------------------------------- 12699 | postgres | 10918 | 10 | postgres | psql | | | -1 | 2013-12-03 15:25:12.613672+08 | 2013-12-03 15:30:29 .544088+08 | 2013-12-03 15:30:29.544088+08 | f | select * from pg_stat_activity;

通过以上实验理解起来应该 很简单了，procpid=10305在pg_cancel_backend()下，session还在，事物回退，在pg_terminate_backend()操作后，session消失，事物回退。如果在某些时候pg_terminate_backend()不能杀死session，那么可以在os层面，直接kill -9 pid

热心网友 时间：2022-05-04 13:30

年迈，你给的这些内容很好，可是这些都是用户毗连完成之后查表获得的信息，而此刻最好是能够在成立毗连的同时就获得这个PID，这样就有了一个一一对应的关系，而如不美观用查到的数据进行配对的话切确性会斗劲差！

热心网友 时间：2022-05-04 14:48

pg_backend_pid()
获取当前毗连的历程号pg_terminate_backend(procpid)是杀死这个毗连pg_cancel_backend(procpid)是终止这个毗连正在运行的呼吁所以你应该用pg_cancel_backend

热心网友 时间：2022-05-04 16:22

这个可以看到PID，可是不知道怎么将这个PID和当前登录的用户连系起来？

热心网友 时间：2022-05-04 18:14

用户登录后，经由过程SELECT
*
FROM
pg_stat_activity取得procpid，这个是独一的，即使用户一再，你测试一下，我是测试经由过程的。

热心网友 时间：2022-05-04 20:22

不成以吗？用户登录->取得登录名保留->如不美观登录成功，经由过程
SELECT
*
FROM
pg_stat_activity按照登录名
查找PID。