From: Tom Lane <tgl@sss.pgh.pa.us>
To: ahodgson@simkin.ca
Cc: pgsql-bugs@lists.postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: BUG #17817: DISABLE TRIGGER ALL on a partitioned table with foreign key fails
Date: Wed, 01 Mar 2023 15:51:58 -0500
Message-ID: <1554133.1677703918@sss.pgh.pa.us> (raw)
In-Reply-To: <17817-31dfb7c2100d9f3d@postgresql.org>
References: <17817-31dfb7c2100d9f3d@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> This works on 14.7. It fails on 15.2. The Ruby on Rails test suites use
> DISABLE TRIGGER ALL extensively.
> BEGIN;
> CREATE TABLE test_fk (id serial primary key);
> CREATE TABLE test_table (test serial, created_at timestamp not null, fk_id
> int not null references test_fk(id)) PARTITION BY RANGE (created_at);
> CREATE TABLE test_table_2017 PARTITION OF test_table FOR VALUES FROM
> ('2017-01-01') TO ('2018-01-01');
> ALTER TABLE test_table DISABLE TRIGGER ALL;
> ROLLBACK;
> ERROR: trigger "RI_ConstraintTrigger_c_46838897" for table
> "test_table_2017" does not exist
Yeah, duplicated here. Bisecting says it broke at
commit ec0925c22a3da7199650c9903a03a0017705ed5c
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Thu Aug 4 20:02:02 2022 +0200
Fix ENABLE/DISABLE TRIGGER to handle recursion correctly
Using ATSimpleRecursion() in ATPrepCmd() to do so as bbb927b4db9b did is
not correct, because ATPrepCmd() can't distinguish between triggers that
may be cloned and those that may not, so would wrongly try to recurse
for the latter category of triggers.
So this commit restores the code in EnableDisableTrigger() that
86f575948c77 had added to do the recursion, which would do it only for
triggers that may be cloned, that is, row-level triggers. This also
changes tablecmds.c such that ATExecCmd() is able to pass the value of
ONLY flag down to EnableDisableTrigger() using its new 'recurse'
parameter.
Interestingly, although that commit was back-patched to v11, the failure
does not occur in pre-v15 branches. So what's different about v15?
One clue is that the contents of pg_trigger are quite a bit different:
# select oid, tgparentid, tgrelid::regclass, tgname from pg_trigger where tgrelid in ('test_fk'::regclass, 'test_table'::regclass, 'test_table_2017'::regclass);
oid | tgparentid | tgrelid | tgname
-------+------------+-----------------+------------------------------
40997 | 0 | test_fk | RI_ConstraintTrigger_a_40997
40998 | 0 | test_fk | RI_ConstraintTrigger_a_40998
40999 | 0 | test_table | RI_ConstraintTrigger_c_40999
41000 | 0 | test_table | RI_ConstraintTrigger_c_41000
41006 | 40999 | test_table_2017 | RI_ConstraintTrigger_c_41006
41007 | 41000 | test_table_2017 | RI_ConstraintTrigger_c_41007
(6 rows)
# ALTER TABLE test_table DISABLE TRIGGER ALL;
ERROR: trigger "RI_ConstraintTrigger_c_40999" for table "test_table_2017" does not exist
whereas in v14 I see
# select oid, tgparentid, tgrelid::regclass, tgname from pg_trigger where tgrelid in ('test_fk'::regclass, 'test_table'::regclass, 'test_table_2017'::regclass);
oid | tgparentid | tgrelid | tgname
-------+------------+-----------------+------------------------------
38169 | 0 | test_fk | RI_ConstraintTrigger_a_38169
38170 | 0 | test_fk | RI_ConstraintTrigger_a_38170
38176 | 0 | test_table_2017 | RI_ConstraintTrigger_c_38176
38177 | 0 | test_table_2017 | RI_ConstraintTrigger_c_38177
(4 rows)
It's a reasonable bet that we're trying to look up the child trigger
using the name of its parent trigger ... but why are we searching by
name at all, rather than OID? Seems mighty failure-prone.
Stack trace from the lookup failure is
#0 errfinish (filename=0xac4aeb "trigger.c", lineno=1838,
funcname=0xad8fe0 <__func__.30665> "EnableDisableTrigger") at elog.c:480
#1 0x00000000004c968f in EnableDisableTrigger (rel=<optimized out>,
tgname=0x7f10283ad88c "RI_ConstraintTrigger_c_40999",
fires_when=<optimized out>, skip_system=false, recurse=true, lockmode=6)
at trigger.c:1835
#2 0x000000000069a717 in EnableDisableTrigger (rel=rel@entry=0x7f1031892768,
tgname=tgname@entry=0x0, fires_when=fires_when@entry=68 'D',
skip_system=false, recurse=true, lockmode=6) at trigger.c:1819
#3 0x0000000000691db4 in ATExecEnableDisableTrigger (
lockmode=<optimized out>, recurse=<optimized out>,
skip_system=<optimized out>, fires_when=<optimized out>,
trigname=<optimized out>, rel=<optimized out>) at tablecmds.c:14729
#4 ATExecCmd (wqueue=0x7fffb8534408, tab=0x17f4788, cmd=<optimized out>,
lockmode=6, cur_pass=<optimized out>, context=0x7fffb85345a0)
at tablecmds.c:5165
#5 0x0000000000693108 in ATRewriteCatalogs (context=0x7fffb85345a0,
lockmode=6, wqueue=0x7fffb8534408)
at ../../../src/include/nodes/nodes.h:193
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, ahodgson@simkin.ca, alvherre@alvh.no-ip.org
Subject: Re: BUG #17817: DISABLE TRIGGER ALL on a partitioned table with foreign key fails
In-Reply-To: <1554133.1677703918@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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