[Bug]: Wrong result returned after compressing column, with alter table, new column with default value

[dbeck]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Compression

What happened?

I'm creating this bug after Sven Klemm found the issue and Alexander Kuzmenkov provided a further repro.
In the two repros we:

• create a hypertable
• enable compression
• insert a record
• alter the table by adding a new column with a default value
• update the table with a null value / insert a record with a null value
• compress

After these steps we expect the compressed table to return the null value, but instead it returns the default value.

TimescaleDB version affected

2.18.0

PostgreSQL version used

16.6

What operating system did you use?

Ubuntu 22.04 x86

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

How can we reproduce the bug?

--
-- Repro 1: update value
-- Credit: Sven Klemm
--
create table t1 (ts int);
select create_hypertable('t1', 'ts');
alter table t1 set(timescaledb.compress);
insert into t1 (ts) values (1);
alter table t1 add column c1 int default 42;
update t1 set c1 = null;
select compress_chunk(show_chunks('t1'));
select * from t1;
 ts | c1 
----+----
  1 | 42
(1 row)

--
-- Repro 2: insert value
-- Credit: Alexander Kuzmenkov
-- 
create table ttt(ts int);
select create_hypertable('ttt', 'ts');
insert into ttt values (1);
alter table ttt set (timescaledb.compress, timescaledb.compress_segmentby = 'ts');
select compress_chunk(show_chunks('ttt'));
alter table ttt add column a int default 7;
insert into ttt values (2, null);
set timescaledb.enable_segmentwise_recompression to off;
select compress_chunk(show_chunks('ttt'));
select * from ttt;

 ts │ a 
────┼───
  1 │ 7
  2 │ 7

[dbeck]

Potentially related issue: when I alter-added the new column I set the default value to 42, and then I altered it to 99 and 33 at last. Somehow the 42 came back after compression.

# alter table t add column c2 set default 42;
# alter table t alter column c2 set default 99;
# insert into t (ts) values (1);
INSERT 0 1
dbeck=# select * from t;
 ts | c1 | c2 
----+----+----
  1 | 43 | 99
(1 row)

# alter table t alter column c2 set default 33;
# insert into t (ts) values (2);
# select * from t;
 ts | c1 | c2 
----+----+----
  1 | 43 | 99
  2 | 43 | 33
(2 rows)

# select compress_chunk(show_chunks('t'));
# select * from t;
 ts | c1 | c2 
----+----+----
  2 | 43 | 33
  1 | 43 | 99
(2 rows)

# update t set c1=null, c2=null;
# select * from t;
 ts | c1 | c2 
----+----+----
  2 |    |   
  1 |    |   
(2 rows)

# select compress_chunk(show_chunks('t'));
# select * from t;
 ts | c1 | c2 
----+----+----
  2 |    | 42
  1 |    | 42
(2 rows)

# \d+ t
                                            Table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 ts     | integer |           | not null |         | plain   |             |              | 
 c1     | integer |           |          | 43      | plain   |             |              | 
 c2     | integer |           |          | 33      | plain   |             |              | 
Indexes:
    "t_ts_idx" btree (ts DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON t FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()
Child tables: _timescaledb_internal._hyper_4_4_chunk
Access method: heap