[Bug]: `drop_chunk()` fails when there is an FK referencing a hypertable

[zilder]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Other

What happened?

drop_chunk() fails when there is a regular table referencing a hypertable, see the reproduction script.

TimescaleDB version affected

2.16.0

PostgreSQL version used

15.4

What operating system did you use?

Ubuntu 22.04.2

What installation method did you use?

Source

What platform did you run on?

Not applicable

Relevant log output and stack trace

No response

How can we reproduce the bug?

CREATE TABLE metrics (id int8 PRIMARY KEY,
                      ts timestamptz, 
                      device_id integer,
                      val float);
INSERT INTO metrics VALUES (101, '2024-08-06', 1, 123);
SELECT create_hypertable('metrics', 'id', chunk_time_interval=>100, migrate_data=>true);

CREATE TABLE notes (metrics_id integer REFERENCES metrics(id) ON DELETE CASCADE, msg text);
INSERT INTO notes values (101, 'test');

-- fails here
SELECT _timescaledb_functions.drop_chunk('_timescaledb_internal._hyper_2_7_chunk');

Output:

SELECT _timescaledb_functions.drop_chunk('_timescaledb_internal._hyper_2_7_chunk');
ERROR:  cannot drop constraint 7_6_metrics_pkey on table _timescaledb_internal._hyper_2_7_chunk because other objects depend on it
DETAIL:  constraint notes_metrics_id_fkey on table notes depends on index _timescaledb_internal."7_6_metrics_pkey"
HINT:  Use DROP ... CASCADE to drop the dependent objects too.