[Bug]: FK to hypertable fails intermittently under repeated inserts

[hohumsup]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Data ingestion

What happened?

Summary

according to the docs, hypertable supports foreign key constraints from a regular table referencing a hypertable

however, fk violations occur under repeated insert patterns, even though parent rows are inserted first and the fk key matches

this is on:

• TimescaleDB 2.20.3
• PostgreSQL 17.6

Expected Behavior

for regular_table(child_fk) -> hypertable(parent_pk):

• FK checks should consistently pass whenever parent key exists

Actual Behavior

under repeated/bulk insert patterns, inserts into the regular child table fail with:

ERROR: insert or update on table "pos2" violates foreign key constraint ...
DETAIL: Key (instance_id, instance_created_at)=(..., ...) is not present in table "inst2".

the same key pair exists in inst2

TimescaleDB version affected

2.20.3

PostgreSQL version used

17.6

What operating system did you use?

macOS Tahoe 26.2

What installation method did you use?

Docker

What platform did you run on?

Not applicable

Relevant log output and stack trace

ERROR: insert or update on table "pos2" violates foreign key constraint ...
DETAIL: Key (instance_id, instance_created_at)=(..., ...) is not present in table "inst2".

How can we reproduce the bug?

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE inst2 (
  instance_id uuid NOT NULL DEFAULT uuid_generate_v1(),
  created_at timestamptz NOT NULL,
  payload text,
  PRIMARY KEY (instance_id, created_at)
);
SELECT create_hypertable('inst2','created_at', if_not_exists => TRUE);

CREATE TABLE pos2 (
  instance_id uuid NOT NULL,
  instance_created_at timestamptz NOT NULL,
  lat double precision,
  lon double precision,
  FOREIGN KEY (instance_id, instance_created_at)
    REFERENCES inst2(instance_id, created_at)
);

// a single insert is fine but running running a high volume of inserts will fail

WITH p AS (
  INSERT INTO inst2(created_at,payload)
  VALUES (clock_timestamp(),'single')
  RETURNING instance_id, created_at
)
INSERT INTO pos2(instance_id, instance_created_at, lat, lon)
SELECT instance_id, created_at, 1.0, 2.0 FROM p;

[hohumsup]

i think the latest version 2.25.0 seems to address my issue