Better handle indirect constraint drops It is possible for certain cases to remove not-null constraints without maintaining the attnotnull in its correct state; for example if you drop a column that's part of the primary key, and the other columns of the PK don't have not-null constraints, then we should reset the attnotnull flags for those other columns; up to this commit, we didn't. Handle those cases better by doing the attnotnull reset in RemoveConstraintById() instead of in dropconstraint_internal().
However, there are some cases where we must not do so. For example if those other columns are in replica identity indexes or are generated identity columns, we must keep attnotnull set, even though it results in the catalog inconsistency that no not-null constraint supports that. Because the attnotnull reset now happens in more places than before, for instance when a column of the primary key changes type, we need an additional trick to reinstate it as necessary. Introduce a new alter-table pass that does this, which needs simply reschedule some AT_SetAttNotNull subcommands that were already being generated and ignored. Because of the exceptions in which attnotnull is not reset noted above, we also include a pg_dump hack to include a not-null constraint when the attnotnull flag is set even if no pg_constraint row exists. This part is undesirable but necessary, because failing to handle the case can result in unrestorable dumps. Reported-by: Tender Wang <tndrw...@gmail.com> Co-authored-by: Tender Wang <tndrw...@gmail.com> Reviewed-by: jian he <jian.universal...@gmail.com> Discussion: https://postgr.es/m/CAHewXN=hMbNa3d43NOR=ocgdgptt18s-1fmuecoegesyek4...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/0cd711271d42b0888d36f8eda50e1092c2fed4b3 Modified Files -------------- src/backend/catalog/pg_constraint.c | 117 +++++++++++++++++++++++++- src/backend/commands/tablecmds.c | 135 +++++++++++++++--------------- src/bin/pg_dump/pg_dump.c | 30 +++++-- src/test/regress/expected/constraints.out | 78 +++++++++++++++++ src/test/regress/sql/constraints.sql | 39 +++++++++ 5 files changed, 323 insertions(+), 76 deletions(-)
