[YSQL] Backfill existing rows when new column is added with default value

[d-uspenskiy]

Jira Link: DB-1463

yugabyte=# create table t(k int PRIMARY KEY);
yugabyte=# insert into t values(1), (2), (3);
yugabyte=# alter table t add v int not null default 5;
yugabyte=# select * from t;
 k | v 
---+---
 1 |  
 2 |  
 3 |  
(3 rows)

expected (vanilla postgresql) result:

postgres=# select * from t;
 k | v 
---+---
 1 | 5
 2 | 5
 3 | 5
(3 rows)

[ndeodhar]

This is expected behavior for now @d-uspenskiy and is being tracked under the umbrella of online schema changes: #4192

[jaki]

With D10666 (relaxing conflicts on ALTER), I found a bug with default not null:

session A                     | session B
CREATE TABLE t (i int);
\c
BEGIN;
SELECT 1;
                              | ALTER TABLE t ADD j int default 44 NOT NULL;
INSERT INTO t VALUES (7);
COMMIT;
SELECT * FROM t;

i | j 
---+---
 7 |  
(1 row)

A possible explanation is that this bug was previously masked by catalog version mismatch. An other explanation is that our transaction model is broken (insert after alter should fail).

[lukaseder]

Another way to look at this bug: #9970

[lukaseder]

For the record, SQL Server and Sybase ASE don't fill default values with this statement:

alter table t add v int default 5;

But they do with this one:

alter table t add v int not null default 5;

[lukaseder]

A workaround for this is:

alter table t add v int not null default 5;
update t set v = default;