MIME-Version: 1.0
References: 
 <CAHewXNmF-dn+hMkcsWM=b_f7Zc4nwP7EN0uXK0NG_VNEk-q1uA@mail.gmail.com>
 <202407191318.uecnw7oaidhu@alvherre.pgsql>
 <CAHewXNnuCNiy5VBOVWPTXwxqHRMXKZ-Z_6hT9koQXAS9PBdVRQ@mail.gmail.com>
 <CAEG8a3KsXSW+nSBFEhc2_F_FYCPu5eDDcy_1CFrtP6oQOVeqEQ@mail.gmail.com>
In-Reply-To: 
 <CAEG8a3KsXSW+nSBFEhc2_F_FYCPu5eDDcy_1CFrtP6oQOVeqEQ@mail.gmail.com>
From: Junwang Zhao <zhjwpku@gmail.com>
Date: Fri, 26 Jul 2024 14:56:51 +0800
Message-ID: 
 <CAEG8a3JDPztnBiTPmkhO2B2_MsvO1mi7goM70EJOk0AnpbTUVw@mail.gmail.com>
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
To: Tender Wang <tndrwang@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
 Alexander Lakhin <exclusion@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAEG8a3JDPztnBiTPmkhO2B2_MsvO1mi7goM70EJOk0AnpbTUVw%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 26, 2024 at 2:36=E2=80=AFPM Junwang Zhao <zhjwpku@gmail.com> wr=
ote:
>
> On Mon, Jul 22, 2024 at 1:52=E2=80=AFPM Tender Wang <tndrwang@gmail.com> =
wrote:
> >
> >
> >
> > Alvaro Herrera <alvherre@alvh.no-ip.org> =E4=BA=8E2024=E5=B9=B47=E6=9C=
=8819=E6=97=A5=E5=91=A8=E4=BA=94 21:18=E5=86=99=E9=81=93=EF=BC=9A
> >>
> >> Hello,
> >>
> >> I think the fix for the check triggers should be as the attached.  Ver=
y
> >> close to what you did, but you were skipping some operations that need=
ed
> >> 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 act=
ion
> > triggers, as below:
> > postgres=3D# select oid, conname, contype, conrelid, conindid,conparent=
id, confrelid,conislocal,coninhcount, connoinherit from pg_constraint where=
 oid =3D 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=3D# select oid, tgrelid, tgparentid, tgconstrrelid, tgconstrin=
did, tgconstraint from pg_trigger where tgconstraint =3D 16402;
> >   oid  | tgrelid | tgparentid | tgconstrrelid | tgconstrindid | tgconst=
raint
> > -------+---------+------------+---------------+---------------+--------=
------
> >  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

--=20
Regards
Junwang Zhao