From: | Junwang Zhao <zhjwpku(at)gmail(dot)com> |
---|---|
To: | Tender Wang <tndrwang(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Alexander Lakhin <exclusion(at)gmail(dot)com> |
Subject: | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
Date: | 2024-07-26 06:56:51 |
Message-ID: | CAEG8a3JDPztnBiTPmkhO2B2_MsvO1mi7goM70EJOk0AnpbTUVw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, Jul 26, 2024 at 2:36 PM Junwang Zhao <zhjwpku(at)gmail(dot)com> wrote:
>
> On Mon, Jul 22, 2024 at 1:52 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> >
> >
> >
> > Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2024年7月19日周五 21:18写道:
> >>
> >> Hello,
> >>
> >> I think the fix for the check triggers should be as the attached. Very
> >> close to what you did, but you were skipping some operations that needed
> >> to be kept. AFAICS this patch works correctly for the posted cases.
> >
> >
> > After applying the attached, the r_1_p_id_fkey1 will have redundant action
> > triggers, as below:
> > postgres=# select oid, conname, contype, conrelid, conindid,conparentid, confrelid,conislocal,coninhcount, connoinherit from pg_constraint where oid = 16402;
> > oid | conname | contype | conrelid | conindid | conparentid | confrelid | conislocal | coninhcount | connoinherit
> > -------+----------------+---------+----------+----------+-------------+-----------+------------+-------------+--------------
> > 16402 | r_1_p_id_fkey1 | f | 16394 | 16392 | 0 | 16389 | t | 0 | f
> > (1 row)
> >
> > postgres=# select oid, tgrelid, tgparentid, tgconstrrelid, tgconstrindid, tgconstraint from pg_trigger where tgconstraint = 16402;
> > oid | tgrelid | tgparentid | tgconstrrelid | tgconstrindid | tgconstraint
> > -------+---------+------------+---------------+---------------+--------------
> > 16403 | 16389 | 16400 | 16394 | 16392 | 16402
> > 16404 | 16389 | 16401 | 16394 | 16392 | 16402
> > 16422 | 16389 | 0 | 16394 | 16392 | 16402
> > 16423 | 16389 | 0 | 16394 | 16392 | 16402
> > (4 rows)
> >
>
> Yes, seems Alvaro has mentioned that he hasn't looked at the
> action triggers, in the attached patch, I add some logic that
> first check if there exists action triggers, if yes, just update
> their Parent Trigger to InvalidOid.
>
> >
> > --
> > Tender Wang
>
>
>
> --
> Regards
> Junwang Zhao
There is a bug report[0] Tender comments might be the same
issue as this one, but I tried Alvaro's and mine patch, neither
could solve that problem, I did not tried Tender's earlier patch
thought. I post the test script below in case you are interested.
CREATE TABLE t1 (a int, PRIMARY KEY (a));
CREATE TABLE t (a int, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t1)
PARTITION BY LIST (a);
ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
ALTER TABLE t DETACH PARTITION t1;
ALTER TABLE t ATTACH PARTITION t1 FOR VALUES IN (1);
[0] https://www.postgresql.org/message-id/18541-628a61bc267cd2d3@postgresql.org
--
Regards
Junwang Zhao
From | Date | Subject | |
---|---|---|---|
Next Message | Yugo Nagata | 2024-07-26 07:07:14 | EphemeralNamedRelation and materialized view |
Previous Message | Junwang Zhao | 2024-07-26 06:36:16 | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |