PPalabas APIJava field course

World 2A / Quest 2A.3

Postgres Exclusion Constraint

Enforce no overlapping showtimes per screen with a GiST exclusion constraint.

Concept

This is the first senior signal in the schema. Postgres can enforce temporal non-overlap directly, but only if the row stores the full occupancy window.

Task

  1. Enable btree_gist in V1.
  2. Add the showtimes_no_screen_overlap exclusion constraint.
  3. Use tstzrange(starts_at, occupied_until, '[)') so back-to-back showtimes are allowed.
  4. Document why Oracle needs a different design later.

Run

./gradlew test --tests "*Phase2ASchemaContractTest"

Expected Result

  • The schema contract test sees btree_gist, exclude using gist, and the half-open tstzrange.

Common Traps

  • Using a closed range and blocking valid back-to-back showtimes.
  • Forgetting btree_gist for screen_id equality.
  • Pretending the same constraint will port to Oracle.

Hint Ladder

Hint 1

The `[)` range includes the start and excludes the end.

Hint 2

The screen_id comparison is equality; the time comparison is overlap.

Hint 3

Oracle's answer comes in Phase 3, not Phase 2A.

Solution

See constraint showtimes_no_screen_overlap in V1__schema.sql.