Alter column set not null fails

Question

Consider the following table with approximately 10M rows

CREATE TABLE user (   id bigint NOT NULL,   ...   CONSTRAINT user_pk PRIMARY KEY (id) ) WITH (   OIDS=FALSE ) 

Then i applied the following alter

ALTER TABLE USER ADD COLUMN BUSINESS_ID    VARCHAR2(50); --OK UPDATE USER SET BUSINESS_ID = ID; //~1500 sec --OK ALTER TABLE USER ALTER COLUMN BUSINESS_ID SET NOT NULL;      ERROR: column "business_id" contains null values     SQL state: 23502 

This is very strange since id column (which has been copied to business_id column) can't contain null values since it is the primary key, but to be sure i check it

select count(*) from USER where BUSINESS_ID is null     --0 records 

I suspect that this is a bug, just wondering if i am missing something trivial

Answer 1

The only logical explanation would be a concurrent INSERT.
(Using tbl instead of the reserved word user as table name.)

ALTER TABLE tbl ADD COLUMN BUSINESS_ID    VARCHAR2(50); --OK UPDATE tbl SET BUSINESS_ID = ID; //~1500 sec --OK  -- concurrent INSERT HERE !!!  ALTER TABLE tbl ALTER COLUMN BUSINESS_ID SET NOT NULL;</code></pre> 

To prevent this, use instead:

ALTER TABLE tbl   ADD COLUMN BUSINESS_ID VARCHAR(50) DEFAULT '';  -- or whatever is appropriate ... 

You may end up with a default value in some rows. You might want to check.

Or run everything as transaction block:

BEGIN; -- LOCK tbl; -- not needed ALTER ... UPDATE ... ALTER ... COMMIT; 

You might take an exclusive lock to be sure, but ALTER TABLE .. ADD COLUMN takes an ACCESS EXCLUSIVE lock anyway. (Which is only released at the end of the transaction, like all locks.)