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,
>     config_id INT NOT NULL REFERENCES config(id),
>     created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
>     description VARCHAR,
>     foo INT NOT NULL,
>     bar BOOLEAN NOT NULL,
>     deployed BOOLEAN NOT NULL DEFAULT FALSE
> );
> 
> -- A configuration has one _current_ revision
> ALTER TABLE config
>   ADD CONSTRAINT current_revision_id_fk
>   FOREIGN KEY (current_revision_id)
>   REFERENCES revision(id);
>   
> -- Revisions are automatically numbered in a view
> CREATE VIEW numbered_revision AS (
>     SELECT *,
>     row_number() OVER (
>         PARTITION BY config_id
>         ORDER BY created_at, id
>     ) AS number
>     FROM revision
> );
> 
> -- Configurations have multiple 'public names'
> CREATE TABLE public_name (
>     id INT PRIMARY KEY,
>     revision_id INT NOT NULL REFERENCES revision(id),
>     name VARCHAR(100),
> 
>     UNIQUE (revision_id, name)
> );
> 
> ```
> 
> 

The view only serves to provide revisions with gapless numbers per config (revisions are never deleted).

As an ERD diagram:

[![diagram of tables](https://i.stack.imgur.com/2asCn.png)](https://i.stack.imgur.com/2asCn.png)

Some sample data to illustrate the setup:

> 
> 
> ```
> INSERT INTO config (id, name) VALUES
>   (17, 'config_foo'),
>   (42, 'config_bar');
>   
> INSERT INTO revision (id, config_id, created_at, description, foo, bar) VALUES
>   (11, 17, '2021-05-29 09:07:18', 'Foo configuration, first draft', 81, TRUE),
>   (19, 17, '2021-05-29 10:42:17', 'Foo configuration, second draft', 73, TRUE),
>   (23, 42, '2021-05-29 09:36:52', 'Bar configuration, first draft', 118, FALSE);
> 
> INSERT INTO public_name (id, revision_id, name) VALUES
>   -- public names for foo configuration, first draft
>   (83, 11, 'some.name'),
>   (84, 11, 'other.name'),
>   -- public names for foo configuration, second draft
>   (85, 19, 'revised.name'),
>   (86, 19, 'other.name'),
>   (87, 19, 'third.name'),
>   -- public names for bar configuration, first draft;
>   -- some of the names here are the same used by foo configurations
>   (88, 23, 'some.name'),
>   (89, 23, 'unique.name'),
>   (90, 23, 'other.name');
>   
> -- Foo configuration has a current, published revision:
> UPDATE config SET current_revision_id = 19 WHERE id = 17;
> UPDATE revision SET deployed = TRUE WHERE id in (11, 19);
> 
> ```
> 
> 

Here is a query showing the sample dataset:

> 
> 
> ```
> SELECT
>   c.name AS config,
>   rev.number AS revision,
>   rev.deployed,
>   CASE WHEN c.current_revision_id = rev.id 
>     THEN 'ACTIVE'
>     ELSE ''
>   END AS status,
>   string_agg(p.name, ', ' ORDER BY p.name) AS names
> FROM config c
> JOIN numbered_revision AS rev ON c.id = rev.config_id
> JOIN public_name p ON p.revision_id = rev.id
> GROUP BY c.id, rev.id, rev.number, rev.deployed
> ORDER BY c.id, rev.number;
> 
> ```
> 
> 
> 
> 
> | config | revision | deployed | status | names |
> | --- | --- | --- | --- | --- |
> | config\_foo | 1 | t |  | other.name, some.name |
> | config\_foo | 2 | t | ACTIVE | **other.name**, revised.name, third.name |
> | config\_bar | 1 | f |  | **other.name**, some.name, unique.name |
> 
> 
> 

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a4602704df384c1f1d5f6a170d3bd230)*

In the above output table, the second row represents a "current" revision, made public *deployed*), and that row has been given exclusive access to the public names in the `names` column.

The third row represents a configuration with a draft revision. Any attempts to set it as *current* for `config_bar` should fail because the name `other.name` is already in use for `config_foo`, revision 2. If, in the future, `config_foo` were to create a new revision that doesn't include `other.name`, only then could `config_bar` revision 1 be made current.

We do pre-validate this constraint; the API runs some checks and blocks marking a configuration as current when pre-conditions are not met. Names in the `public_name` table are also constrained to be unique per revision (`UNIQUE (revision_id, name)`). Neither of these prevents a race condition, they just reduce the rate at which race conditions happen.

I was hoping a CONSTRAINT TRIGGER on `config`, firing on `UPDATE`s of the `current_revision_id` column, would be sufficient to enforce this constraint:

```
CREATE OR REPLACE FUNCTION unique_current_names() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF EXISTS (
     SELECT 1
     FROM public_name p
     WHERE
       p.revision_id = NEW.current_revision_id
     AND p.name IN (
       SELECT pp.name 
       FROM config AS pc
       JOIN public_name pp ON pp.revision_id = pc.current_revision_id
        AND pc.id != OLD.id
     )
   ) THEN
      RAISE EXCEPTION 'Public name is already published';
   END IF;

   RETURN NEW;
END;$$;

DROP TRIGGER IF EXISTS unique_current_names_trig ON config;
CREATE CONSTRAINT TRIGGER unique_current_names_trig
   AFTER UPDATE OF current_revision_id ON config
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE unique_current_names();

```

(Note that the relationship between `config` and `public_name` is, in the general case, a many-to-many connection, but for the more specific `current_revision_id` case, it is a one-to-many connection, and you can use `config.current_version_id = public_name.version_id` to list the names directly.)

My concern is that, even though this trigger fires at the very end of a transaction, there is still the possibility of a race condition, wherein another connection also tries to make a revision current with conflicting public names.

OTOH, because all updates and inserts are the results of RESTFul API operations, there will never be a transaction that includes multiple operations (updates of `public_name`, *and* setting `current_revision_id`). Is that enough to prevent race conditions here, or are there corner cases I missed?

Another option might be to copy the public names of the current revision into a separate “published names” table (with a trigger; delete all old names, insert all new names), with a UNIQUE constraint on the name column there. Would that work better than the constraint trigger?

Note that we can’t use namespaces or other additions to the names (which are hostnames, on the public internet) to make them unique. The names must be unique entirely on their own, once deployed.

We are aware the design allows a configuration to reference a current `revision_id` that belongs to a different configuration. That’s a possibility we explicitly guard against at the application level, but a trigger could also handle that.