pgsql: Fix ADD PRIMARY KEY after dropping and re-adding a column

Поиск
Список
Период
Сортировка
Искать

pgsql: Fix ADD PRIMARY KEY after dropping and re-adding a column

От:
Fujii Masao <fujii@postgresql.org>
Дата:
Fix ADD PRIMARY KEY after dropping and re-adding a column

Previously, dropping a NOT NULL column, adding a new column with the same
name, and making it a primary key in a single ALTER TABLE command could
fail with "primary key column ... is not marked NOT NULL". This happened
because ALTER TABLE checked for an existing NOT NULL constraint before
dropping the old column. Finding the constraint caused it to skip adding
one for the new column, even though the old constraint would be removed
with the old column.

Defer this check until after column drops and additions have been
executed, so that it examines the columns that will actually form the
primary key. Add any required NOT NULL constraints before creating the
primary-key index.

Backpatch to v18, where this issue was introduced by commit a379061a22a.

Reported-by: Ludvig Janiuk 
Author: Fujii Masao 
Co-authored-by: Manuel Reyes Bravo 
Discussion: https://postgr.es/m/0a9b4c44-cdff-4ec8-a8fb-b3a8b59e9ba9@proton.me
Backpatch-through: 18

Branch
------
REL_18_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/5659e549adeb9de1a89efa181b032044ada64b1f

Modified Files
--------------
src/backend/commands/tablecmds.c          | 35 +++++++++++++++++++++----------
src/test/regress/expected/alter_table.out | 25 ++++++++++++++++++++++
src/test/regress/sql/alter_table.sql      | 26 +++++++++++++++++++++++
3 files changed, 75 insertions(+), 11 deletions(-)

FAQ