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
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:
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
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
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
UPDATEs 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
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.