PostgreSQL中的Alter Table太慢了 [英] Alter Table too slow in postgres

问题描述

我正在尝试添加新列

ALTER TABLE "Cidade" ADD COLUMN "BoundBox" VARCHAR(255)

到此表：

 "Cidade"
    "Id" integer not null
        constraint "Cidade_PK"
            primary key,
    "Nome" varchar(120),
    "EstadoId" integer not null
        constraint "Estado_Cidade_FK"
            references "Estado",
    "PontoCentralLatitude" numeric,
    "PontoCentralLongitude" numeric

但是查询从未完成，我已经等了5分钟，什么也没发生。该表格只有5,000条记录，由于它阻止了对该表格的访问，我迫不及待地等待了太多时间。
我有一个测试数据库（等于生产版），并且运行非常迅速。
postgres版本是9.5.6。

But the query never finish, I've already waited for 5 minutes and nothing happened. The table has only 5,000 records, and I can't wait too much time since it block the access to the table. I have a test database (equal to production), and it worked very quickly. The postgres version is 9.5.6.

推荐答案

如果您运行的是PostgreSQL 9.6+，则可以使用 pg_blocking_pids（）来查找锁定您的查询的PID。

If you are running PostgreSQL 9.6+ you can use pg_blocking_pids() to find PID of queries that lock your one.

select pid, pg_blocking_pids(pid) as blocked_by, query as blocked_query
from pg_stat_activity
where pg_blocking_pids(pid)::text != '{}';

这篇关于PostgreSQL中的Alter Table太慢了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！