Subject: ALTER TABLE with ADD COLUMN and ADD PRIMARY KEY USING INDEX throws spurious "column contains null values"

From: "Zhang, Jie" <[email-masked]>
Date: Mon, 25 Mar 2019 01:31:51 +0000

Hi all,

When I do the following:
postgres=# create table t1 (a int);
postgres=# insert into t1 values(1);
postgres=# create unique index uniq_idx on t1(a);
postgres=# alter table t1 add column b float8 not null default random(), add primary key using index uniq_idx;
ERROR: column "b" contains null values

PostgreSQL throws error "column b contains null values".

#########################################
alter table t1 add column b float8 not null default 0, add primary key using index uniq_idx;

alter table success.
#########################################

The reasons for the error are as follows.

ATController provides top level control over the phases.
Phase 1: preliminary examination of commands, create work queue 
Phase 2: update system catalogs 
Phase 3: scan/rewrite tables as needed 

In Phase 2, when dealing with "add column b float8 not null default random()", the table is marked rewrite.
When dealing with "add primary key using index uniq_idx", ATExecAddIndexConstraint calls index_check_primary_key.

The calling order is as follows.
index_check_primary_key()
    ↓
AlterTableInternal()
    ↓
ATController()
    ↓
ATRewriteTables()
    ↓
ATRewriteTable()

ATRewriteTable check all not-null constraints. Column a and column b need to check NOT NULL.
Unfortunately, at this time, Phase 3 hasn't been done yet.
The table is not rewrited, just marked rewrite. So, throws error "column b contains null values".

In Phase 2, if table is marked rewrite, we can do not check whether columns are NOT NULL.
Because phase 3 will do it.

Here's a patch to fix this bug.

Best Regards!

---

From: Sergei Kornilov <[email-masked]>
Date: Mon, 25 Mar 2019 15:45:21 +0300

Hi

I did not review patch, just want add link to related bug 15580 and one another -hackers thread:

https://www.postgresql.org/message-id/flat/15580-d1a6de5a3d65da51%40postgresql.org
https://www.postgresql.org/message-id/flat/CAOVr7%2B3C9u_ZApjxpccrorvt0aw%3Dk8Ct5FhHRVZA-YO36V3%3Drg%40mail.gmail.com

regards, Sergei