How to have uniqueness constraints for structures across multiple tables?

Question

Say I have a schema system something like this:

create table objects {
  uuid id;
  string type;
}

create table object_properties {
  uuid id;
  uuid source_id; // the object which has this property
  string name; // the property name
  uuid value_id; // the property value object
}

// ...and tables for each primitive data type
create table string_properties {
  uuid id;
  uuid source_id; // the object which has this property
  string name; // the property name
  string value; // the property value string
}

I then want to create this object:

{
  type: 'foo',
  bar: {
    type: 'bar',
    baz: {
      type: 'baz',
      slug: 'hello-world'
    }
  }
}

That is:

// objects
id  | type
123 | foo
234 | bar
345 | baz

// object_properties
source_id   | name  | value_id
123         | bar   | 234
234         | baz   | 345

// string_properties
source_id   | name  | value
345         | slug  | hello-world

I want to only create this "object tree" if the tree ending in slug: hello-world doesn't exist. How best can I do that? I can do it easily by first making a query, checking the object exists, and then creating it if not. But that is one query followed by one insert. There is a chance that two processes come in at the same time, both make the query, both succeed, and then both make the insert. How can I prevent that? Note, I am currently having each independent query+insert happen both in a transaction, so each transaction has the query followed by the insert.

Or will the update inside the first transaction be readable "outside" from the second transaction? I am using PostgreSQL / CockroachDB, is this a "read uncommitted" sort of setting?

Answer 1

Working PostgreSQL setup:

CREATE TABLE objects (
  id   uuid PRIMARY KEY DEFAULT gen_random_uuid()
, type text NOT NULL
);

CREATE TABLE object_properties (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid()  -- redundant
, source_id uuid NOT NULL REFERENCES objects    -- the object which has this property
, name      text NOT NULL                       -- the property name  -- redundant
, value_id  uuid NOT NULL REFERENCES objects    -- the property value object
, CONSTRAINT object_properties_uni UNIQUE (source_id, value_id)  -- unique?
);

CREATE TABLE string_properties (
  id        uuid PRIMARY KEY DEFAULT gen_random_uuid()  -- redundant
, source_id uuid NOT NULL REFERENCES objects    -- the object which has this property
, name      text NOT NULL                       -- the property name
, value     text NOT NULL                       -- the property value string
, CONSTRAINT string_properties_uni UNIQUE (value)  -- REQUIRED !!!
);

Proof of concept:

WITH ins_string_properties AS (
   INSERT INTO string_properties (source_id, name, value)
   VALUES (gen_random_uuid(), 'slug', 'hello-world')
   ON CONFLICT DO NOTHING  -- to silence unique violation errors
   RETURNING source_id
   )
, ins_objects AS (
   INSERT INTO objects (id, type)
   SELECT o.id, o.type
   FROM   ins_string_properties isp  -- always 0 or 1 rows
   CROSS  JOIN LATERAL (
      VALUES 
        (isp.source_id    , 'baz')
      , (gen_random_uuid(), 'foo')
      , (gen_random_uuid(), 'bar')
      ) o(id, type)
   RETURNING id, type
   )
INSERT INTO object_properties (source_id, name, value_id)
SELECT io1.id, io2.type, io2.id
FROM   ins_objects io1
JOIN   ins_objects io2 ON io1.type = 'foo' AND io2.type = 'bar'
                       OR io1.type = 'bar' AND io2.type = 'baz'
;

db<>fiddle here

This query only does the minimum work necessary. One INSERT command for each table.

The first core feature is to chain inserts in a single query with CTEs. See:

• Insert data in 3 tables at a time using Postgres

The second core feature is INSERT ... ON CONFLICT DO NOTHING in conjunction with the added UNIQUE constraint object_properties_uni. See:

• Deadlock with multi-row INSERTs despite ON CONFLICT DO NOTHING
• How to use RETURNING with ON CONFLICT in PostgreSQL?

If the first insert to string_properties does not insert a row, the rest does nothing, either. So the attempt to insert a tree with a "slug" that's already there does nothing. If you want an exception instead, drop the ON CONFLICT clause.

An important reason for a single command if there are FOREIGN KEY constraints like in my setup above (REFERENCES objects): Without expensively making the constraint DEFERRABLE or setting it DEFERRED, we can only insert the referencing row "before" the referenced row if done in a single command. Everything virtually happens at the same time this way. Again, that's the case in Postgres - according to standard SQL, though. See:

• SET CONSTRAINTS ALL DEFERRED not working as expected

It's also safe under concurrent write load with default READ COMMITTED transaction isolation. The first transaction to insert the slug wins the race. Concurrent transactions trying to do the same wait till the first one commits (or rolls back) and then either do nothing or proceed in queued order. Just make sure that all transactions use the same query, and don't add any independent commands to the same transaction.

There are many subtleties to this. Note the modifications I made to your (pseudo-code) relational design. It can be adjusted to most every requirement. Can even be wrapped in a server side function taking the JSON object you show. But working out the details goes beyond the scope of a question on dba. That's work for a paid consultant.