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

[ebreijo]

Any updated on this issue or any workaround to get a batch of rows?

[Codesleuth]

The scariest thing about this issue is that it can be applied-to and works fine for the current chunk. As soon as your chunk rolls over to the next (say if you store daily, rolls over at midnight) the hypertable will stop functioning with error:

hypertables do not support transition tables in triggers

This almost got out to our production system. It's quite a flaw.

[dhendry]

Would love to see this capability

[yaoyuan12]

This would be great.

[jledentu]

We were implementing statement-level triggers (to refresh global stats) when we noticed this issue on hypertables. That's a serious limitation. :cry: Is there any workaround?

[anthonyorona]

There ought to be a mention of this in the TSDB docs. Is there any discussion regarding the addition of this support? I do not see much via search

[jflambert]

please please please support this :)

[oliora]

That's a serious limitation to us because row level triggers are several times slower than statement triggers