主页 / user-47518

Theron Luhn's questions

Martin Hope
Theron Luhn
Asked: 2020-08-04 16:41:48 +0800 CST
  • 1

我正在运行 PostgreSQL 11.5。我有一个简单的寄存器表。

create table register(
  id serial primary key,
  name text not null
);

该表很小（约 6000 行），读取相对较重（100 秒查询/秒），几乎没有写入。我想添加一个自引用外键：

alter table register
  add column leader_id integer references register(id);

鉴于桌子的大小，我认为这将是一个相当平凡的变化。它在本地和登台服务器上运行良好。但是，当我针对生产运行它时，表被锁定了。日志显示 ALTER 和各种 SELECT 语句需要大约 10m 才能完成。

2020-08-04 00:01:15 UTC:10.0.2.101(59588):postgres@db:[21609]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: unexpected EOF on client connection with an open transaction
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: duration: 456741.453 ms execute <unnamed>: alter table register
add column leader_id integer references register(id);

2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: could not send data to client: Broken pipe
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: duration: 456635.839 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 123 AND register.account_id = '22781BD1-F37A-4ACE-9A3D-CBF3464AFB43'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: duration: 456656.956 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 234 AND register.account_id = 'A6D8395C-63E8-40A8-A0AE-4F19B1DA5509'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:FATAL: connection to client lost

这里发生了什么导致表锁定？如何安全地使用自引用外键？

postgresql postgresql-11