Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many

We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision involves multiple tables with one-to-many relationships.

Because the configuration is shipped off elsewhere, revisions are marked as deployed, and become immutable. Users have to create a new revision (which can be cloned from an existing one) if they want to make changes to a configuration. One revision per configuration can be marked as 'current'; this allows the users to switch between past revisions at will, or disable the configuration entirely by not picking any revision. The current revision is deployed, when marking a different revision as 'current' you replace the deployed config.

We already have everything in place to enforce immutability of deployed revisions; the deployed column is automatically transitioned to TRUE when you first use a revision as the current revision, and all further INSERT, UPDATE and DELETE operations concerning rows that match a deployed revision id in revision-related tables is blocked.

However, any value used for the name column in the public name table, must be unique across all the 'current' revisions across all current configurations. I'm trying to figure out the best strategy to enforce this.

If this was a plain one-to-many relationship from config to public names, this would be solved by using a unique constraint on the name column. This is, instead, a one-to-many-to-many pattern with revision acting as the bridge table, and the current_revision_id "collapses" the one-to-many-to-many to a virtual one-to-many relationship from config to public names.

Here is a simplified set of tables that illustrate our situation:

```
-- Configurations
CREATE TABLE config (
id INT PRIMARY KEY,
name VARCHAR(100),
current_revision_id INT
);

-- Have multiple revisions
CREATE TABLE revision (
id INT PRIMARY KEY

I did not follow the exact details of your data model, but a deferred constraint trigger is always subject to race conditions unless you operate with the SERIALIZABLE transaction isolation level.

The reason is that concurrent updates of config could cause the trigger function to run in parallel in two sessions, where they cannot see the effects of the other transaction, because no transaction has committed yet. Defining the trigger as INITIALLY DEFERRED narrows the window for the race condition, but it does not close it.

As an alternative to using SERIALIZABLE, you could modify your trigger function so that it takes locks that prevent it from running more than once at the same time. Transaction-level advisory locks come to mind as a simple way to do that.

StackExchange Database Administrators Q#292567, answer score: 3