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:

It checks against some unique fields whether the table already has that row and if it's currently the active row or not
If such row is found, it deprecates the previous
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?

Laurenz Albe · Accepted Answer · 2025-10-21 16:24:15Z

2

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'

answered Oct 21 at 16:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zaplec Oct 21 at 17:53

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

Collectives™ on Stack Overflow

How to handle inserts and updates on postgres temporal tables?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related