-- https://www.postgresql.org/docs/current/ddl-partitioning.html
-- zh
-- http://postgres.cn/docs/11/ddl-partitioning.html
-- keyword: further redirect

-- 已有数据分表，因为主表不能有数据，所以需要先备份，创建分表和规则完毕后重新插入
-- 或者用新的表名，之后再分批读取插入

-- 创建主表
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- 创建分表及规则
-- 还可以通过partition by 再次创建sub-partitioning，对插入measurement_y2006m02的数据再次重定向
-- CREATE TABLE measurement_y2006m02 PARTITION OF measurement
--     FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
--     PARTITION BY RANGE (peaktemp);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

-- 创建索引，自动再每个分区上创建索引
CREATE INDEX ON measurement (logdate);

-- Ensure that the enable_partition_pruning configuration parameter is not disabled in postgresql.conf. If it is, queries will not be optimized as desired.

-- test
INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2008-2-1', 1, 1);
INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES (1, '2006-2-1', 1, 1);

---- 维护 ----

-- 删除分表，可以快速删除百万数据，但是需要父表的 ACCESS EXCLUSIVE 锁
DROP TABLE measurement_y2006m02;

-- 更好的处理方式，将分表从主表中分离，以单独的形式存在
-- This allows further operations to be performed on the data before it is dropped. For example, this is often a useful time to back up the data using COPY, pg_dump, or similar tools. It might also be a useful time to aggregate data into smaller formats, perform other data manipulations, or run reports.
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

-- 在新的自盘空间申明，一般是用于把重要数据放在可靠快速磁盘，将日志型等数据放于普通磁盘
-- CREATE TABLE measurement_y2008m02 PARTITION OF measurement
--     FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
--     TABLESPACE fasttablespace;

--  分区表有下列限制：
--
--     没有办法创建跨越所有分区的排除约束，只可能单个约束每个叶子分区。
--
--     虽然在分区表上支持主键，但引用分区表的外键不受支持（但支持从分区表到某个其他表的外键引用）。
--
--     当一个UPDATE导致一行从一个分区移动到另一个分区时，另一个并发的UPDATE或DELETE可能会产生一个串行化错误。假设会话1正在执行一个分区键上的UPDATE，同时一个并发的能看见这个行的会话2执行了对该行的UPDATE或者DELETE操作。在这种情况下，会话2的UPDATE或者DELETE会检测到行的移动，并抛出一个串行化的错误(将总是会返回一个SQLSTATE '40001')。 如果发生这种情况，应用程序可能希望重试该事务。 在没有分区表或没有行移动的通常情况下， 会话2将识别新更新的行并在新行上执行UPDATE/DELETE。
--
--     如果必要，必须在个体分区上定义BEFORE ROW触发器，分区表上不需要。
--
--     不允许在同一个分区树中混杂临时关系和持久关系。因此，如果分区表是持久的，则其分区也必须是持久的，反之亦然。在使用临时关系时，分区数的所有成员都必须来自于同一个会话。

-- 使用继承实现

--  虽然内建的声明式分区适合于大部分常见的用例，但还是有一些场景需要更加灵活的方法。分区可以使用表继承来实现，这能够带来一些声明式分区不支持的特性，例如：
--
--     对声明式分区来说，分区必须具有和分区表正好相同的列集合，而在表继承中，子表可以有父表中没有出现过的额外列。
--
--     表继承允许多继承。
--
--     声明式分区仅支持范围、列表以及哈希分区，而表继承允许数据按照用户的选择来划分（不过注意，如果约束排除不能有效地剪枝子表，查询性能可能会很差）。
--
--     在使用声明式分区时，一些操作比使用表继承时要求更长的持锁时间。例如，向分区表中增加分区或者从分区表移除分区要求在父表上取得一个ACCESS EXCLUSIVE锁，而在常规继承的情况下一个SHARE UPDATE EXCLUSIVE锁就足够了。

--继承实现可以在已有数据中实现吗？
create table measurement3 (
    id int not null,
    name char not null
);
insert into measurement3 (id, name) values (1, 'a');
insert into measurement3 (id, name) values (2, 'b');
insert into measurement3 (id, name) values (3, 'c');
insert into measurement3 (id, name) values (4, 'd');

create table measurement3_y1 (check ( id >= 10 and id < 20 )) inherits (measurement3);
create table measurement3_y2 (check ( id >= 20 and id < 30 )) inherits (measurement3);

create function measurement_insert_trigger() returns trigger
    language plpgsql
as
$$
BEGIN
    IF (NEW.id >= 10 and NEW.id < 20) then
        INSERT INTO measurement3_y1 VALUES (NEW.*);
    ELSIF (NEW.id >= 20 and NEW.id < 30) then
        INSERT INTO measurement3_y2 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'id out of range. Fix the measurement_insert_trigger function!';
    END IF;
    RETURN NULL;
END;
$$;

alter function measurement_insert_trigger() owner to develop;


CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement3
    FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

insert into measurement3 (id, name)
values (100, 'd');