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. 😢 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

[juantxorena]

5 years of this bug. Some comment, even a "we won't implement this", would be nice.

[jflambert]

@juantxorena I'm with you, but realistically I wonder if this isn't more of an upstream issue. Are transition tables supported on vanilla postgres partitioning?

[juantxorena]

I managed to implement this manually with two triggers. For now it's working, and I have compression activated (and running) and everything. I put it as a reference:

This will executed per row and store the changes in a temp table. The good thing is that you can simply store the columns you need.

CREATE OR REPLACE FUNCTION collect_inserted_rows()
 RETURNS TRIGGER AS $trigger$
BEGIN
     CREATE TEMP TABLE IF NOT EXISTS temp_inserted_rows (
         vehicle_id UUID,
         timestamp TIMESTAMPTZ
     ) ON COMMIT DROP;
    INSERT INTO temp_inserted_rows (vehicle_id, timestamp)
    VALUES (NEW.vehicle_id, NEW.timestamp);
    RETURN NEW;
END;
$trigger$ LANGUAGE plpgsql;
CREATE TRIGGER collect_rows_trigger
AFTER INSERT ON measurements
FOR EACH ROW
EXECUTE FUNCTION collect_inserted_rows();

Now another trigger per statement, which will fetch all the data of that table. In my case it's for sending a notification, I guess it could be any other logic you need:

CREATE OR REPLACE FUNCTION notify_trigger()
 RETURNS TRIGGER AS $trigger$
DECLARE
    payload TEXT;
BEGIN
	SELECT jsonb_agg(row_to_json(t))
	INTO payload
	FROM (
		SELECT DISTINCT vehicle_id, timestamp
		FROM temp_inserted_rows
	) t;
		
	PERFORM pg_notify('realtimedata', payload::text);
    RETURN NULL;
END;
$trigger$ LANGUAGE plpgsql;
CREATE TRIGGER notify_on_commit
AFTER INSERT ON measurements
FOR EACH STATEMENT
EXECUTE FUNCTION notify_trigger();

As I said, for me is working, so if somebody is interested, you can have a try.

[ancoron]

Just for reference, because @jflambert asked about it, triggers using transition tables are supported for e.g. declarative partitioned tables:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2024m09 PARTITION OF measurement
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');
CREATE TABLE measurement_y2024m10 PARTITION OF measurement
    FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
CREATE TABLE measurement_y2024m11 PARTITION OF measurement
    FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');

CREATE INDEX ON measurement (logdate);

INSERT INTO measurement (logdate, city_id, peaktemp, unitsales) VALUES ('2024-09-02', 123, 21, 8765);
INSERT INTO measurement (logdate, city_id, peaktemp, unitsales) VALUES ('2024-10-13', 123, 13, 4096);
INSERT INTO measurement (logdate, city_id, peaktemp, unitsales) VALUES ('2024-11-05', 123, 8, 10256);

CREATE OR REPLACE FUNCTION fnt_tracking_insert()
RETURNS TRIGGER AS $$
DECLARE
    tmp RECORD;
BEGIN
    FOR tmp IN SELECT logdate, count(DISTINCT city_id) AS c_count FROM newrows GROUP BY logdate
    LOOP
        RAISE WARNING 'New measurements at % (% cities)', tmp.logdate, tmp.c_count;
    END LOOP;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

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

INSERT INTO measurement (logdate, city_id, peaktemp, unitsales) VALUES
    ('2024-10-24', 123, 12, 1234),
    ('2024-10-24',  23, 11,  511),
    ('2024-10-24',  42, 13, 2501)
;

The last INSERT will print a warning to our server log:

2024-10-24 08:42:30.678 GMT [131] WARNING:  New measurements at 2024-10-24 (3 cities)

So yes, this is an explicit limitation by Timescale.

@juantxorena Thanx for the workaround!

[jflambert]

Awesome proof @ancoron and yes I use a workaround similar to @juantxorena, great minds think alike ;)

[oliora]

One thing to note. The solution proposed by @juantxorena is great and working but it is still a workaround as it has a high cost associated with per row triggers. The only way to avoid such a cost is adding transition tables support in TimescaleDB.

[jflambert]

Absolutely, it needs to be written in C. And if Timescale wants to one-up postgres, I have suggestions:

• specify columns in the transition table (for performance)
• allow aggregates on said columns (for example min(timestamp) of transition)

[jflambert]

wow! release ETA @mkindahl ? excited to test it out.

[mkindahl]

wow! release ETA @mkindahl ? excited to test it out.

We are tentatively planning to do the next release (2.18) after Christmas.