不灭的火

1、查询正在执行的会话

SELECT pid, usename, application_name, client_addr, backend_start, query_start, state_change, state, query
FROM pg_stat_activity 
WHERE datname='db_informat_biz_prd_0'

2、杀死会话

SELECT pg_terminate_backend(9097)
SELECT pg_terminate_backend(1156)

SELECT pid, usename, application_name, client_addr, backend_start, query_start, state_change, state, query
FROM pg_stat_activity 
WHERE datname='db_informat_biz_prd_0'

AND (query LIKE '%COMMIT%' OR query LIKE '%update%');


-------------------------------------------------------


SELECT pg_terminate_backend(15892);

工作中的案例

阿里云RDS数据库后台记录一个慢查询：耗时 177.7 秒

UPDATE "z_0Z6t6t2WjrRREcQ8"
  SET "0Z6t6t2WjrRRJBzl" = '6faC1LaIUBlHrr',
  "0Z6t6t2WjrRRJDxc" = '2025-09-20 18:02:06.523',
  "0Z6t6t2WjrRRJn94" = '23.31'
WHERE
  1 = 1
  AND id = '0Z6t6taXbejqctyP'

查询作用在某个表（如：z_0Z6t6t2WjrRREcQ8）上的“表锁”和“行锁”

SELECT 
    c.relname AS table_name,
    l.locktype,
    l.mode,
    l.pid,
    l.granted
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
WHERE l.locktype IN ('relation', 'tuple') AND  c.relname='z_0Z6t6t2WjrRREcQ8';

杀死进程：

SELECT pg_terminate_backend(26882);

慢查询SQL

select a."id",a."table_id",l2."name" as "module_name",a."company_id",a."application_id",a."name",a."icon",a."type",a."group_id",a."setting"::text,a."remark",a."is_private_visible",a."private_visible",a."is_private_edit",a."private_edit",a."is_condition_visible",a."row_number",a."display_width",a."is_hidden",a."is_system_field",a."create_account_id",a."update_account_id",a."create_time",a."update_time",a."condition_visible_filter",a."is_condition_readonly",a."condition_readonly_filter",a."alias_name",l3."avatar" as "create_account_avatar",l3."name" as "create_account_name" 
from "t_table_field" a 
left join  "t_table" l1 on a."table_id"=l1."id" 
left join  "t_application_module" l2 on l1."id"=l2."id" 
left join  "t_biz_account" l3 on a."create_account_id"=l3."id" 
where 1=1 
 and ( a."company_id" = $1  and a."type" = $2  and l2."is_delete" = $3  and a.setting::jsonb@>'{"targetTableId":"0Z6t6t2WKu75KAM4"}' and a.setting::jsonb@>'{"targetFieldId":"0Z6t6t3baiiad9Gh"}'  )   
order by  a.create_time desc  
limit 2147483647 offset 0 

格式化：

SELECT
  a."id",
  a."table_id",
  l2."name" AS "module_name",
  a."company_id",
  a."application_id",
  a."name",
  a."icon",
  a."type",
  a."group_id",
  a."setting" :: TEXT,
  a."remark",
  a."is_private_visible",
  a."private_visible",
  a."is_private_edit",
  a."private_edit",
  a."is_condition_visible",
  a."row_number",
  a."display_width",
  a."is_hidden",
  a."is_system_field",
  a."create_account_id",
  a."update_account_id",
  a."create_time",
  a."update_time",
  a."condition_visible_filter",
  a."is_condition_readonly",
  a."condition_readonly_filter",
  a."alias_name",
  l3."avatar" AS "create_account_avatar",
  l3."name" AS "create_account_name"
FROM
  "t_table_field" a
  LEFT JOIN "t_table" l1 ON a."table_id" = l1."id"
  LEFT JOIN "t_application_module" l2 ON l1."id" = l2."id"
  LEFT JOIN "t_biz_account" l3 ON a."create_account_id" = l3."id"
WHERE
  1 = 1
  AND (
    a."company_id" = '0Z6tVkW5WURmGNN'
    AND a."type" = 'SingleText'
    AND l2."is_delete" = 'f'
    AND a.setting :: JSONB @> '{"targetTableId":"0Z6t6t2WKu75KAM4"}'
    AND a.setting :: JSONB @> '{"targetFieldId":"0Z6t6t3baiiad9Gh"}'
  )
ORDER BY
  a.create_time DESC
  LIMIT 2147483647 OFFSET 0