Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
От | Junwang Zhao |
---|---|
Тема | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
Дата | |
Msg-id | CAEG8a3JDPztnBiTPmkhO2B2_MsvO1mi7goM70EJOk0AnpbTUVw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails (Junwang Zhao <zhjwpku@gmail.com>) |
Ответы |
Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails |
Список | pgsql-hackers |
On Fri, Jul 26, 2024 at 2:36 PM Junwang Zhao <zhjwpku@gmail.com> wrote: > > On Mon, Jul 22, 2024 at 1:52 PM Tender Wang <tndrwang@gmail.com> wrote: > > > > > > > > Alvaro Herrera <alvherre@alvh.no-ip.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, connoinheritfrom 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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Junwang ZhaoДата:
Сообщение: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails