Hi,
I think I may have run into a small gap left by f80bedd52b1 ("Allow ALTER
COLUMN SET EXPRESSION on virtual columns with CHECK constraints"), and
wanted to share a patch and hear what others think.After that commit, ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION rebuilds CHECK constraints that depend on the generated column. The rebuild list is populated by RememberAllDependentForRebuilding(), which scans dependencies on the column's attnum. That works well for ordinary column references, but CHECK constraints that reference the table through a whole-row Var (for example CHECK (tab IS NOT NULL)) do not record a per-column dependency on the generated column, so they don't seem to be picked up. As a result, SET EXPRESSION can change the generation expression in a way that makes existing rows no longer satisfy the whole-row CHECK constraint, and the ALTER TABLE still succeeds. The attached patch handles this by remembering all CHECK constraints on the relation during SET EXPRESSION, in addition to the objects found by the per-column dependency scan. It's fairly conservative, but it keeps the logic simple, and RememberConstraintForRebuilding() already de-duplicates constraints found through both paths. A more targeted alternative would be to inspect each CHECK expression and only add constraints that contain a whole-row Var. I went with the simpler approach here, but I'd be happy to switch to the narrower scan if that seems preferable. The patch adds regression coverage for both stored and virtual generated columns. - on unpatched master, SET EXPRESSION succeeds and leaves a row for which the whole-row CHECK expression evaluates to false - with the patch, the same SET EXPRESSION command is rejected with: ERROR: check constraint "whole_row_check" of relation "..." is violated by some row Thoughts? Regards, Ayush
v1-0001-Rebuild-CHECK-constraints-for-SET-EXPRESSION.patch
Description: Binary data
