Postgres insert into `select distinct` view if not exists

Question

I have an events table which is a denormalized table containing item data. There is a one-to-many relationship between items and events.

CREATE TABLE events
(
 id UUID PRIMARY KEY,
 item VARCHAR(16) NOT NULL,
 created TIMESTAMPTZ NOT NULL...
)

I also have a SELECT DISTINCT view called items which essentially takes the last event row for each item. I would really like to be able to insert into that view as though it were a physical table, including a constraint error if the user-provided item ID already exists.

I believe I need to create a trigger function, but I'm not sure what the query would look like to return a constraint violation (or maybe some other exception that callers can catch?). I think trigger functions have to be PL/pgSQL, but ideally it would hew as closely as possible to plain SQL.

• mustaccio: Presumably that trigger would have to insert a row into the underlying items table, which will obviously fail if the corresponding unique constraint is violated, so I'm not really sure what is the problem.
• weberc2: @mustaccio items isn't a table, is a view into the events table. Consequently there is no unique constraint to violate (the only unique constraint in the system is events.id).
• Erwin Brandstetter: Please clarify the task. Show the exact view definition and your version of Postgres. Why do you want a "constraint error" (without constraint) for duplicate user-provided item ID, while the table events is obviously designed to hold multiple rows per item. Why not create an actual item table with unique items? (Or is that an option?)

Answer 1

Let's say that the view is defined as

CREATE VIEW event_items AS SELECT DISTINCT item FROM events;

Writing a trigger like that is not difficult; the problem is that such triggers always suffer from race conditions with concurrent INSERTs.

There are only two ways to avoid race conditions:

1. Lock all affected rows to serialize operations:

   CREATE FUNCTION ins_trig() RETURNS trigger
    LANGUAGE plpgsql AS
   $$BEGIN
    PERFORM FROM events
    WHERE item = NEW.item
    FOR NO KEY UPDATE;
    IF FOUND THEN
    RAISE EXCEPTION unique_violation
    USING MESSAGE = 'duplicate key value violates unique constraint',
    DETAIL = format('Key (item)=(%s) already exists.', NEW.item);
    END IF;
    INSERT INTO events (id, item, created)
    VALUES (gen_random_uuid(), NEW.item, current_timestamp);
    RETURN NULL;
   END;$$;
   CREATE TRIGGER ins_trig INSTEAD OF INSERT ON event_items
    FOR EACH ROW EXECUTE FUNCTION ins_trig();
   

2. Set default_transaction_isolation = 'serializable' to use the SERIALIZABLE isolation level throughout and remove the FOR NO KEY UPDATE in the above example.

   For that to work well, you need an index on events.items, otherwise you'll get predicate locks on the whole table and very bad concurrency.

   Note that with SERIALIZABLE isolation you have to be prepared to get serialization errors, which require you to retry the INSERT until it succeeds.

See my article for some background information.