On Fri, Jan 3, 2025 at 12:11 AM Álvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > > > > On Thu, Jan 2, 2025, at 5:49 PM, Amul Sul wrote: > > When adding a new FK constraint or attaching a partitioned table, where > matching FK constraints are merged, we allow the parent constraint to be NOT > VALID while the child constraint remains VALID, which is harmless. However, > the > reverse scenario -- where the parent constraint is VALID and the child is NOT > VALID -- is incorrect. To address this, when merging a NOT VALID FK constraint > from the child with a VALID parent constraint, it implicitly validates the > child constraint against its existing data and marks it as VALID. This > behavior > aligns with adding a new FK constraint directly to the child table, which > would > also validate the existing data. > > > Hmm, I'm not sure about this, which may cause surprising delays. Maybe it > would be better that the operation fails with an error, so that the user can > do VALIDATE CONSTRAINT explicitly and retry the ATTACH once all the > partitions have been so processed.
Error reporting would have made this straightforward, but the delay is not a new, and the patch does not introduce any additional delay. Setting the patch aside for a moment, consider the current behavior on the master branch: if you have a partitioned table(see e.g. below) where one of the child tables has a NOT VALID foreign key constraint, adding a new VALID foreign key constraint to the partitioned table will ignore the existing constraint on the child table. Instead, it creates a new constraint, which ultimately triggers a scan of the child table to validate the new constraint. E.g. create table bar(i int primary key); create table foo(i int) partition by range(i); create table foo_p1 partition of foo for values from (0) to (10); insert into foo_p1 values(1); -- value doesn't exists in bar alter table foo_p1 add constraint fk_con foreign key(i) references bar NOT VALID; -- ok -- following triggers the validation and fails. alter table foo add constraint fk_con foreign key(i) references bar; The behavior with the patch remains the same, but instead of creating a new foreign key constraint, it merges with the existing one and validates it. Regards, Amul
