ALTER TABLE with ADD COLUMN and ADD PRIMARY KEY USING INDEX throwsspurious "column contains null values"

Поиск
Список
Период
Сортировка
От Zhang, Jie
Тема ALTER TABLE with ADD COLUMN and ADD PRIMARY KEY USING INDEX throwsspurious "column contains null values"
Дата
Msg-id 1396E95157071C4EBBA51892C5368521017F2E6E63@G08CNEXMBPEKD02.g08.fujitsu.local
обсуждение исходный текст
Ответы Re: ALTER TABLE with ADD COLUMN and ADD PRIMARY KEY USING INDEX throws spurious "column contains null values"
Re: ALTER TABLE with ADD COLUMN and ADD PRIMARY KEY USING INDEX throws spurious "column contains null values"
Список 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!



Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Ryan Lambert
Дата:
Сообщение: Re: Fix XML handling with DOCTYPE
Следующее
От: "Imai, Yoshikazu"
Дата:
Сообщение: RE: speeding up planning with partitions