Apply ALTER ... SET NOT NULL recursively in ALTER ... ADD PRIMARY KEY.

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 4 August 2017 11:45 EDT

If you do ALTER COLUMN SET NOT NULL against an inheritance parent table, it will recurse to mark all the child columns as NOT NULL as well. This is necessary for consistency: if the column is labeled NOT NULL then reading it should never produce nulls.

However, that didn't happen in the case where ALTER ... ADD PRIMARY KEY marks a target column NOT NULL that wasn't before. That was questionable from the beginning, and now Tushar Ahuja points out that it can lead to dump/restore failures in some cases. So let's make that case recurse too.

Although this is meant to fix a bug, it's enough of a behavioral change that I'm pretty hesitant to back-patch, especially in view of the lack of similar field complaints. It doesn't seem to be too late to put it into v10 though.

Michael Paquier, editorialized on slightly by me

Discussion: https://postgr.es/m/b8794d6a-38f0-9d7c-ad4b-e85adf860fc9@enterprisedb.com

c30f177 Apply ALTER ... SET NOT NULL recursively in ALTER ... ADD PRIMARY KEY.
src/backend/catalog/index.c | 9 ++++-----
src/test/regress/expected/alter_table.out | 2 +-
2 files changed, 5 insertions(+), 6 deletions(-)

Upstream: git.postgresql.org


  • Share