Timescaledb: Support transition tables in triggers on hypertable

[ancoron]

In order to improve performance of custom triggers dramatically, please support the use of transition tables as of PostgreSQL 10, e.g.:

CREATE TRIGGER trg_tracking_insert
    AFTER INSERT ON t_tracking
    REFERENCING NEW TABLE AS newrows -- temporary new table data for bulk inserts
    FOR EACH STATEMENT
    EXECUTE PROCEDURE fnt_tracking_insert()
;

Furthermore, while creation of such a trigger works, any attempt to actually INSERT data into the affected hypertable will fail with:

ERROR:  hypertables do not support transition tables in triggers

I would have expected to get this error while trying to create the trigger, not while executing an INSERT afterwards (which might be handled separately as a bug or enhancement).

TimescaleDB version: 1.2.1

[gajus]

1. Should definitely be an alert at the time of creating the trigger. The issue went unnoticed for couple of days.
2. Is there a workaround?

[cloud-rocket]

This feature is needed to optimize MIN/MAX queries which sometimes can take several minutes (https://stackoverflow.com/questions/58868527/optimizing-min-max-queries-on-time-series-data/58889609)

[belst]

I wanted to use this to create custom notification logic:

create or replace function monitor_values() returns trigger as $$
begin
    perform pg_notify('monitor_queue', json_agg(inserted)::text)
    from inserted;
    return NULL;
end;
$$ language plpgsql;

    create trigger monitor_vals
        after insert on data.values
        referencing new table as inserted
        for each statement execute function monitor_values();

it is important for me to get a complete statement and not just each inserted row, because the logic for the notification depends on multiple rows which always get inserted in a single statement.

[racosa]

I was getting a segmentation fault when trying to this and ended up here. With a regular SQL table it works correctly.

Is there a way to access NEW TABLE inside a trigger function on a hypertable?

Thanks