On Fri, Jan 31, 2025 at 7:10 PM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > > On 2025-Jan-31, Ashutosh Bapat wrote: > > > But if the constraint is NOT VALID and later marked as NOT ENFORCED, > > what is expected behaviour while changing it to ENFORCED? > > I think what you want is a different mode that would be ENFORCED NOT > VALID, which would be an extension of the standard, because the standard > does not support the concept of NOT VALID. So while I think what you > want is nice, I'm not sure that this patch necessarily must implement > it. >
Here is my understanding behind this feature implementation -- I am not claiming to be 100% correct, I am confident I am not entirely wrong either. Let me explain with an example: imagine a user adds a VALID constraint to a table that already has data, and the user is completely sure that all the data complies with the constraint. Even in this case, the system still runs a validation check. This is expected behavior because the system can't just take the user's word for it -- it needs to explicitly confirm that the data is valid through validation. Now, with a NOT ENFORCED constraint, it's almost like the constraint doesn't exist, because no checks are being performed and there is no visible effect for the user, even though the constraint is technically still there. So when the constraint is switched to ENFORCED, we should be careful not to automatically mark it as validated (regardless of its previous validate status) unless the data is actually checked against the constraint -- treat as adding a new VALID constraint. Even if the user is absolutely sure the data complies, we should still run the validation to ensure reliability. In response to Ashutosh’s point about the VALID/NOT ENFORCED scenario: if a constraint is initially VALID, then marked as NOT ENFORCED, and later switched back to ENFORCED -- IMO, it shouldn't automatically be considered VALID. I had similar thoughts when working on a patch before v5, but after further discussion, I now agree that it makes more sense for the system to keep it as NOT VALID until the data has been validated against the constraint. This is especially important when a user adds the constraint, is confident the data complies, and then needs to enforce it. Validating the data ensures the integrity and consistency of the constraint. In short, even if the user is 100% confident, skipping validation is not an option. We need to make sure the constraint’s VALID status is accurate and reliable before marking it as validated. Regards, Amul
