PostgreSQL 15.2：为何无法禁用分区表users的所有触发器？

环境信息 #

• PostgreSQL版本：PostgreSQL 15.2 on aarch64-apple-darwin21.6.0，由Apple clang version 14.0.0 (clang-1400.0.29.102)编译，64位。

建表语句 #

CREATE TABLE activities (
  id INT PRIMARY KEY,
  activity_name VARCHAR(50)
);

CREATE TABLE users (
  id INT,
  username VARCHAR(50),
  activity_id INT,
  FOREIGN KEY (activity_id) REFERENCES activities(id)
) PARTITION BY RANGE (id);

CREATE TABLE users_0 PARTITION OF users
  FOR VALUES FROM (0) TO (100);

CREATE TABLE users_1 PARTITION OF users
  FOR VALUES FROM (100) TO (200);

CREATE TABLE users_2 PARTITION OF users
  FOR VALUES FROM (200) TO (300);

CREATE TABLE users_3 PARTITION OF users
  FOR VALUES FROM (300) TO (MAXVALUE);

表结构查询结果 #

主表users结构 #

Partitioned table "public.users"
   Column    |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id          | integer               |           |          |         | plain    |             |              |
 username    | character varying(50) |           |          |         | extended |             |              |
 activity_id | integer               |           |          |         | plain    |             |              |
Partition key: RANGE (id)
Foreign-key constraints:
    "users_activity_id_fkey" FOREIGN KEY (activity_id) REFERENCES activities(id)
Partitions: users_0 FOR VALUES FROM (0) TO (100),
            users_1 FOR VALUES FROM (100) TO (200),
            users_2 FOR VALUES FROM (200) TO (300),
            users_3 FOR VALUES FROM (300) TO (MAXVALUE)

分区users_0结构 #

Table "public.users_0"
   Column    |         Type          | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
 id          | integer               |           |          |
 username    | character varying(50) |           |          |
 activity_id | integer               |           |          |
Partition of: users FOR VALUES FROM (0) TO (100)
Foreign-key constraints:
    TABLE "users" CONSTRAINT "users_activity_id_fkey" FOREIGN KEY (activity_id) REFERENCES activities(id)

问题现象 #

执行命令alter table users disable trigger all;时，收到错误：

ERROR: trigger "RI_ConstraintTrigger_c_572918" for table "users_0" does not exist

而执行alter table only users disable trigger all;可以成功。进一步查询发现，主表的触发器名称为RI_ConstraintTrigger_c_572918、RI_ConstraintTrigger_c_572919，但分区users_0的触发器名称是RI_ConstraintTrigger_c_572924、RI_ConstraintTrigger_c_572925，两者并不一致。

问题分析与结论 #

这并非PostgreSQL的Bug，而是分区表中外键约束的工作机制导致的：

• 分区表的外键约束实际是在每个分区上单独创建对应触发器，主表上的触发器仅用于元数据管理，不会作用于实际数据。
• 执行alter table users disable trigger all;时，PostgreSQL会尝试将操作传播到所有分区，但错误地使用主表的触发器名称去操作分区的触发器，而分区的触发器名称是独立生成的，因此出现找不到触发器的错误。
• alter table only users disable trigger all;仅操作主表，不会传播到分区，所以可以成功，但这不会影响分区上的实际约束触发器。

如果需要禁用所有分区上的外键触发器，可直接针对每个分区执行命令，或者用批量脚本处理：

-- 批量禁用users表所有分区的触发器
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'users_%') LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(rec.tablename) || ' DISABLE TRIGGER ALL;';
    END LOOP;
END $$;

内容的提问来源于stack exchange，提问作者marrrie