运行选择查询时，Postgres不会返回任何数据

Question

我正在运行Postgres 9.22.2并尝试运行以下语句：

从表限制100;

选择 * *

当我这样做时，命令就挂起。PS输出表明它是 等待：

postgres lookup [local] COPY  
postgres: lookup exomeSNP [local] SELECT waiting

我有一个shell脚本，该脚本在常规时间复制了CSV的数据。

以下是PG_STAT_ACTIVITY的前2个记录

datname   |  pid  | state  |                                                                                                                              query                    
------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
 lookup     | 22243 | active | copy cell_info(radio, mcc, net, area, cell, unit, lon, lat, range, samples, changeable, created, updated, average_signal) From '/data/cell-dup.cs
v' DELIMITER ',' CSV HEADER
 lookup     |  6529 | active | SELECT * FROM "public"."cell_info" WHERE "cell"=$1 and “net"=$4 ORDER BY "cell_info_id" LIMIT 1

Answer 1

并发事务在表上持有ACCESS EXCLUSIVE锁定。TRUNCATE，ALTER TABLE或CREATE INDEX等语句需要此类锁。

您可以通过查询pg_locks视图来找出哪些交易。

pg_stat_activity将告诉您该并发交易在做什么。

Answer 2

您可以从Postgres活动日志进行调试

 SELECT datname,pid,state,query FROM pg_stat_activity
Execute this query and find the state of query