postgresql - 删除 postgres 中没有 pid 的锁

Question

我在 Amazon Web Services RDS 上使用 PostgreSQL 9.6.1

如何解除下面的锁？我原本期望删除 PID，但在本例中它们是空的。

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)

Answer 1

尝试找出到底是谁阻止了您 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 阻止:

t=# select datname,pid,usename,query from pg_stat_activity where waiting ;
 datname |  pid  | usename  |        query
---------+-------+----------+---------------------
 t       | 30930 | postgres | select * from so24;
(1 row)

更新: for 9.6 column waiting was replaced与 wait_event 和 wait_event_type，所以查询将是

select datname,pid,usename,query from pg_stat_activity where wait_event is not null;