精华内容
下载资源
问答
  • 如下Function函数可以计算postgre数据库中,分区表的汇总大小。 CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname charac varying, v_tablename character varying) RETURN bigint AS $BODY ...

    如下Function函数可以计算postgre数据库中,分区表的汇总大小。

    CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname charac varying, v_tablename character varying)
        RETURN bigint AS
    $BODY
    DECLARE
        v_calc BIGINT :=0;
        V_total BIGINT :=0;
        v_tbname VARCHAR(200);
        cur_tbname cursor for select schemaname||'.'||partitiontablename as tb from pgpartitions    where schemaname=v_schemaname and tablename=vtablename;
    BEGIN
        OPEN cur_tbname;
        loop
            fetch cur_tbname into v_tbname;
            if not found THEN
                exit;
            end if;
            EXECUTE 'select pg_relation_size('''||v_tbname||''')' into v_calc;
            v_total:= v_total + v_calc;
        end loop;
        CLOSE cur_tbname;
        RETURN v_total;
    end;
    $BODY$
        LANGUAGE plpgsql VOLATILE;

    游标

      游标(cursor)是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录,并赋给主变量,交由主语言进一步处理。

      游标是处理结果集的一种机制吧,它可以定位到结果集中的某一行,多数据进行读写,也可以移动游标定位到你所需要的行中进行操作数据。一般复杂的存储过程,都会有游标的出现,他的用处主要有:

    1. 定位到结果集中的某一行。
    2. 对当前位置的数据进行读写。
    3. 可以对结果集中的数据单独操作,而不是整行执行相同的操作。
    4. 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

     

    优点

      在数据库中,游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 查询语句相关联因为游标由结果集(可以是零条、一条或由相关的选择语句检索出的多条记录)和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时,必须声明一个指向该结果集的游标。如果曾经用C 语言写过对文件进行处理的程序,那么游标就像您打开文件所得到的文件句柄一样,只要文件打开成功,该文件句柄就可代表该文件。对于游标而言,其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集,从而把表中数据以平面文件的形式呈现给程序。我们知道关系数据库管理系统实质是面向集合的,在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式,除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见,游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作,而不是一次对整个结果集进行同一种操作;它还提供对基于游标位置而对表中数据进行删除或更新的能力;而且,正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来,使两个数据处理方式能够进行沟通。

     

    游标的生命周期

      游标的生命周期包含有五个阶段:声明游标、打开游标、读取游标数据、关闭游标、释放游标。

    1. 声明游标:

    DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
         [ FORWARD_ONLY | SCROLL ] 
         [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
         [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
         [ TYPE_WARNING ] 
         FOR select_statement 
         [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

    参数说明:

    • cursor_name:游标名称。
    • Local:作用域为局部,只在定义它的批处理,存储过程或触发器中有效。
    • Global:作用域为全局,由连接执行的任何存储过程或批处理中,都可以引用该游标。
    • [Local | Global]:默认为local。
    • Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字,默认为Dynamic游标。如果Forward_Only和Scroll没有指定,Static、KeySet、Dynamic游标默认为Scroll,Fast_Forward默认为Forward_Only
    • Static:静态游标
    • KeySet:键集游标
    • Dynamic:动态游标,不支持Absolute提取选项
    • Fast_Forward:指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update,就不能指定他啦。
    • Read_Only:不能通过游标对数据进行删改。
    • Scroll_Locks:将行读入游标是,锁定这些行,确保删除或更新一定会成功。如果指定啦Fast_Forward或Static,就不能指定他啦。
    • Optimistic:指定如果行自读入游标以来已得到更新,则通过游标进行的定位更新或定位删除不成功。当将行读入游标时,sqlserver不锁定行,它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改,如果表不行timestamp列,它改用校验和值进行确定。如果已修改改行,则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
    • Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
    • For Update[of column_name ,....] :定义游标中可更新的列。

    2. 声明一个动态游标

    declare orderNum_02_cursor cursor scroll
    for select OrderId from bigorder where orderNum='ZEORD003402'

    3. 打开游标

    --打开游标语法
    open [ Global ] cursor_name | cursor_variable_name
    
    cursor_name:游标名,cursor_variable_name:游标变量名称,该变量引用了一个游标。
    
    --打开游标
    open orderNum_02_cursor

    4. 提取数据

    --提取游标语法
    Fetch
    [ [Next|prior|Frist|Last|Absoute n|Relative n ]
    from ]
    [Global] cursor_name
    [into @variable_name[,....]]
    
    

    参数说明:

    • Frist:结果集的第一行
    • Prior:当前位置的上一行
    • Next:当前位置的下一行
    • Last:最后一行
    • Absoute n:从游标的第一行开始数,第n行。
    • Relative n:从当前位置数,第n行。
    • Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。
    --提取数据
    fetch first from orderNum_02_cursor
    fetch relative 3 from orderNum_02_cursor
    fetch next from orderNum_02_cursor
    fetch absolute 4 from orderNum_02_cursor
    fetch next from orderNum_02_cursor
    fetch last from orderNum_02_cursor 
    fetch prior from orderNum_02_cursor
    select * from bigorder where orderNum='ZEORD003402'
    
    --提取数据赋值给变量
    declare @OrderId int
    fetch absolute 3 from orderNum_02_cursor into @OrderId
    select @OrderId as id
    select * from bigorder where orderNum='ZEORD003402'

    通过检测全局变量@@Fetch_Status的值,获得提取状态信息,该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后,@@Fetch_Status可能出现3种值:

    • 0,Fetch语句成功。
    • -1:Fetch语句失败或行不在结果集中。
    • -2:提取的行不存在。

    这个状态值可以帮你判断提取数据的成功与否。

    declare @OrderId int
    fetch absolute 3 from orderNum_02_cursor into @OrderId
    while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
     begin
       select @OrderId as id
       fetch  next from orderNum_02_cursor into @OrderId  --移动游标
     end

    5.利用游标更新删除数据

    --游标修改当前数据语法
    Update 基表名 Set 列名=值[,...] Where Current of 游标名
    --游标删除当前数据语法
    Delete 基表名  Where Current of 游标名

    游标更新删除当前数据

    --1.声明游标
    declare orderNum_03_cursor cursor scroll
    for select OrderId ,userId from bigorder where orderNum='ZEORD003402'
    --2.打开游标
    open orderNum_03_cursor
    --3.声明游标提取数据所要存放的变量
    declare @OrderId int ,@userId varchar(15)
    --4.定位游标到哪一行
    fetch First from orderNum_03_cursor into @OrderId,@userId  --into的变量数量必须与游标查询结果集的列数相同
    while @@fetch_status=0  --提取成功,进行下一条数据的提取操作 
     begin
       if @OrderId=122182
         begin
         Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改当前行
         end
       if @OrderId=154074
          begin
          Delete bigorder Where Current of  orderNum_03_cursor  --删除当前行
          end
       fetch next from orderNum_03_cursor into @OrderId ,@userId  --移动游标
     end

    6. 关闭游标

      游标打开后,服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集,同时使用游标也会对某些数据进行封锁。所以游标一旦用过,应及时关闭,避免服务器资源浪费

    --关闭游标语法
    close [ Global ] cursor_name | cursor_variable_name
    --关闭游标
    close orderNum_03_cursor

    7. 删除游标

      删除游标,释放资源

    --释放游标语法
    deallocate  [ Global ] cursor_name | cursor_variable_name
    --释放游标
    deallocate orderNum_03_cursor

    使用实例:

    USE Test_DB;
    
    DECLARE @jid CHAR(5)
    DECLARE @pic NVARCHAR(64)
    DECLARE My_Cursor CURSOR --定义游标
    FOR (SELECT jid FROM journal WHERE isall in(1,2)) --查出需要的集合放到游标中
    OPEN My_Cursor; --打开游标
    FETCH NEXT FROM My_Cursor INTO @jid; --读取第一行数据
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @pic=(SELECT TOP 1 smallpic FROM journalissue WHERE jid=@jid and (smallpic != '' and smallpic is not null) ORDER BY issueyear DESC,issueno DESC);
            PRINT (@jid +'    '+ @pic);
            IF(@jid != '' and @jid is not null and @pic != '' and @pic is not null)
            BEGIN
                UPDATE journal SET pic=@pic WHERE jid=@jid;
            END        
            FETCH NEXT FROM My_Cursor INTO @jid; --读取下一行数据
        END
    CLOSE My_Cursor; --关闭游标
    DEALLOCATE My_Cursor; --释放游标
    GO

     

     

     

    展开全文
  • postgre 创建分区

    2020-06-14 11:37:04
    PG 创建分区表 1.建表 CREATE TABLE "exchange_detail" ( "id" int8 primary key, "exchange_type" int2, "exchange_text" varchar(255), "detail_user" int8, "create_time" timestamp(6) ); COMMENT ON ...

    PG 创建分区表

    1.建表

    CREATE TABLE  "exchange_detail" (
      "id" int8 primary key,
      "exchange_type" int2,
      "exchange_text" varchar(255),
      "detail_user" int8,
      "create_time" timestamp(6)
    );
    
    COMMENT ON COLUMN "public"."exchange_detail"."id" IS '置换记录id';
    COMMENT ON COLUMN "public"."exchange_detail"."exchange_type" IS '置换类型';
    COMMENT ON COLUMN "public"."exchange_detail"."exchange_text" IS '置换描述';
    COMMENT ON COLUMN "public"."exchange_detail"."detail_user" IS '置换用户';
    COMMENT ON COLUMN "public"."exchange_detail"."create_time" IS '创建时间'; 
    

    2.创建分区表 直接继承exchange_detail表就好了。一般的子表不需要加字段。可以建立多个分区

     create table exchange_detail01
    (CHECK (extract(month from  create_time) = 1))
    INHERITS (exchange_detail);
    

    2.1 给字表增加主键

    ALTER TABLE exchange_detail01 ADD PRIMARY KEY (id);
    

    3.给子表创建索引 这个索引一般都是创建规则的字段

    create index exchange_detail01_create_time ON exchange_detail01(create_time);
    

    4.创建触发器函数

    CREATE OR REPLACE FUNCTION  exchange_detail_trigger() RETURNS  trigger AS $$
    BEGIN
     
    IF (
    extract(month from  NEW .create_time) = 1 
    ) THEN
    INSERT INTO exchange_detail01
    VALUES
    (NEW .*) ;
    ELSEIF (
    extract(month from  NEW .create_time) = 2
    ) THEN
    INSERT INTO exchange_detail02
    VALUES
    (NEW .*) ;
    ELSEIF (
    extract(month from  NEW .create_time) = 3
    ) THEN
    INSERT INTO exchange_detail03
    VALUES
    (NEW .*) ;
    ELSEIF (
    extract(month from  NEW .create_time) = 4
    ) THEN
    INSERT INTO exchange_detail04
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 5
    )THEN
    INSERT INTO exchange_detail05
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 6
    )THEN
    INSERT INTO exchange_detail06
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 7
    )THEN
    INSERT INTO exchange_detail07
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 8
    )THEN
    INSERT INTO exchange_detail08
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 9
    )THEN
    INSERT INTO exchange_detail09
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 10
    )THEN
    INSERT INTO exchange_detail10
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 11
    )THEN
    INSERT INTO exchange_detail11
    VALUES
    (NEW .*) ;
    ELSEIF(
    extract(month from  NEW .create_time) = 12
    )THEN
    INSERT INTO exchange_detail12
    VALUES
    (NEW .*) ;
    ELSE
    RAISE EXCEPTION 'Date out of range!' ;
    END
    IF ; RETURN NULL ;
    END ; $$ LANGUAGE plpgsql;
    

    5.创建触发器

    CREATE TRIGGER exchange_detail_trigger BEFORE INSERT ON exchange_detail
    FOR EACH ROW
    EXECUTE PROCEDURE exchange_detail_trigger();
    
    展开全文
  • postgre 表继承 分区

    2014-06-16 09:50:00
    范围分区: 表被一个或者多个键字字段分区成"范围",在这些范围之间没有重叠的数值分布到不同的分区里。比如,我们可以为特定的商业对象根据数据范围分区,或者根据标识符范围分区。 2). 列表分区: 表是通过明确地...

    一、表的继承:

        这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧。
        1. 第一个继承表:
        CREATE TABLE cities (   --父表
            name        text,
            population float,
            altitude     int
        );
        CREATE TABLE capitals ( --子表
            state      char(2)
        ) INHERITS (cities);
        capitals表继承自cities表的所有属性。在PostgreSQL里,一个表可以从零个或多个其它表中继承属性,而且一个查询既可以引用父表中的所有行,也可以引用父表的所有行加上其所有子表的行,其中后者是缺省行为。
        MyTest=# INSERT INTO cities values('Las Vegas', 1.53, 2174);  --插入父表
        INSERT 0 1
        MyTest=# INSERT INTO cities values('Mariposa',3.30,1953);     --插入父表
        INSERT 0 1
        MyTest=# INSERT INTO capitals values('Madison',4.34,845,'WI');--插入子表
        INSERT 0 1
        MyTest=# SELECT name, altitude FROM cities WHERE altitude > 500; --父表和子表的数据均被取出。
           name     | altitude
        -----------+----------
         Las Vegas |     2174
         Mariposa   |     1953
         Madison    |      845
        (3 rows)
        
        MyTest=# SELECT name, altitude FROM capitals WHERE altitude > 500; --只有子表的数据被取出。
          name   | altitude
        ---------+----------
         Madison |      845
        (1 row)

        如果希望只从父表中提取数据,则需要在SQL中加入ONLY关键字,如:
        MyTest=# SELECT name,altitude FROM ONLY cities WHERE altitude > 500;
           name     | altitude
        -----------+----------
         Las Vegas |     2174
         Mariposa   |     1953
        (2 rows)
        上例中cities前面的"ONLY"关键字表示该查询应该只对cities进行查找而不包括继承级别低于cities的表。许多我们已经讨论过的命令--SELECT,UPDATE和DELETE--支持这个"ONLY"符号。
        在执行整表数据删除时,如果直接truncate父表,此时父表和其所有子表的数据均被删除,如果只是truncate子表,那么其父表的数据将不会变化,只是子表中的数据被清空。
        MyTest=# TRUNCATE TABLE cities;  --父表和子表的数据均被删除。
        TRUNCATE TABLE
        MyTest=# SELECT * FROM capitals;
         name | population | altitude | state
        ------+------------+----------+-------
        (0 rows)
        
        2. 确定数据来源:
        有时候你可能想知道某条记录来自哪个表。在每个表里我们都有一个系统隐含字段tableoid,它可以告诉你表的来源:
        MyTest=# SELECT tableoid, name, altitude FROM cities WHERE altitude > 500;
         tableoid |   name    | altitude
        ----------+-----------+----------
            16532 | Las Vegas |     2174
            16532 | Mariposa  |     1953
            16538 | Madison   |      845
        (3 rows)
        以上的结果只是给出了tableoid,仅仅通过该值,我们还是无法看出实际的表名。要完成此操作,我们就需要和系统表pg_class进行关联,以通过tableoid字段从该表中提取实际的表名,见以下查询:
        MyTest=# SELECT p.relname, c.name, c.altitude FROM cities c,pg_class p WHERE c.altitude > 500 and c.tableoid = p.oid;
         relname  |   name    | altitude
        ----------+-----------+----------
         cities    | Las Vegas |     2174
         cities    | Mariposa   |     1953
         capitals | Madison    |      845
        (3 rows)
        
        3. 数据插入的注意事项:
        继承并不自动从INSERT或者COPY中向继承级别中的其它表填充数据。在我们的例子里,下面的INSERT语句不会成功:
        INSERT INTO cities (name, population, altitude, state) VALUES ('New York', NULL, NULL, 'NY');
        我们可能希望数据被传递到capitals表里面去,但是这是不会发生的:INSERT总是插入明确声明的那个表。
        
        4. 多表继承:
        一个表可以从多个父表继承,这种情况下它拥有父表们的字段的总和。子表中任意定义的字段也会加入其中。如果同一个字段名出现在多个父表中,或者同时出现在父表和子表的定义里,那么这些字段就会被"融合",这样在子表里面就只有一个这样的字段。要想融合,字段必须是相同的数据类型,否则就会抛出一个错误。融合的字段将会拥有它所继承的字段的所有约束。
        CREATE TABLE parent1 (FirstCol integer);
        CREATE TABLE parent2 (FirstCol integer, SecondCol varchar(20));
        CREATE TABLE parent3 (FirstCol varchar(200)); 
        --子表child1将同时继承自parent1和parent2表,而这两个父表中均包含integer类型的FirstCol字段,因此child1可以创建成功。
        CREATE TABLE child1 (MyCol timestamp) INHERITS (parent1,parent2);
        --子表child2将不会创建成功,因为其两个父表中均包含FirstCol字段,但是它们的类型不相同。
        CREATE TABLE child2 (MyCol timestamp) INHERITS (parent1,parent3);
        --子表child3同样不会创建成功,因为它和其父表均包含FirstCol字段,但是它们的类型不相同。
        CREATE TABLE child3 (FirstCol varchar(20)) INHERITS(parent1);

        5. 继承和权限:
        表访问权限并不会自动继承。因此,一个试图访问父表的用户还必须具有访问它的所有子表的权限,或者使用ONLY关键字只从父表中提取数据。在向现有的继承层次添加新的子表的时候,请注意给它赋予所有权限。     
        继承特性的一个严重的局限性是索引(包括唯一约束)和外键约束只施用于单个表,而不包括它们的继承的子表。这一点不管对引用表还是被引用表都是事实,因此在上面的例子里,如果我们声明cities.name为UNIQUE或者是一个PRIMARY KEY,那么也不会阻止capitals表拥有重复了名字的cities数据行。 并且这些重复的行缺省时在查询cities表的时候会显示出来。实际上,缺省时capitals将完全没有唯一约束,因此可能包含带有同名的多个行。你应该给capitals增加唯一约束,但是这样做也不会避免与cities的重复。类似,如果我们声明cities.name REFERENCES某些其它的表,这个约束不会自动广播到capitals。在这种条件下,你可以通过手工给capitals 增加同样的REFERENCES约束来做到这点。
        
    二、分区表:

        1. 概述分区表:
        分区的意思是把逻辑上的一个大表分割成物理上的几块儿,分区可以提供若干好处:
        1). 某些类型的查询性能可以得到极大提升。
        2). 更新的性能也可以得到提升,因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里,那么在索引上的读和写都会产生更多的磁盘访问。
        3). 批量删除可以用简单地删除某个分区来实现。
        4). 将很少用的数据可以移动到便宜的、慢一些地存储介质上。 
        假设当前的数据库并不支持分区表,而我们的应用所需处理的数据量也非常大,对于这种应用场景,我们不得不人为的将该大表按照一定的规则,手工拆分成多个小表,让每个小表包含不同区间的数据。这样一来,我们就必须在数据插入、更新、删除和查询之前,先计算本次的指令需要操作的小表。对于有些查询而言,由于查询区间可能会跨越多个小表,这样我们又不得不将多个小表的查询结果进行union操作,以合并来自多个表的数据,并最终形成一个结果集返回给客户端。可见,如果我们正在使用的数据库不支持分区表,那么在适合其应用的场景下,我们就需要做很多额外的编程工作以弥补这一缺失。然而需要说明的是,尽管功能可以勉强应付,但是性能却和分区表无法相提并论。
        目前PostgreSQL支持的分区形式主要为以下两种:
        1). 范围分区: 表被一个或者多个键字字段分区成"范围",在这些范围之间没有重叠的数值分布到不同的分区里。比如,我们可以为特定的商业对象根据数据范围分区,或者根据标识符范围分区。
        2). 列表分区: 表是通过明确地列出每个分区里应该出现那些键字值实现的。 

        2. 实现分区:
        1). 创建"主表",所有分区都从它继承。
        CREATE TABLE measurement (            --主表
            city_id      int    NOT NULL,
            logdate     date  NOT NULL,
            peaktemp int,
        );    
        2). 创建几个"子"表,每个都从主表上继承。通常,这些"子"表将不会再增加任何字段。我们将把子表称作分区,尽管它们就是普通的PostgreSQL表。
        CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement);
        CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement);
        ...
        CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement);
        CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement);
        CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
        上面创建的子表,均已年、月的形式进行范围划分,不同年月的数据将归属到不同的子表内。这样的实现方式对于清空分区数据而言将极为方便和高效,即直接执行DROP TABLE语句删除相应的子表,之后在根据实际的应用考虑是否重建该子表(分区)。相比于直接DROP子表,PostgreSQL还提供了另外一种更为方便的方式来管理子表:
        ALTER TABLE measurement_yy06mm01 NO INHERIT measurement;
        和直接DROP相比,该方式仅仅是使子表脱离了原有的主表,而存储在子表中的数据仍然可以得到访问,因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说,就可以在此时对该表进行必要的维护操作,如数据清理、归档等,在完成诸多例行性的操作之后,就可以考虑是直接删除该表(DROP TABLE),还是先清空该表的数据(TRUNCATE TABLE),之后再让该表重新继承主表,如:
        ALTER TABLE measurement_yy06mm01 INHERIT measurement;
        3). 给分区表增加约束,定义每个分区允许的健值。同时需要注意的是,定义的约束要确保在不同的分区里不会有相同的键值。因此,我们需要将上面"子"表的定义修改为以下形式:
        CREATE TABLE measurement_yy04mm02 (
            CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')
        ) INHERITS (measurement);
        CREATE TABLE measurement_yy04mm03 (
            CHECK (logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01')
        ) INHERITS (measurement);
        ...
        CREATE TABLE measurement_yy05mm11 (
            CHECK (logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01')
        ) INHERITS (measurement);
        CREATE TABLE measurement_yy05mm12 (
            CHECK (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01')
        ) INHERITS (measurement);
        CREATE TABLE measurement_yy06mm01 (
            CHECK (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01')
        ) INHERITS (measurement);    
        4). 尽可能基于键值创建索引。如果需要,我们也同样可以为子表中的其它字段创建索引。
        CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);
        CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate);
        ...
        CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate);
        CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate);
        CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);    
        5). 定义一个规则或者触发器,把对主表的修改重定向到适当的分区表。
        如果数据只进入最新的分区,我们可以设置一个非常简单的规则来插入数据。我们必须每个月都重新定义这个规则,即修改重定向插入的子表名,这样它总是指向当前分区。
        CREATE OR REPLACE RULE measurement_current_partition AS
        ON INSERT TO measurement
        DO INSTEAD
        INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);
        其中NEW是关键字,表示新数据字段的集合。这里可以通过点(.)操作符来获取集合中的每一个字段。
        我们可能想插入数据并且想让服务器自动定位应该向哪个分区插入数据。我们可以用像下面这样的更复杂的规则集来实现这个目标。
        CREATE RULE measurement_insert_yy04mm02 AS
        ON INSERT TO measurement WHERE (logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01')
        DO INSTEAD
        INSERT INTO measurement_yy04mm02 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);
        ...
        CREATE RULE measurement_insert_yy05mm12 AS
        ON INSERT TO measurement WHERE (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01')
        DO INSTEAD
        INSERT INTO measurement_yy05mm12 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);
        CREATE RULE measurement_insert_yy06mm01 AS
        ON INSERT TO measurement WHERE (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01')
        DO INSTEAD
        INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);    
        请注意每个规则里面的WHERE子句正好匹配其分区的CHECK约束。
        可以看出,一个复杂的分区方案可能要求相当多的DDL。在上面的例子里我们需要每个月创建一次新分区,因此写一个脚本自动生成需要的DDL是明智的。除此之外,我们还不难推断出,分区表对于新数据的批量插入操作有一定的抑制,这一点在Oracle中也同样如此。  
        除了上面介绍的通过Rule的方式重定向主表的数据到各个子表,我们还可以通过触发器的方式来完成此操作,相比于基于Rule的重定向方法,基于触发器的方式可能会带来更好的插入效率,特别是针对非批量插入的情况。然而对于批量插入而言,由于Rule的额外开销是基于表的,而不是基于行的,因此效果会好于触发器方式。另一个需要注意的是,copy操作将会忽略Rules,如果我们想要通过COPY方法来插入数据,你只能将数据直接copy到正确的子表,而不是主表。这种限制对于触发器来说是不会造成任何问题的。基于Rule的重定向方式还存在另外一个问题,就是当插入的数据不在任何子表的约束中时,PostgreSQL也不会报错,而是将数据直接保留在主表中。
        6). 添加新分区:
        这里将介绍两种添加新分区的方式,第一种方法简单且直观,我们只是创建新的子表,同时为其定义新的检查约束,如:
        CREATE TABLE measurement_y2008m02 (
            CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
        ) INHERITS (measurement);
        第二种方法的创建步骤相对繁琐,但更为灵活和实用。见以下四步:
        /* 创建一个独立的数据表(measurement_y2008m02),该表在创建时以将来的主表(measurement)为模板,包含模板表的缺省值(DEFAULTS)和一致性约束(CONSTRAINTS)。*/
        CREATE TABLE measurement_y2008m02
            (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
        /* 为该表创建未来作为子表时需要使用的检查约束。*/
        ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
            CHECK (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01');
        /* 导入数据到该表。下面只是给出一种导入数据的方式作为例子。在导入数据之后,如有可能,还可以做进一步的数据处理,如数据转换、过滤等。*/
        \copy measurement_y2008m02 from 'measurement_y2008m02'
        /* 在适当的时候,或者说在需要的时候,让该表继承主表。*/
        ALTER TABLE measurement_y2008m02 INHERIT measurement;
        7). 确保postgresql.conf里的配置参数constraint_exclusion是打开的。没有这个参数,查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。
        /> pwd
        /opt/PostgreSQL/9.1/data
        /> cat postgresql.conf | grep "constraint_exclusion"
        constraint_exclusion = partition        # on, off, or partition

        3. 分区和约束排除:
        约束排除(Constraint exclusion)是一种查询优化技巧,它改进了用上面方法定义的表分区的性能。比如:
        SET constraint_exclusion = on;
        SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
        如果没有约束排除,上面的查询会扫描measurement表中的每一个分区。打开了约束排除之后,规划器将检查每个分区的约束然后再视图证明该分区不需要被扫描,因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个,它就把该分区从查询规划里排除出去。
        你可以使用EXPLAIN命令显示一个规划在constraint_exclusion打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是:    
        SET constraint_exclusion = off;
        EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';    
                                                  QUERY PLAN
        -----------------------------------------------------------------------------------------------
         Aggregate  (cost=158.66..158.68 rows=1 width=0)
           ->  Append  (cost=0.00..151.88 rows=2715 width=0)
                 ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
                       Filter: (logdate >= '2006-01-01'::date)
                 ->  Seq Scan on measurement_yy04mm02 measurement  (cost=0.00..30.38 rows=543 width=0)
                       Filter: (logdate >= '2006-01-01'::date)
                 ->  Seq Scan on measurement_yy04mm03 measurement  (cost=0.00..30.38 rows=543 width=0)
                       Filter: (logdate >= '2006-01-01'::date)
        ...
                 ->  Seq Scan on measurement_yy05mm12 measurement  (cost=0.00..30.38 rows=543 width=0)
                       Filter: (logdate >= '2006-01-01'::date)
                 ->  Seq Scan on measurement_yy06mm01 measurement  (cost=0.00..30.38 rows=543 width=0)
                       Filter: (logdate >= '2006-01-01'::date)


        从上面的查询计划中可以看出,PostgreSQL扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划:
        SET constraint_exclusion = on;
        EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';    
                                                  QUERY PLAN
        -----------------------------------------------------------------------------------------------
         Aggregate  (cost=63.47..63.48 rows=1 width=0)
           ->  Append  (cost=0.00..60.75 rows=1086 width=0)
                 ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
                       Filter: (logdate >= '2006-01-01'::date)
                 ->  Seq Scan on measurement_yy06mm01 measurement  (cost=0.00..30.38 rows=543 width=0)
                       Filter: (logdate >= '2006-01-01'::date)
        请注意,约束排除只由CHECK约束驱动,而不会由索引驱动。
        目前版本的PostgreSQL中该配置的缺省值是partition,该值是介于on和off之间的一种行为方式,即规划器只会将约束排除应用于基于分区表的查询,而on设置则会为所有查询都进行约束排除,那么对于普通数据表而言,也将不得不承担由该机制而产生的额外开销。
        
        约束排除在使用时有以下几点注意事项:
        1). 约束排除只是在查询的WHERE子句包含约束的时候才生效。一个参数化的查询不会被优化,因为在运行时规划器不知道该参数会选择哪个分区。因此像CURRENT_DATE这样的函数必须避免。把分区键值和另外一个表的字段连接起来也不会得到优化。
        2). 在CHECK约束里面要避免跨数据类型的比较,因为目前规划器会无法证明这样的条件为假。比如,下面的约束会在x是整数字段的时候可用,但是在x是一个bigint的时候不能用:
        CHECK (x = 1)
        对于bigint字段,我们必须使用类似下面这样的约束:
        CHECK (x = 1::bigint)
        这个问题并不仅仅局限于bigint数据类型,它可能会发生在任何约束的缺省数据类型与其比较的字段的数据类型不匹配的场合。在提交的查询里的跨数据类型的比较通常是OK的,只是不能在CHECK条件里。
        3). 在主表上的UPDATE和DELETE命令并不执行约束排除。
        4). 在规划器进行约束排除时,主表上的所有分区的所有约束都将会被检查,因此,大量的分区会显著增加查询规划的时间。
        5). 在执行ANALYZE语句时,要为每一个分区都执行该命令,而不是仅仅对主表执行该命令。

    转载于:https://my.oschina.net/stonezing/blog/280073

    展开全文
  • Pg库建分区表(pg9.6之后的插件pg_pathman可以建分区,比下面两种方法方便很多) 下面是分别介绍使用触发器和Ruler建分区表的步骤: (  CONSTRAINT table_y2013m01 the_date_check

    Pg库建分区表pg9.6后的插件pg_pathman可以建分区,比下面两种方法方便很多)

    下面是分别介绍使用触发器和Ruler建分区表的步骤:

    一、使用触发器

    1、建分区表(默认父表已经建了):

    假设按月分区,按the_date字段分区

    CREATE TABLE table_y2013m01

    (

     CONSTRAINT table_y2013m01 the_date_checkCHECK(the_date >= '2013-01-01' AND the_date < '2013-02-01')(类型需要转的时候用cast

    )

    INHERITS (父表)(INHERITS相当于继承

    WITH (

     OIDS=FALSE

    );


    需要多个分区表,就写多个create语句。

    2、写函数:

    -- Function:func_funcname()

     

    -- DROP FUNCTION func_funcname();

     

    CREATE OR REPLACE FUNCTION func_funcname()

     RETURNS trigger AS

    $BODY$

     BEGIN

    IF ( NEW.the_date < DATE '20130201')THEN

    INSERT INTO table_2013m01 VALUES(NEW.*);

    …………………………(这里如果建多个分区表,就多个elsif

    ELSIF ( NEW.the_date  >= DATE '20161201' AND

    NEW.the_date  <  DATE '20170101') THEN

    INSERT INTO table_y2016m12 VALUES(NEW.*);

    ELSE

                     RAISE EXCEPTION 'Date out of range. Fix the function!' ;

    END IF;

           RETURN NULL;

     END;

     $BODY$

     LANGUAGE plpgsql VOLATILE

     COST 100;

    ALTER FUNCTION func_funcname()

     OWNER TO pierdata;

    3、需要触发器

    CREATE TRIGGER 自己取名

     BEFORE INSERT

     ON 父表

     FOR EACH ROW

     EXECUTE PROCEDURE func_funcname();

     

    新插入数据时触发,即inset才会触发。

     

    二、使用Ruler

    1、建父表:

    CREATE TABLE ckf_test (

    student_id integer,

    name varchar(32),

    score integer

    );


    2、建分区表

    CREATE TABLE ckf_test_p1

    (CHECK(score >= 60 )) INHERITS (ckf_test) ;

    CREATE TABLE ckf_test_p2

    (CHECK(score < 60)) INHERITS(ckf_test) ;


    3、建Ruler

    CREATE OR REPLACE RULE insert_ckf_test_p1

    AS ON INSERT TO ckf_test

           WHERE score >= 60

           DO INSTEAD

           INSERT INTO ckf_test_p1VALUES(NEW.*);

     

    CREATE OR REPLACE RULE insert_ckf_test_p2

    AS ON INSERT TO ckf_test

           WHERE score < 60

           DO INSTEAD

           INSERT INTO ckf_test_p2VALUES(NEW.*);

     

    不需要函数了~~~~

     

    测试一下:

    INSERT INTO ckf_test (name,score) VALUES('Jim',77);

    INSERT INTO ckf_test (name,score) VALUES('Frank',56);

    INSERT INTO ckf_test (name,score) VALUES('Bean',88);

    INSERT INTO ckf_test (name,score) VALUES('John',47);

    INSERT INTO ckf_test (name,score) VALUES('Albert','87');

    INSERT INTO ckf_test (name,score) VALUES('Joey','60');

    展开全文
  • postgre的索引

    千次阅读 2008-08-05 16:02:00
    postgre一共支持四种索引,btree,hash,gist,gin其中gist是GiST stands for Generalized Search Tree泛型搜索树,gin是GIN stands for Generalized Inverted Index需要pair的出现。这里主要讨论btree和hash。在...
  • postgre--源码安装

    2020-04-13 14:49:41
    打开官网,选择源码分区,选择要下载的版本,这里我要下载10的版本,所以选这个: postgre源码下载 在linux中输入: [root@node1 ~]# wget --no-check-certificate ...
  • postgre+timescaledb笔记

    2020-05-25 18:22:31
    1.postgre教程链接:参考链接 2.postgre时序数据库的应用场景及使用:参考链接 3.PostgreSQL+TimescaleDB安装详解(Windows):参考链接 注意:TimescaleDB在安装时,安装文件别放在C盘,可以放在桌面,然后用管理员...
  • 使用postgreSQL做分区

    2020-07-22 21:29:45
    首先说一下为什么使用postgre来做分区表,最大的原因是使用postgre分区表,对于项目的代码是没有任何侵入的,不需要改动任何代码。下面是创建分区表的过程。 创建student父表 CREATE TABLE "public"."student" ...
  • 转载 postgre调优

    2010-12-10 09:23:29
    参照官方手册和自己在pg上做的实验,记录下pg(FREEBSD)的参数调优过程: ... 优化db,从表结构,sql语句,分表,分区,分库,物理设备,网络设备....进行优化,从量变到质变 表结构方面: 从http://www.p...
  • oracle中支持多种分区类型的组合,组合分区是range、hash、list分区的相互组合,但不允许hash分区作为 Top level。 pg中同样也支持类似的组合分区,PG支持非常灵活的分区布局,支持任意层级的分区,支持每个分区的...
  • 如果安装成服务的话,需要你的机器上有NTFS分区(安装成服务就可以做为系统的一个服务,做为系统的一个后台应用进程执行比较方便)否则安装不能成功,会报错的,如果你机器上没有NTFS分区,可以参考下面的步骤安装...
  • postgre与mysql区别

    千次阅读 2018-07-11 20:50:00
    PostgreSQL 通过继承支持分区表, 阿里RDS PostgreSQL支持分区表语法 例子 http: //www.postgresql.org/docs/9.5/static/ddl-partitioning.html https: //yq.aliyun.com/articles/113 物化视图 PostgreSQL ...
  • PostgreSQL 11 新特性之哈希分区

    千次阅读 2019-01-07 09:52:38
    本文介绍 PostgreSQL 11 新增的分区类型:哈希分区(hash partitioning)。
  • 实战 PostgreSQL 分区表:分区修改

    千次阅读 2020-03-09 22:22:20
    在 实战 PostgreSQL 分区表 中并没有涉及到如何修改分区表的问题。这篇内容将聚焦如何进行分区表的修改。 我们使用上一篇 实战 PostgreSQL 分区表 中的 Order 数据的场景,关于如何初始化这部分数据,请参考 实战 ...
  • 配置postgre数据库服务器

    千次阅读 2008-09-20 10:59:00
     安装数据库就不用说了(我用的是8.2版本),有一点要注意的就是安装的磁盘分区必须是NTFS格式的。装好之后,选择pgAdmin III启动服务控制台,里面有一个Database Server,数据库服务器,里面有四个选项,数据库,...
  • PostgreSQL postgre sql 的一些特性

    千次阅读 2017-07-24 10:02:00
    最近通读了一遍postgre的手册,忽然觉得postgresql 确实在交互友好性,方便性,功能性等都要强过mysql太多,至于为什么没有像mysql一样遍布全球的火起来,或许是因为mysql之前的推广做得好吧,或许mysql更原始所以...
  • 文章目录1)创建自增序列seq2)创建分区表主表3)创建分区表子表4)分区表数据插入5)分区表查询 1)创建自增序列seq CREATE SEQUENCE if not exists public.test_id_seq INCREMENT 1 START 5 MINVALUE 1 ...
  • PostgreSQL 创建表分区

    2020-09-11 08:08:42
    在pg里表分区是通过表继承来实现的,一般都是建立一个主表,里面是空,然后每个分区都去继承它。
  • 在postgresql10之后,引入了内置分区表,用户不需要先在父表上定义insert,update,delete触发器,对父表的DML操作会自动路由到相应分区,相比传统分区表大幅度降低了维护成本,目前内置分区表仅支持范围分区和列表...
  • postgresql分区

    2019-11-27 11:55:08
    #postgresql分区表重要参数配置 constraint_exclusion = partition enable_partitionwise_aggregate = on #(分区级别的聚合:PostgreSQL 11附带了一个名为enable_partitionwise_aggregate的新选项,可以打开该选项以...
  • PostgreSql分区操作

    2020-10-29 14:34:09
    数据库表分区的优点有很多,也有很多文章有所介绍,本文就不赘述了 本文主要是提供数据库分表的操作步骤(也供自己将来需要时使用) 1.创建主表 通过指定PARTITION BY子句把measurement表创建为分区表,可选方法有...
  • 关于PosrgreSQL分区表的应用实例~
  • 但只能在创建时决定是否为分区表,并决定分区条件字段,普通表创建后,不能在修改为分区表。Note:通过其他方法也可转化为分区表。和其他数据库一样,分区表作为一个DB的特性,优点自不用说,物理分散,逻辑统一。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 858
精华内容 343
关键字:

postgre分区