Constraints over multiple rows in PostgreSQL

Enforcing Row-Spanning Integrity Rules in PostgreSQL

PostgreSQL developers are generally comfortable with primary keys, foreign keys, and CHECK constraints. But some integrity rules don't fit neatly into those categories. A classic example: ensuring that the ownership shares of an airplane always sum to exactly 100%. The constraint spans multiple rows in a child table, and the real challenge is handling concurrent modifications correctly.

Consider a simple schema with two tables: t_plane holds a unique plane ID and its call sign, while a second table stores each owner's percentage share for a given plane.

If two users simultaneously modify ownership shares for the same aircraft, nothing in the standard constraint toolkit stops them from producing an invalid total. One user might add a 50% share while another adds a different 50% share, leaving the plane "owned" by 200% of its parts.

The Heavy-Hammer Approach: Table Locks

The most direct solution is to serialize all modifications with a table-level lock. PostgreSQL supports eight lock levels, and the one that fits here is EXCLUSIVE. This lock allows concurrent reads but ensures only one transaction at a time can modify the table. The heavier ACCESS EXCLUSIVE lock would block reads too, which is unnecessary for this use case.

With the lock in place, the sequence of events becomes predictable: User 1 locks the table, performs the update, verifies the sum, and commits. If User 2 attempts a conflicting modification during that window, the second transaction waits until the first completes, then proceeds to check the constraint—and typically rolls back when the total no longer equals 100%.

The downside is scalability. A table lock means only one transaction can touch the table at any moment. If you need to update ownership records for 100 different planes concurrently, 99 of those transactions will sit idle waiting for the lock to release. For high-traffic scenarios, this approach wastes the parallelism your hardware provides.

Relying on SERIALIZABLE Isolation

A more elegant alternative is to switch the transaction isolation level to SERIALIZABLE. PostgreSQL implements three of the four ANSI SQL isolation levels; READ UNCOMMITTED is mapped to READ COMMITTED, which is sensible under MVCC. SERIALIZABLE gives the illusion of sequential execution while still allowing concurrency underneath.

Using SERIALIZABLE, you can write a plain transaction that inserts ownership rows and then checks the total percentage before committing. No explicit LOCK TABLE, no SELECT FOR UPDATE—just a normal transaction plus the application's own verification query. If two transactions touch the same data in ways that could break the illusion of sequential execution, PostgreSQL detects the conflict at commit time and aborts one of them.

For example, User 1 begins a SERIALIZABLE transaction, inserts two 50% owners for a new aircraft, verifies the sum is 100, and commits. Meanwhile, User 2 has inserted conflicting ownership data for the same plane. When User 2 attempts to commit, the database raises a serialization error rather than silently allowing a state that could not have occurred under sequential execution.

The application must be prepared to catch that error and retry the transaction. This is the standard tradeoff: SERIALIZABLE shifts the burden of conflict resolution from locks to the application layer, but it unlocks far better concurrency for workloads where conflicts are rare.

Why Not SELECT FOR UPDATE?

A common question is why SELECT FOR UPDATE cannot solve the problem. Row-level locks do protect existing rows from concurrent changes, but they do nothing to prevent other transactions from inserting new rows. Since the ownership constraint is violated by an insert as easily as by an update, row locking leaves a significant hole. SELECT FOR UPDATE simply cannot guard against future rows that don't exist yet.

The other frequent objection is practical: scenarios like airplane ownership rarely change, so why go to the trouble? The answer is that correct handling of multi-row constraints costs little effort and prevents subtle data corruption. When SERIALIZABLE is implemented properly, the database does the heavy lifting of detecting conflicts, so the application logic remains simple and the concurrency benefits are substantial even under low contention.