[Bug]: Foreign keys on compressed hypertables not working

[svenklemm]

What type of bug is this?

Data corruption

What subsystems and features are affected?

Compression

What happened?

Foreign keys on compressed hypertables are not correctly working. Inserts with keys that are not present in the referenced table are possible on compressed chunks.

TimescaleDB version affected

2.14.2

PostgreSQL version used

16.2

What operating system did you use?

ArchLinux

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

sven@fk_repro[1299973]=# CREATE TABLE keys(time timestamptz unique);
CREATE TABLE

sven@fk_repro[1299973]=# CREATE TABLE fk(time timestamptz);
CREATE TABLE

sven@fk_repro[1299973]=# select create_hypertable('fk','time');
NOTICE:  00000: adding not-null constraint to column "time"
DETAIL:  Dimensions cannot have NULL values.
LOCATION:  dimension_add_not_null_on_column, dimension.c:1149
 create_hypertable
-------------------
 (1,public,fk,t)
(1 row)

sven@fk_repro[1299973]=# ALTER TABLE fk ADD CONSTRAINT fk_keys FOREIGN KEY (time) REFERENCES keys(time) ON DELETE CASCADE;
ALTER TABLE

sven@fk_repro[1299973]=# ALTER TABLE fk SET (timescaledb.compress,timescaledb.compress_segmentby='time');
ALTER TABLE

sven@fk_repro[1299973]=# INSERT INTO fk SELECT '2000-01-01';
ERROR:  23503: insert or update on table "_hyper_1_1_chunk" violates foreign key constraint "1_1_fk_keys"

sven@fk_repro[1299973]=# INSERT INTO keys SELECT '2000-01-01';
INSERT 0 1

sven@fk_repro[1299973]=# INSERT INTO fk SELECT '2000-01-01';
INSERT 0 1

sven@fk_repro[1299973]=# INSERT INTO fk SELECT '2001-01-01';
ERROR:  23503: insert or update on table "_hyper_1_3_chunk" violates foreign key constraint "3_3_fk_keys"

sven@fk_repro[1299973]=# SELECT compress_chunk('_timescaledb_internal._hyper_1_2_chunk');
             compress_chunk
----------------------------------------
 _timescaledb_internal._hyper_1_2_chunk
(1 row)

sven@fk_repro[1299973]=# INSERT INTO fk SELECT '2000-01-02';
INSERT 0 1

sven@fk_repro[1299973]=# INSERT INTO fk SELECT '2001-01-01';
ERROR:  23503: insert or update on table "_hyper_1_5_chunk" violates foreign key constraint "5_4_fk_keys"

sven@fk_repro[1299973]=# SELECT * from keys;
          time
------------------------
 2000-01-01 00:00:00+01
(1 row)

Time: 1.859 ms
sven@fk_repro[1299973]=# SELECT * from fk;
          time
------------------------
 2000-01-01 00:00:00+01
 2000-01-02 00:00:00+01
(2 rows)