在PostgreSQL分区中创建唯一索引

Question

我有一个叫做cdrs的表：

CREATE TABLE cdrs (
    i_cdr bigint NOT NULL,
    i_cdrs_connection bigint NOT NULL,
    i_call bigint NOT NULL,
    customer_name character varying(156) NOT NULL,
    client_name_id character varying(256) NOT NULL,
    connection_name character varying(156) NOT NULL,
    vendor_name_id character varying(256) NOT NULL,    
    setup_time timestamp with time zone NOT NULL,
    c_result_id bigint NOT NULL,
    v_result_id bigint NOT NULL
    );

现在创建了一个由触发器插入到父表的继承分区，并进行了更新。此功能尝试插入，如果所需分区不存在，则使用索引创建它。我们已经为每个分区上的i_cdrs_connection创建了唯一索引，并在父表上也创建了索引。

CREATE UNIQUE INDEX i_cdrs_connection ON cdrs(i_cdrs_connection)

CREATE OR REPLACE FUNCTION cdrs_insert_trigger() RETURNS TRIGGER AS $$
BEGIN
    EXECUTE 'INSERT INTO cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' SELECT ($1).*'
    USING NEW;
    RETURN NULL;
    EXCEPTION
        WHEN undefined_table THEN
            EXECUTE 'CREATE TABLE IF NOT EXISTS cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (CHECK ( setup_time >= '''|| to_char(NEW.setup_time, 'YYYY-MM-DD 00:00') ||''' AND setup_time < '''|| to_char(NEW.setup_time + INTERVAL '1 day', 'YYYY-MM-DD 00:00') ||''' )) INHERITS (cdrs)';
            EXECUTE 'CREATE UNIQUE INDEX i_cdrs_connection_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_cdrs_connection)';
            EXECUTE 'CREATE INDEX i_cdr_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_cdr)';
            EXECUTE 'CREATE INDEX i_call_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_call)';
            EXECUTE 'CREATE INDEX setup_time_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (setup_time)';

        EXECUTE 'INSERT INTO cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' SELECT ($1).*'
        USING NEW;
        RETURN NULL;
END
$$
LANGUAGE plpgsql;


CREATE TRIGGER fk_checkTrigger_cdrs
BEFORE INSERT ON cdrs
FOR EACH ROW
EXECUTE PROCEDURE cdrs_insert_trigger();

现在，当我尝试在同一分区中插入重复的 i_cdrs_connections 时，它会显示唯一键冲突，但是当我尝试在来自同一分区的其他分区中使用相同的键时，行被添加而没有错误。

总之，在单个分区上的唯一索引可以正常工作，但对于具有多个分区的表，其值不唯一。

我知道 sequence（序列） ，但对于给定的表格无用，因为这将从另一个数据库复制，并且我们必须消除重复插入的机会。

- sharafjaffri

---

请看这里：https://dev59.com/K0fSa4cB1Zd3GeqPAdIn - dezso

这似乎与您刚刚发布的新问题几乎相同。您能否合并它们或删除旧问题？http://stackoverflow.com/questions/12299352/unique-index-over-partition-tables-in-postgresql - Craig Ringer

@CraigRinger 两者是不同的，你提到的那个会要求序列的随机行为。就像一个用户也遇到了几乎相同的问题http://bizzteams.com/forum/postgresql/25727-weird-sequence-increasing-partitioned-table.html，但这里的问题是所有子表上的唯一索引。 - sharafjaffri

Answer 1

这是PostgreSQL的当前行为。唯一索引只针对分区进行，而不是整个表。你有几个选择:

1. 如果可能的话，请按某种方式对表进行分区，使键范围在某些方面是独占的。换句话说，在关键数据上进行分区。这是最简单、最无麻烦的方法。但现在你在对非关键数据进行分区，这是一个问题。

2. 如果这不起作用，你可以将分区值添加到连接的另一侧。此时需要自定义fkey触发器。

3. 如果你确实需要，可以创建一个由触发器维护的所有ID的物化视图，并在其上创建唯一索引。

- Chris Travers

Answer 2

我读到了一篇关于这个主题的文章: http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

作者提出了一个非常好的解决方案。

基本上，解决方案是添加锁（Advisory Locks）并添加触发器，例如：

 CREATE OR REPLACE FUNCTION public.master_id_pkey()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  PERFORM pg_advisory_xact_lock(NEW.id);

  IF count(1) > 1 FROM master WHERE id = NEW.id THEN
     RAISE EXCEPTION 'duplicate key value violates unique constraint "%" ON "%"', 
      TG_NAME, TG_TABLE_NAME 
      USING DETAIL = format('Key (id)=(%s) already exists.', NEW.id);
  END IF;

  RETURN NULL;
END
$function$;

- MaximeF

---

触发器本身执行起来比较慢。尤其是这个触发器的设计非常糟糕。使用“IF count(1) > 1”是一项代价高昂的操作，因为它将解析整个表。相反，在插入之前使用“IF EXISTS”并添加触发器会更好。 - Alex