我在表 T 和 T_CHILD 之间有父/子关系。当我有一个活动事务从父表 - T 中选择，并且我尝试删除子表 (T_CHILD) 时，DROP 语句只是挂起，因为此会话尝试在父表和子表上获取访问独占锁。
1. 这是预期行为吗？
2. 为什么 Postgres 应该防止删除子表，即使删除子表对父表上的 SELECT 没有影响？ 你能解释一下吗？
dev_sporuran=# d+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "t_child" CONSTRAINT "t_child_fk" FOREIGN KEY (t_id) REFERENCES t(id)
dev_sporuran=# d+ t_child
Table "public.t_child"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
t_id | integer | | plain | |
name | character varying(10) | | extended | |
Foreign-key constraints:
"t_child_fk" FOREIGN KEY (t_id) REFERENCES t(id)
***********26727 session starts a SELECT ********
dev_sporuran=# begin;
BEGIN
dev_sporuran=# select * from t;
id
----
(0 rows)
*****26727 gets a shared lock for the SELECT on the table and the PK******
dev_sporuran=# select locktype,database,relation::regclass,transactionid,pid,mode,granted
from pg_locks where pid in (26727,26780) order by pid;
locktype | database | relation | transactionid | pid | mode | granted
------------+----------+----------+---------------+-------+-----------------+---------
relation | 19752133 | pg_locks | | 26727 | AccessShareLock | t
relation | 19752133 | t_pkey | | 26727 | AccessShareLock | t
relation | 19752133 | t | | 26727 | AccessShareLock | t
virtualxid | | | | 26727 | ExclusiveLock | t
(4 rows)
**************26827 tries to DROP the t_child table ************
dev_sporuran=# select pg_backend_pid();
pg_backend_pid
----------------
26867
(1 row)
dev_sporuran=# drop table t_child;
********* This just hangs;
Looking at pg_locks - 26867 requires not only an Access Exclusive lock on t_child, it also needs an Access Exclusive Lock on the parent table - T.
But it cannot get it, because 26727 has a Share lock on it thru the SELECT statement. So it has to wait*****
dev_sporuran=# select locktype,virtualxid,relation::regclass,transactionid,pid,mode,granted
from pg_locks where pid in (26727,26867) and relation::regclass in ('t','t_pkey','t_child') order by pid;
locktype | virtualxid | relation | transactionid | pid | mode | granted
----------+------------+----------+---------------+-------+---------------------+---------
relation | | t_pkey | | 26727 | AccessShareLock | t
relation | | t | | 26727 | AccessShareLock | t
relation | | t_child | | 26867 | AccessExclusiveLock | t
relation | | t | | 26867 | AccessExclusiveLock | f
(4 rows)
谢谢 穆拉利
似乎你可以提出一些关于pgsql-hackers的讨论。乍一看，我看不出没有充分的理由不应该ExclusiveLock不AccessExclusiveLock，所以它仍然允许SELECT.但大多数表结构更改都需要 A.E.L。
看看 ALTER TABLE 锁定强度降低补丁，了解这种事情有多复杂。