主页 / dba / 问题 / 315123
Accepted
Lance
Asked: 2022-08-02 21:56:29 +0800 CST

如何对跨多个表的结构进行唯一性约束？

  • 772

假设我有一个类似这样的模式系统：

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
}

然后我想创建这个对象：

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

那是：

// 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

slug: hello-world如果以结尾的树不存在，我只想创建这个“对象树” 。我怎样才能做到最好？我可以很容易地做到这一点，首先进行查询，检查对象是否存在，如果不存在则创建它。但这是一个查询，然后是一个插入。有可能两个进程同时进入，都进行查询，都成功，然后都进行插入。我怎样才能防止这种情况？请注意，我目前在事务中都发生了每个独立的查询+插入，因此每个事务都有查询后跟插入。

或者update第一笔交易的内部是否可以从第二笔交易的“外部”读取？我正在使用 PostgreSQL / CockroachDB，这是一种“未提交读取”的设置吗？

postgresql transaction

1 个回答

  1. Best Answer

    工作 PostgreSQL 设置：

    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 !!!
);

    概念证明：

    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<>在这里摆弄

    这个查询只做最少的必要工作。每个表一个INSERT命令。

    第一个核心功能是在单个查询中使用 CTE链接插入。看：

    第二个核心特征是INSERT ... ON CONFLICT DO NOTHING与添加的UNIQUE约束相结合object_properties_uni。看：

    如果第一个 insert tostring_properties没有插入一行，那么其余的也不做任何事情。因此，尝试使用已经存在的“蛞蝓”插入一棵树没有任何作用。如果您想要一个例外，请删除该ON CONFLICT子句。

    如果存在像我上面的设置中的FOREIGN KEY约束（） ，则使用单个命令的一个重要原因REFERENCES objects：无需昂贵的约束DEFERRABLE或设置它，如果在单个命令DEFERRED中完成，我们只能在引用行“之前”插入引用行。几乎所有事情都以这种方式同时发生。同样，Postgres 就是这种情况——不过，根据标准 SQL。看：

    在具有默认事务隔离的并发写入负载下也是安全的。READ COMMITTED插入蛞蝓的第一笔交易赢得比赛。尝试执行相同操作的并发事务等待第一个提交（或回滚），然后要么什么都不做，要么按队列顺序继续。只要确保所有事务都使用相同的查询，并且不要将任何独立的命令添加到同一个事务中。

    这有很多微妙之处。请注意我对您的（伪代码）关系设计所做的修改。它可以根据大多数要求进行调整。甚至可以包装在服务器端函数中，获取您显示的 JSON 对象。但是解决细节超出了关于 dba 的问题的范围。这是付费顾问的工作。

    • 4

相关问题