分区和序列递增 [英] Partitioning and Sequence Increment

问题描述

我有一个名为scdr_buz的表，并按月对其进行了分区，我在插入时创建了触发器，该触发器负责upsert，如果不存在则创建表，然后创建upsert。我的序列i_buz_scdr序列的增量为1，但是它的行为是随机添加行而不是以1的增量递增。这是我的触发器代码：

I have table call it scdr_buz and have partitioned it on monthly basis, I have created trigger on insert which take care of upsert and create table if not present then upsert. I have sequence i_buz_scdr sequence with 1 increment but it's behavior while added rows in random not increment of 1. here is code of my trigger:

CREATE OR REPLACE FUNCTION tbl_scdr_buz_insert_trigger() RETURNS TRIGGER AS $$
BEGIN
    EXECUTE 'UPDATE scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' sc SET c_total_calls = sc.c_total_calls + ($1).c_total_calls WHERE (sc.c_prefix_id = ($1).c_prefix_id AND sc.v_prefix_id = ($1).v_prefix_id AND sc.start_time = ($1).start_time)'
    USING NEW;
    EXECUTE 'INSERT INTO scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||'(customer_name, ...) Select ($1).* 
 WHERE NOT EXISTS (SELECT * FROM scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' WHERE (...))'
    USING NEW;
    RETURN NULL;
    EXCEPTION
        WHEN undefined_table THEN
            EXECUTE 'CREATE TABLE IF NOT EXISTS scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' (CHECK ( start_time >= '''|| to_char(NEW.start_time, 'YYYY-MM-01 00:00') ||''' AND start_time < '''|| to_char(NEW.start_time + INTERVAL '1 month', 'YYYY-MM-01 00:00') ||''' )) INHERITS (scdr_buz)';
            EXECUTE 'CREATE INDEX i_buz_scdr_'|| to_char(NEW.start_time, 'YYYY_MM') ||' ON scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' (switch_name, customer_name, client_name_id, vendor_name_id, vendor_connection, c_prefix_id, v_prefix_id, start_time, c_billing_prefix, v_billing_prefix)';
            EXECUTE 'CREATE INDEX i_buz_scdr_starttime_'|| to_char(NEW.start_time, 'YYYY_MM') ||' ON scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' (start_time)';

        EXECUTE 'UPDATE scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' sc SET c_total_calls = sc.c_total_calls + ($1).c_total_calls WHERE (sc.c_prefix_id = ($1).c_prefix_id AND sc.v_prefix_id = ($1).v_prefix_id AND sc.start_time = ($1).start_time)'
        USING NEW;
        EXECUTE 'INSERT INTO scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||'(customer_name, ...) Select ($1).* 
     WHERE NOT EXISTS (SELECT * FROM scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' WHERE (...))'
        USING NEW;
        RETURN NULL;
END
$$
LANGUAGE plpgsql;


CREATE TRIGGER fk_checkTrigger_buz_scdr
BEFORE INSERT ON scdr_buz
FOR EACH ROW
EXECUTE PROCEDURE tbl_scdr_buz_insert_trigger();

推荐答案

可能会增加1，但请记住 any 分区的每个插入都增加一个。另请注意，回滚不会回滚序列。

It's probably incrementing by 1, but keep in mind that each insert to any partition increases by one. Also note that roll-backs do not roll back the sequence.

这篇关于分区和序列递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！