From: | "Zhang, Jie" <zhangjie2(at)cn(dot)fujitsu(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Cc: | "Zhang, Jie" <zhangjie2(at)cn(dot)fujitsu(dot)com> |
Subject: | ALTER TABLE with ADD COLUMN and ADD PRIMARY KEY USING INDEX throws spurious "column contains null values" |
Date: | 2019-03-25 01:31:51 |
Message-ID: | 1396E95157071C4EBBA51892C5368521017F2E6E63@G08CNEXMBPEKD02.g08.fujitsu.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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!
Attachment | Content-Type | Size |
---|---|---|
alter_table.patch | application/octet-stream | 15.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Imai, Yoshikazu | 2019-03-25 01:47:23 | RE: speeding up planning with partitions |
Previous Message | Ryan Lambert | 2019-03-25 01:04:24 | Re: Fix XML handling with DOCTYPE |