[Bug]: drop_chunks and _timescaledb_functions.drop_chunk don't respect FKs against _timescaledb_catalog.chunk

[brasic]

What type of bug is this?

Data corruption

What subsystems and features are affected?

Other

What happened?

Our application needs to keep an index on a set of per-chunk values stored in compressed hypertable chunks. These values should be dropped when the chunk is dropped, so we created a foreign key from our table to the _timescaledb_catalog.chunk table. But when timescale drops chunks either on policy-driven schedule or manually via public.drop_chunks/_timescaledb_functions.drop_chunk, the foreign key constraint is not respected: chunk ids that are no longer valid continue to exist in the referencing table rather than being deleted on cascade as one would expect.

TimescaleDB version affected

2.16.1

PostgreSQL version used

16.3

What operating system did you use?

Ubuntu 22.04 x64

What installation method did you use?

Deb/Apt, Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

CREATE TABLE metrics (
  time timestamptz NOT NULL
);
SELECT create_hypertable('metrics', 'time');
CREATE TABLE chk_ref (
  chunk_id integer NOT NULL REFERENCES _timescaledb_catalog.chunk(id) ON DELETE CASCADE
);
INSERT into metrics(time) values(now()); -- now one chunk exists
INSERT INTO chk_ref(chunk_id) select id from _timescaledb_catalog.chunk;
select drop_chunks('metrics', created_before => now()); -- drops the only chunk,
-- _timescaledb_catalog.chunk is now empty, so should any table referencing it
select * from chk_ref; -- should be empty, instead returns id of nonexistent chunk:
                       --   chunk_id
                       --  ----------
                       --           1
                       --  (1 row)
-- You can also see the issue by forcing a constraint violation like:
insert into chk_ref (chunk_id) select chunk_id from chk_ref;
-- ERROR:  insert or update on table "chk_ref" violates foreign key constraint "chk_ref_chunk_id_fkey"
-- DETAIL:  Key (chunk_id)=(1) is not present in table "chunk".