A query, safe against concurrent writes with READ COMMITTED, also safe with SERIALIZABLE isolation level?

Question

I have asked this question with a wonderfully informative answer:
How to have uniqueness constraints for structures across multiple tables?

The answer from Erwin Brandstetter suggests this:

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'
;

I am just learning about CTEs, but the answer says:

It's also safe under concurrent write load with default READ COMMITTED transaction isolation.

I am going to be using this with CockroachDB, and they seem to suggest avoiding READ COMMITTED and using SERIALIZABLE instead.

Can I use this query with SERIALIZABLE, or if not, why not / what must be modified to make it work with SERIALIZABLE. These transaction levels are new to me, I mostly have used PostgreSQL with Ruby on Rails ORMs in the past, so haven't dug this deep into SQL. Just trying to use the default SERIALIZABLE transaction isolation level that CockroachDB recommends, and not sure if/when exactly I can/can't use it, and not sure about this case.

This is as much as I know about READ COMMITTED vs. SERIALIZABLE.

Answer 1

... CockroachDB ... seem to suggest avoiding READ COMMITTED and using SERIALIZABLE instead.

Actually, according to the manual, it's more than a suggestion. CockroachDB uses SERIALIZABLE snapshot isolation, period:

In contrast to most databases, CockroachDB always uses SERIALIZABLE isolation

But that poses no problem for this solution. SERIALIZABLE is only stricter than READ COMMITTED. If anything, you have more leeway, while still being safe against race conditions from concurrent write load. That's according to the SQL standard.
But there is no need, as the solution should be as fast as it gets. The problem with SERIALIZABLE is that it's more expensive. At least that's the case in Postgres.

While being on the topic of "Postgres vs CockroachDB". One suggestion from the manual:

Cockroach Labs recommends that you use multi-column primary keys or the UUID datatype for primary key columns.

Your table object_properties should probably drop the added surrogate id and use PRIMARY KEY (source_id, value_id) instead - also replacing my suggested CONSTRAINT object_properties_uni UNIQUE (source_id, value_id).