Thread: deferrable FK constraints on partitioned rels
While messing around, I noticed that SET CONSTRAINTS ... DEFERRED does not work with partitioned tables. I had some code to cover this case, but it has a bug that prevents it from working at all: the sanity check that verifies whether triggers exist fails. The attached patch fixes this problem: it merely removes the sanity check. With that, everything works. (Another approach I tried was to split out constraints in partitioned tables vs. constraints in regular ones. That's indeed workable, but it requires us to do two additional syscache access per partition for get_rel_relkind, which seems excessive.) The UNIQUE DEFERRABLE case works after the patch. (I didn't try without the patch.) -- Álvaro Herrera Developer, https://www.PostgreSQL.org/
Attachment
On 2019-Nov-05, Alvaro Herrera wrote: > While messing around, I noticed that SET CONSTRAINTS ... DEFERRED > does not work with partitioned tables. I had some code to cover this > case, but it has a bug that prevents it from working at all: the sanity > check that verifies whether triggers exist fails. > > The attached patch fixes this problem: it merely removes the sanity > check. With that, everything works. > > (Another approach I tried was to split out constraints in partitioned > tables vs. constraints in regular ones. That's indeed workable, but it > requires us to do two additional syscache access per partition for > get_rel_relkind, which seems excessive.) Uh, somehow I posted a previous version of the patch that implements my rejected approach, instead of the final version I described. Here's the real patch (which also includes tests). -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
On 2019-Nov-05, Alvaro Herrera wrote: > Uh, somehow I posted a previous version of the patch that implements my > rejected approach, instead of the final version I described. Here's the > real patch (which also includes tests). This was broken in pg11 also. Pushed to all branches. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services