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

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17882: I can't disable triggers on a table that has been partitioned
Дата
Msg-id 17882-707c9c1553485c97@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17882: I can't disable triggers on a table that has been partitioned  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      17882
Logged by:          Quynh Le
Email address:      quynh.helendinh@gmail.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.


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Alexander Lakhin
Дата:
Сообщение: Re: BUG #17798: Incorrect memory access occurs when using BEFORE ROW UPDATE trigger
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #17882: I can't disable triggers on a table that has been partitioned