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 COMMITTEDtransaction isolation.
I am going to be using this with CockroachDB, and they seem to suggest avoiding
READ COMMITTED and using
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.