Transaction strategy for UNIQUE constraint?

Question

How to use a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day?

It was suggested to me that it will different for every isolation level. Could you add an example for every one of them? (read committed, repeatable read and serializable). I would like to understand every one of them.

Here are tables and test data:

CREATE TABLE place (
  place_id INT                   PRIMARY KEY,
  Name     CHARACTER VARYING(50) NOT NULL,
  Type     CHARACTER VARYING(50) NOT NULL
);

CREATE TABLE visit (
  visit_id SERIAL PRIMARY KEY,
  place_id INT NOT NULL,
  place_dt TIMESTAMP NOT NULL,

  FOREIGN KEY (place_id) REFERENCES place(place_id)
);

INSERT INTO place(place_id, Name, Type
) VALUES
    (1, 'Denali', 'mountain'),
    (2, 'Brindley', 'mountain'),
    (3, 'St. Louis Cathedral', 'church')
;

INSERT INTO visit(place_id, place_dt
) VALUES
    (1, '2019-01-02 10:00'),
    (2, '2019-01-02 11:00'),
    (3, '2019-01-03 14:09')
;

fiddle

---

What I want is that for each isolation level, look for a transaction-based strategy to ensure that the same location cannot be booked more than once on the same day.

Given that it could be the case where a strategy could be optimal for various levels of isolation.

Answer 1

What Laurenz said. Be aware that Postgres checks (has to check) across transaction isolation boundaries for unique violations (the exception raised by a duplicate entry into a UNIQUE index). Those checks are "absolute".
Details in the chapter "Index Uniqueness Checks" in the manual.

But use this optimised, equivalent solution, while being at it:

CREATE UNIQUE INDEX ON visit (place_id, cast(place_dt AS date));

place_id is integer (4 bytes).
date_trunc() returns timestamp for timestamp input (8 bytes).
date only occupies 4 bytes.
Space is typically allocated in multiples of 8 bytes.

An index on (integer, timestamp) effectively occupies 28 bytes per index tuple.
An index on (integer, date) effectively occupies 20 bytes per index tuple. So the other one is 40% bigger for no gain.

More details:

• Configuring PostgreSQL for read performance

Aside: the index also helps performance of the FK constraint on visit (place_id) - while you put place_id first.

Answer 2

For that, you simply put a constraint on visit in the shape of a unique index:

CREATE UNIQUE INDEX ON visit (place_id, date_trunc('day', place_dt));

That will guarantee uniqueness, no matter what the isolation level is.