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

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.

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 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 prevent a race condition, they just reduce the rate at which race conditions happen.

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, where in another connection also tries to make a revision current with conflicting public names.

OTOH, because all updates and inserts are the result 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?

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.

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?

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.

However, any value used for the name column in the public name table, must be unique across all 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, this would be solved by using a unique constraint on the name column. This is, instead, a many-to-many pattern with revision acting as the association table, and the current_revision_id "collapses" the many-to-many to a virtual one-to-many relationship.

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

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.

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.

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.

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