ALTER TABLE with multiple SET NOT NULL

[Allison Kaptur <allison.kaptur@xxxxxxxxx>]

Hi folks,

I encountered a surprising error when writing a migration that both added a primary key to a table and added a new NOT NULL column. It threw the error "column "col_d" contains null values", even though I supplied a default. The migration looks like this:

CREATE TABLE new_table AS SELECT col_a, col_b, col_c from existing_table;

ALTER TABLE new_table

    ADD COLUMN col_d UUID UNIQUE NOT NULL DEFAULT uuid_generate_v4(),

    ADD PRIMARY KEY (col_a, col_b, col_c);

Because of the `DEFAULT uuid_generate_v4()`, I wouldn't expect it to be possible for the new column to have null values, so I was surprised to get an integrity error with the message "column "col_d" contains null values".

I found two workarounds that don't produce the error. First, if I instead set the NOT NULL last, I get no error:

ALTER TABLE new_table

    ADD COLUMN col_d UUID UNIQUE DEFAULT uuid_generate_v4(),

    ADD PRIMARY KEY (col_a, col_b, col_c),

    ALTER COLUMN col_d SET NOT NULL;

Second, if I do the two steps in two ALTER TABLE statements, I also get no error.

ALTER TABLE new_table

    ADD COLUMN col_d UUID UNIQUE NOT NULL DEFAULT uuid_generate_v4();

ALTER TABLE new_table

    ADD PRIMARY KEY (col_a, col_b, col_c);

I'm running postgres 9.6.2.

I know that adding a column with a default requires the table & its indexes to be rewritten, and I know that adding a primary key on a column that doesn't have an existing NOT NULL constraint does ALTER COLUMN SET NOT NULL on each column in the primary key. So I'm wondering if Postgres is reordering the SET NOT NULL operations in a way that causes it to attempt setting col_d to NOT NULL before the default values are supplied.

My understanding from the docs is that I should be able to combine any ALTER TABLE statements into one if they don't involve RENAME or SET SCHEMA (or a few other things in v10, which I'm not using).

So my questions are:

- Is there a way I can see what Postgres is doing under the hood? I wanted to use EXPLAIN ANALYZE but it doesn't appear to work on alter table statements.

- Am I missing something about my original migration, or is there a reason I shouldn't expect it to work?

Thanks,

Allison Kaptur