linux上执行postgreSQL 命令无反应的处理方法

问题背景：

项目使用了postgreSQL的作为数据库，要对表进行修改，但发现在linux上修改列字段，迟迟未有响应，经过资料查阅，将问题的解决办法po出，以供以后学习备忘。

---

问题描述

尝试在 Linux 上直接执行postgreSQL命令的方式尝试修改表及其字段。执行命令后迟迟未响应

--首先我使用docker 进入 postgreSQL容器 并进入postgre终端
root@admin123:/home/software# docker exec -it 0a1c0184a52b psql -U root -d test
--进入后 输入\dt 查看所有表
psql (15.6 (Debian 15.6-1.pgdg120+2))
Type "help" for help.

test=# \dt
                       List of relations
 Schema |                 Name                  | Type  | Owner
--------+---------------------------------------+-------+-------
 public | application                           | table | root
 public | application_access_token              | table | root
 public | application_api_key                   | table | root
 public | application_chat                      | table | root
......
--查看表信息
test=# SELECT column_name, data_type, is_nullable
test-# FROM information_schema.columns
test-# WHERE table_schema = 'public' AND table_name = 'application_chat';
  column_name   |        data_type         | is_nullable
----------------+--------------------------+-------------
 create_time    | timestamp with time zone | NO
 update_time    | timestamp with time zone | NO
 id             | uuid                     | NO
 abstract       | character varying        | NO
 application_id | uuid                     | NO
 client_id      | uuid                     | YES
 is_deleted     | boolean                  | NO
(7 rows)
--发现需要加个字段，因此调用ALTER语句
test=# ALTER TABLE application_chat  ADD COLUMN username character varying(50);

执行命令后迟迟未响应

原因分析：

经查阅知道是由于锁表导致，alter语句卡住了，因此需要先查看锁的情况，然后再停止对应的sql

---

执行这个语句：
select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,100) from pg_stat_activity where state <> ‘idle’ order by 3 desc;

test=# select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,100) from pg_stat_activity where state <> 'idle' order by 3 desc;
 pg_blocking_pids |  pid  |    ?column?     | wait_event | wait_event_type |                                                substr
------------------+-------+-----------------+------------+-----------------+------------------------------------------------------------------------------------------------------
 {}               | 61935 | 00:40:47.399196 | ClientRead | Client          |
 {61935}          | 64569 | 00:12:03.908096 | relation   | Lock            | SELECT COUNT(*) AS "__count" FROM "application_chat" WHERE ("application_chat"."application_id" = '9
 {61935}          | 64584 | 00:11:50.548827 | relation   | Lock            | SELECT COUNT(*) AS "__count" FROM "application_chat" WHERE ("application_chat"."application_id" = '9
 {61935}          | 64590 | 00:11:45.4309   | relation   | Lock            | SELECT COUNT(*) AS "__count" FROM "application_chat" WHERE ("application_chat"."application_id" = '9
 {61935}          | 64601 | 00:11:39.516483 | relation   | Lock            | SELECT COUNT(*) AS "__count" FROM "application_chat" WHERE ("application_chat"."application_id" = '9
 {61935}          | 64618 | 00:11:25.634871 | relation   | Lock            | SELECT COUNT(*) AS "__count" FROM "application_chat" WHERE ("application_chat"."application_id" = '9
 {}               | 64681 | 00:00:00        |            |                 | select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,100) fro
(7 rows)

可以看到有5个查询 {61935} 进程号将其卡住了，因此给他停掉。
select pg_terminate_backend(61935);

test=# select pg_terminate_backend(61935);
 pg_terminate_backend
----------------------
 t
(1 row)

test=# select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,100) from pg_stat_activity where state <> 'idle' order by 3 desc;
 pg_blocking_pids |  pid  | ?column? | wait_event | wait_event_type |                                                substr
------------------+-------+----------+------------+-----------------+------------------------------------------------------------------------------------------------------
 {}               | 64681 | 00:00:00 |            |                 | select pg_blocking_pids(pid),pid,now()-xact_start,wait_event,wait_event_type,substr(query,1,100) fro
(1 row)

自此看到没有阻塞进程，执行原SQL，成功，解决问题。

test=# ALTER TABLE application_chat  ADD COLUMN username character varying(50);
ALTER TABLE