Добавление новой колонки NOT NULL

[sgrinko]

добавил в таблицу новую колонку:
job_number smallint DEFAULT 0 NOT NULL
сгенерировался код:
ALTER TABLE checksync_tables
ADD COLUMN job_number smallint;

ALTER TABLE ONLY checksync_tables
ALTER COLUMN job_number SET DEFAULT 0;

ALTER TABLE ONLY checksync_tables
ALTER COLUMN job_number SET NOT NULL;
—
к сожалению этот код просто так выполнить невозможно.
1-я строка добавит NULL поле
2-я строка объявит что для insert/update надо заполнить поле значением null
3-я строка попытается сделать поле NOT NULL но конечно у неё это не получится, так как в таблице все строки на данный момент есть NULL

для данной операции ожидалась команда
ALTER TABLE checksync_tables
ADD COLUMN job_number smallint NOT NULL DEFAULT 0;
—
ваш вариант генерации кода я использую в своих PRE скриптах, когда нужно в цикле мелкими пакетами обработать большую таблицу. Но это ручная работа и такой скрипт пишется специально и вручную. В этом случаем между 2-й и 3-й строкой стоит код замены всех строк с NULL на DEFAULT значение.

[asaushkin]

Скрипт для воспроизведения:

create table checksync_tables as
	select generate_series(1, 5) as id;

ALTER TABLE checksync_tables
  ADD COLUMN job_number smallint;

ALTER TABLE ONLY checksync_tables
  ALTER COLUMN job_number SET DEFAULT 0;

ALTER TABLE ONLY checksync_tables
  ALTER COLUMN job_number SET NOT NULL;

[asaushkin]

Я помню, мы этот кейс обсуждали в своё время и пришли к выводу, что текущее поведение (т.е. когда дефолтные значения выставляются только для новых записей) наиболее приемлемо для существующих данных.

Нужно подумать об опции для изменения поведения.

[sgrinko]

Разъединение такой команды на части хорошо в случае когда мы хотим добавить поле с DEFAULT значением но не NOT NULL.
То есть у нас есть большая таблица и мы добавляем поле
job_number smallint DEFAULT 0 NULL
то, если сделать 1 командой
ALTER TABLE checksync_tables ADD COLUMN job_number smallint NULL DEFAULT 0;
то ВСЕ строки заполнятся значением 0, что не желательно скорее всего (в этой точке как минимум будем долго ждать выполнения команды).
таким образом разъединив это на 2 команды мы получим:
ALTER TABLE checksync_tables
ADD COLUMN job_number smallint;

ALTER TABLE ONLY checksync_tables
ALTER COLUMN job_number SET DEFAULT 0;
быстрое выполнение, а уже потом сможем сами запустить свой POST скрипт заполнения DEFAULT значения пакетами.
Итог:
NULL поле - 2 команды + возможно POST скрипт
NOT NULL - нужно генерить 1 команду и скорее всего потребуется PRE скрипт.
такие скрипты конечно будет писать DBA вручную, хотя конечно шаблончик в папку миграции можно и положить :)

[Endeavourl]

Да, именно кейс без NOT NULL рассматривался, когда мы меняли это поведение (задача 3755 во внутреннем трекере).

Для NOT NULL видится опциональное добавление команды наподобие:

UPDATE t_table SET c_column = DEFAULT WHERE c_column IS NULL;

перед выставлением NOT NULL.

[asaushkin]

@Endeavourl А почему определение дефолта не перенести в стейтмент создания колонки?

[sgrinko]

Согласен с @Endeavourl
а DBA уже заменит эту строку на свой цикл при необходимости.
@asaushkin, DEFAULT в стейтмент создания колонки сразу заполнит её, а это долго и такой код нужно как-то выискивать и заменять

[asaushkin]

Ок, можно и так.

[Endeavourl]

@asaushkin
Апдейтом покроется выставление NOT NULL как при создании, так и при изменении колонки, и вообще так проще.
Единственно, апдейты надо будет добавить в опасные выражения.

[Endeavourl]

исправлено в a4675b3

[sgrinko]

Спасибо!
В 11 версии появилась поддержка быстрого добавления DEFAULT значений...
По возможности учтите эту возможность если идет построение скрипта для 11-й и выше версии.