BUG #17882: I can't disable triggers on a table that has been partitioned

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: quynh(dot)helendinh(at)gmail(dot)com
Subject: BUG #17882: I can't disable triggers on a table that has been partitioned
Date: 2023-04-02 15:28:37
Message-ID: 17882-707c9c1553485c97@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17882
Logged by: Quynh Le
Email address: quynh(dot)helendinh(at)gmail(dot)com
PostgreSQL version: 15.2
Operating system: MacOS
Description:

Postgres version: PostgreSQL 15.2 on aarch64-apple-darwin21.6.0, compiled by
Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit

Given that I have a table users that has a referential constraint to another
table, activities, like below:

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);
Below are the results of \dS+ commands on the table users and its
partitions.
--------------------------------------------------------------------------------------------------
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)
--------------------------------------------------------------------------------------------------
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)
--------------------------------------------------------------------------------------------------

When I try to disable triggers on the table users using this command:
alter table users disable trigger all;

I'm met with this error:
ERROR: trigger "RI_ConstraintTrigger_c_572918" for table "users_0" does not
exist

My hypothesis: I guess the command to disable all triggers will propagate to
all the partitions as this command to disable triggers on only this table
would work:
alter table only users disable trigger all; -- ALTER TABLE

I tried to list all triggers for the original table and partitions and they
return different trigger names. PostgreSQL seems to use the triggers' name
from the original table and propagates it to the partitions, but with naming
differences like this, it would never work right?

Below is the result when I list the triggers on the original table users and
its partitions.

-- original table
test=# SELECT
tgname AS trigger_name
FROM
pg_trigger
WHERE
tgrelid = 'users'::regclass
ORDER BY
trigger_name;
trigger_name;

--- -------------------------------
RI_ConstraintTrigger_c_572918
RI_ConstraintTrigger_c_572919
-- partition
test=# SELECT
tgname AS trigger_name
FROM
pg_trigger
WHERE
tgrelid = 'users_0'::regclass
ORDER BY
trigger_name;
trigger_name
-------------------------------
RI_ConstraintTrigger_c_572924
RI_ConstraintTrigger_c_572925
(2 rows)
My question: However, I'm unused as to why PostgreSQL is trying to disable a
non-existent trigger on the partition. Is this a bug? I have tested on 14.4
and 14.7 and Postgres doesn't create triggers on the original table, but in
15.5 it does.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2023-04-02 16:20:26 Re: BUG #17882: I can't disable triggers on a table that has been partitioned
Previous Message Alexander Lakhin 2023-04-01 12:00:00 Re: BUG #17798: Incorrect memory access occurs when using BEFORE ROW UPDATE trigger