[Bug]: BEFORE INSERT trigger on partitioning columns does not work

[svenklemm]

What type of bug is this?

Incorrect result, Unexpected error

What subsystems and features are affected?

Data ingestion

What happened?

Inserting into a hypertable with a BEFORE INSERT trigger on partitioning columns fails to take the value modified by the trigger into account when doing tuple routing.

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

ERROR:  23502: NULL value in column "c1" violates not-null constraint
HINT:  Columns used for time partitioning cannot be NULL.
LOCATION:  ts_hyperspace_calculate_point, dimension.c:981
Time: 1.023 ms

How can we reproduce the bug?

CREATE TABLE metrics(c1 timestamptz, c2 timestamptz);
SELECT create_hypertable('metrics', 'c1');

CREATE OR REPLACE FUNCTION coalesce_timestamps()
RETURNS TRIGGER AS $$
BEGIN
NEW.ts := COALESCE(NEW."c1",NEW."c2");
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER coalesce_timestamps_trigger
BEFORE INSERT ON metrics
FOR EACH ROW 
EXECUTE FUNCTION coalesce_timestamps();

INSERT INTO metrics(c2) VALUES (NOW());