我有一个名为 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 时,它会显示唯一键冲突,但是当尝试在从相同分区行继承的其他分区中添加相同的键时,不会出现错误。
总而言之,单个分区上的唯一索引工作正常,但在具有多个分区的表上值不是唯一的。
我了解序列,但对于给定的表使用较少,因为这将从另一个数据库复制,我们必须消除重复插入的机会。