hypertable constraint blocks trigger from copying values before insert

[mxfactorial]

Relevant system information:

• OS: macos big sur, 11.5.2
• PostgreSQL version (output of postgres --version): 13.3
• TimescaleDB version (output of \dx in psql): 2.4
• Installation method: docker, timescale/timescaledb:latest-pg13

Describe the bug hypertable constraint blocks trigger from copying values before insert

To Reproduce Steps to reproduce the behavior:

1. save below as start.sh
2. uncomment -- select create_hypertable('transaction_item', 'created_at'); line
3. chmod +x start.sh && ./start.sh

*note: created_at value copies with zero errors when select create_hypertable() statement remains commented

#!/bin/bash

docker run \
    --rm \
    -d \
    --name timescaledb \
    -p 5433:5432 \
    -e POSTGRES_PASSWORD=password \
    timescale/timescaledb:latest-pg13

sleep 2 # wait for postgres availability

docker exec timescaledb psql -U postgres -d postgres -c "CREATE DATABASE accounting;"

sql="CREATE TABLE transaction (
id SERIAL,
author character varying(255) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_at)
);

CREATE TABLE transaction_item (
id SERIAL,
transaction_id integer NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (id, created_at)
);

-- select create_hypertable('transaction_item', 'created_at');

CREATE OR REPLACE FUNCTION copy_transaction_created_at()
    RETURNS trigger AS
\$\$
BEGIN
    NEW.created_at = (SELECT created_at FROM transaction WHERE id = NEW.transaction_id);
    RETURN NEW;
END;
\$\$
LANGUAGE plpgsql;

CREATE TRIGGER copy_transaction_created_at
BEFORE INSERT ON transaction_item
FOR EACH ROW
EXECUTE PROCEDURE copy_transaction_created_at();

insert into transaction (author) values ('GroceryStore');

insert into transaction_item (transaction_id) values (1);"

docker exec timescaledb psql -U postgres -d accounting -c "${sql}"

docker exec timescaledb psql -U postgres -d accounting -c "select * from transaction;"
docker exec timescaledb psql -U postgres -d accounting -c "select * from transaction_item;"

docker stop timescaledb >/dev/null

Expected behavior trigger copies transaction.created_at value to transaction_item.created_at before transaction_item insert

CREATE DATABASE
INSERT 0 1
 id |    author    |          created_at
----+--------------+-------------------------------
  1 | GroceryStore | 2021-09-05 23:36:05.670838+00
(1 row)

 id | transaction_id |          created_at
----+----------------+-------------------------------
  1 |              1 | 2021-09-05 23:36:05.670838+00
(1 row)

Actual behavior hypertable not-null constraint violates before trigger copies value

ERROR:  NULL value in column "created_at" violates not-null constraint
HINT:  Columns used for time partitioning cannot be NULL.

Screenshots If applicable, add screenshots to help explain your problem.

Additional context Add any other context about the problem here.

[mxfactorial]

workaround

timescale requires a default timestamp on the column targeted by the triggered function

the triggered function then overwrites the default timestamp

#!/bin/bash

docker run \
    --rm \
    -d \
    --name timescaledb \
    -p 5433:5432 \
    -e POSTGRES_PASSWORD=password \
    timescale/timescaledb:latest-pg13

sleep 2 # wait for postgres availability

docker exec timescaledb psql -U postgres -d postgres -c "CREATE DATABASE accounting;"

sql1="CREATE TABLE transaction (
id SERIAL,
author character varying(255) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_at)
);

CREATE TABLE transaction_item (
id SERIAL,
transaction_id integer NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_at)
);

select create_hypertable('transaction_item', 'created_at');

CREATE OR REPLACE FUNCTION copy_transaction_created_at()
    RETURNS trigger AS
\$\$
BEGIN
    NEW.created_at = (SELECT created_at FROM transaction WHERE id = NEW.transaction_id);
    RETURN NEW;
END;
\$\$
LANGUAGE plpgsql;

CREATE TRIGGER copy_transaction_created_at
BEFORE INSERT ON transaction_item
FOR EACH ROW
EXECUTE PROCEDURE copy_transaction_created_at();

insert into transaction (author) values ('GroceryStore');"

sql2="insert into transaction_item (transaction_id) values (1);"

docker exec timescaledb psql -U postgres -d accounting -c "${sql1}"

sleep 1

docker exec timescaledb psql -U postgres -d accounting -c "${sql2}"

docker exec timescaledb psql -U postgres -d accounting -c "select * from transaction;"
docker exec timescaledb psql -U postgres -d accounting -c "select * from transaction_item;"

docker stop timescaledb >/dev/null