pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17882: I can't disable triggers on a table that has been partitioned
2+ messages / 2 participants
[nested] [flat]
* BUG #17882: I can't disable triggers on a table that has been partitioned
@ 2023-04-02 15:28 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2023-04-02 15:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: quynh.helendinh@gmail.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17882: I can't disable triggers on a table that has been partitioned
@ 2023-04-02 16:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-04-02 16:20 UTC (permalink / raw)
To: quynh.helendinh@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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
Hi, this was fixed about a month ago (commit f61e60102). That will
appear in 15.3, due out in May.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-04-02 16:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-02 15:28 BUG #17882: I can't disable triggers on a table that has been partitioned PG Bug reporting form <noreply@postgresql.org>
2023-04-02 16:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox