在postgres中删除没有pid的锁 [英] Remove locks without a pid in postgres
本文介绍了在postgres中删除没有pid的锁的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在Amazon Web Services RDS上使用PostgreSQL 9.6.1
I am using PostgreSQL 9.6.1 on Amazon Web Services RDS
如何删除下面的锁？我期望删除PID，但在此示例中为空。
How can I remove the locks below? I was expecting a PID to remove, but they are empty in this example.
business_data=> CREATE OR REPLACE VIEW public.active_locks AS
business_data-> SELECT t.schemaname,
business_data-> t.relname,
business_data-> l.locktype,
business_data-> l.page,
business_data-> l.virtualtransaction,
business_data-> l.pid,
business_data-> l.mode,
business_data-> l.granted
business_data-> FROM pg_locks l
business_data-> JOIN pg_stat_all_tables t ON l.relation = t.relid
business_data-> WHERE t.schemaname <> 'pg_toast'::name AND t.schemaname <> 'pg_catalog'::name
business_data-> ORDER BY t.schemaname, t.relname;
CREATE VIEW
business_data=> SELECT * FROM active_locks;
schemaname | relname | locktype | page | virtualtransaction | pid | mode | granted
------------+--------------------------------------+----------+------+--------------------+-----+-----------------+---------
public | passengercar | relation | | -1/226452 | | AccessShareLock | t
public | passengercarcover | relation | | -1/226452 | | AccessShareLock | t
public | passengercarcoveria | relation | | -1/226452 | | AccessShareLock | t
public | passengercardriver | relation | | -1/226452 | | AccessShareLock | t
public | passengercarinsuredamount | relation | | -1/226452 | | AccessShareLock | t
public | passengercarmore | relation | | -1/226452 | | AccessShareLock | t
public | passengercaror | relation | | -1/226452 | | AccessShareLock | t
public | passengercarpassengercar | relation | | -1/226452 | | AccessShareLock | t
public | passengercarrequest | relation | | -1/226452 | | AccessShareLock | t
public | passengercarrequest_requestedcovers | relation | | -1/226452 | | AccessShareLock | t
public | passengercarresponse | relation | | -1/226452 | | AccessShareLock | t
public | passengercarresponse_requestedcovers | relation | | -1/226452 | | AccessShareLock | t
public | passengercarresponseia | relation | | -1/226452 | | AccessShareLock | t
public | passengercarresponseor | relation | | -1/226452 | | AccessShareLock | t
(14 rows)
来自 pg_stat_activity
business_data=> select datname,pid,usename,wait_event,wait_event_type,query from pg_stat_activity;
datname | pid | usename | wait_event | wait_event_type | query
---------------+-------+--------------+------------+-----------------+------------------------------------------------------------------------------------
rdsadmin | 1804 | rdsadmin | | | <insufficient privilege>
business_data | 5384 | bd_admin | | | SELECT 1
bonita | 5650 | bonita_admin | | | SHOW TRANSACTION ISOLATION LEVEL
business_data | 5385 | bd_admin | | | SELECT 1
business_data | 19304 | bd_admin | | | SELECT COUNT(*) FROM (SELECT * FROM "public".passengercarresponse) C2668
bonita | 5497 | bonita_admin | | | SHOW TRANSACTION ISOLATION LEVEL
business_data | 5386 | bd_admin | | | SELECT 1
business_data | 5387 | bd_admin | | | SELECT 1
bonita | 5498 | bonita_admin | | | COMMIT
bonita | 5543 | bonita_admin | | | SHOW TRANSACTION ISOLATION LEVEL
business_data | 5495 | ia_admin | | | select datname,pid,usename,wait_event,wait_event_type,query from pg_stat_activity;
db_l7_umg | 26442 | umg_admin | | | SELECT COUNT(*) FROM (SELECT * FROM dbo.abz) C2668
bonita | 25981 | bonita_admin | | | SELECT gid FROM pg_prepared_xacts
business_data | 25982 | bd_admin | | | SELECT gid FROM pg_prepared_xacts
business_data | 26619 | ia_admin | | | select datname,pid,usename,query from pg_stat_activity where waiting ;
(15 rows)
推荐答案
try identifying who exactly blocks you with this statement:
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process,
blocked_activity.application_name AS blocked_application,
blocking_activity.application_name AS blocking_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
blocking_pid 是要终止的pid
您可以检查您的语句是否被具有较短qry的smbd阻止：
You can check if your statement is blocked by smbd with shorter qry:
t=# select datname,pid,usename,query from pg_stat_activity where waiting ;
datname | pid | usename | query
---------+-------+----------+---------------------
t | 30930 | postgres | select * from so24;
(1 row)
更新：
9.6列等待已被替换为wait_event和wait_event_type，因此查询将是
Update: for 9.6 column waiting was replaced with wait_event and wait_event_type, so the query will be
select datname,pid,usename,query from pg_stat_activity where wait_event is not null;
这篇关于在postgres中删除没有pid的锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
