How to handle inserts and updates on postgres temporal tables?

Question

I have couple temporal tables in my db.

CREATE TABLE temporal (
 version_id SERIAL PRIMARY KEY,
 some_id TEXT,
 some_data TEXT,
 valid_from TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 valid_to TIMESTAMP DEFAULT NULL,
 is_current BOOLEAN NOT NULL DEFAULT TRUE
);

I have created a trigger that acts on inserts

CREATE OR REPLACE TRIGGER my_trigger
 BEFORE INSERT ON temporal
 FOR EACH ROW 
 EXECUTE manage_temporal_version();

The plpgsql function the trigger calls is quite usual temporal versioning trigger that basically does two things:

1. It checks against some unique fields whether the table already has that row and if it's currently the active row or not
2. If such row is found, it deprecates the previous
3. Continues to insert a new row with the new data

 CREATE OR REPLACE FUNCTION manage_record_version()
 RETURNS TRIGGER AS $$
 DECLARE
 existing_row RECORD;
 where_clause TEXT;
 col_name TEXT;
 i INTEGER;
 BEGIN
 -- Build WHERE clause dynamically based on trigger arguments
 where_clause := 'is_current = TRUE';
 
 -- TG_ARGV contains the column names passed to the trigger
 FOR i IN 0 .. TG_NARGS - 1 LOOP
 col_name := TG_ARGV[i];
 
 -- Add condition for each key column
 where_clause := where_clause || format(' AND %I = 1ドル.%I', col_name, col_name);
 END LOOP;
 
 -- Find existing current row with matching key columns
 EXECUTE format('SELECT * FROM %I WHERE %s FOR UPDATE', TG_TABLE_NAME, where_clause)
 INTO existing_row
 USING NEW;
 
 -- If a current row exists, version it
 IF FOUND THEN
 -- Close the existing version
 EXECUTE format(
 'UPDATE %I SET valid_to = CURRENT_TIMESTAMP, is_current = FALSE WHERE version_id = 1ドル',
 TG_TABLE_NAME
 ) USING existing_row.version_id;
 
 -- Set up NEW record for the new version
 NEW.version_id := nextval(format('%s_version_id_seq', TG_TABLE_NAME));
 NEW.valid_from := CURRENT_TIMESTAMP;
 NEW.valid_to := NULL;
 NEW.is_current := TRUE;
 
 RETURN NEW;
 END IF;
 
 -- If no existing current row, proceed with normal insert
 RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

I was thinking about that I wouldn't like to let people run direct update queries on the table, because the table rows shouldn't be updated directly due to this temporal versioning. I was trying an approach that I will change the trigger to act before insert or update and then during insert I'll do the above procedure and during updates I will simply check whether the update is happening on already closed row or on the currently active row. If updating closed row an exception is thrown preventing the update and if updating the current row the update would follow similar procedure as the insert. Sounds good in theory, but there's one big problem:

The inserts and updates inside the triggered function will invoke the trigger as well leading to infinitely cascading triggers.

Is there any way to make this work? Or is there any commonly agreed good way to handle inserts and updates on temporal tables?

Answer 1

I prefer to use a range type, and I create a view for the current data, on which I perform the DML operations:

/* needed for the exclusion constraint */
CREATE EXTENSION IF NOT EXISTS btree_gist;
/* the exclusion constraint acts as a temporal primary key */
CREATE TABLE temporal (
 id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
 some_data text NOT NULL,
 valid tsrange DEFAULT tsrange(localtimestamp, NULL) NOT NULL,
 EXCLUDE USING gist (valid WITH &&, id with =)
);
/* a view of the current data */
CREATE VIEW current_data AS
 SELECT id, some_data FROM temporal WHERE valid @> TIMESTAMP 'infinity';
/* trigger function for the view */
CREATE OR REPLACE FUNCTION data_trig() RETURNS trigger
 LANGUAGE plpgsql AS
$$BEGIN
 CASE TG_OP
 WHEN 'INSERT' THEN
 /* allow default value */
 IF NEW.id IS NULL THEN
 INSERT INTO temporal (some_data)
 VALUES (NEW.some_data);
 ELSE
 INSERT INTO temporal (id, some_data)
 VALUES (NEW.id, NEW.some_data);
 END IF;
 RETURN NEW;
 WHEN 'DELETE' THEN
 UPDATE temporal
 SET valid = tsrange(lower(valid), localtimestamp)
 WHERE id = OLD.id AND valid @> TIMESTAMP 'infinity';
 RETURN OLD;
 WHEN 'UPDATE' THEN
 UPDATE temporal
 SET valid = tsrange(lower(valid), localtimestamp)
 WHERE id = OLD.id AND valid @> TIMESTAMP 'infinity';
 INSERT INTO temporal (id, some_data)
 VALUES (NEW.id, NEW.some_data);
 RETURN NEW;
 END CASE;
END;$$;
/* this fakes the DML operations on the view */
CREATE TRIGGER data_trig INSTEAD OF INSERT OR DELETE OR UPDATE ON current_data
 FOR EACH ROW EXECUTE PROCEDURE data_trig();

To get the data at a certain point in time, simply query temporal and add a condition like

WHERE valid @> '2025-04-01 00:00:00'

• zaplec: That looks a lot how temporal tables should be done in the first place. I need to take a look at that and see how I could implement that into my case. I already have data in those tables and in the format I mentioned, so for now looks like I am a bit stuck with that. But with some migrations and manipulations I might be able to convert the data. However, on the other hand I don't have many of those tables, so I wonder if all this hassle would be worth it. Definitely something to take a look at to for sure though