설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 94건, 최근 0 건
   

파티션 테이블 실습

글쓴이 : PostgresDBA 날짜 : 2013-08-06 (화) 09:39 조회 : 12442
요약부터 하자면,
PostgreSQL의 파티셔닝 기능은 오라클과 비교하면 아직 많이 미숙해 보입니다.
파티션을 구성하는 멤버(?) 테이블을 일일이 만들어주어야 하며, INSERT 시에 인위적으로 데이터가 옳바른 테이블 들어가도록  트리거를 생성해주어야 합니다.
또한 인덱스를 추가한다면 매뉴얼하게 멤버(?) 테이블들에 대해 일일이 만들어주어야 합니다.
(그래서 당연히 PostgreSQL 에서는 global index 개념이 없습니다. 전부 local 인덱스죠)

테스트목적이라서,
아래는 UPDATE트리거는 제외하고 최대한 단순하게 INSERT 트리거만 구현했습니다.
(실제 운영에 구성하신다면 update/insert 트리거 반드시 구현 delete 트리거는 불필요)

(참고로 상용버전인 PPAS 를 사용시 오라클 syntax 로 파티션 테이블을 생성하면
관련 트리거가 자동으로 생성됩니다.)
---------------------------------------------------------------------------------
CREATE TABLE p_emp
(
  empno integer NOT NULL,
  ename character varying(10),
  job character varying(9),
  mgr integer,
  hiredate date,
  sal integer,
  comm integer,
  deptno integer,
  CONSTRAINT p_emp_pkey PRIMARY KEY (empno)
);

CREATE TABLE p_emp_old (
CHECK (hiredate < DATE '2011-01-01')
) INHERITS (p_emp);


CREATE TABLE p_emp_2011 (
CHECK (hiredate >= DATE '2011-01-01' and hiredate < DATE '2012-01-01')
) INHERITS (p_emp);

CREATE TABLE p_emp_2012 (
CHECK (hiredate >= DATE '2012-01-01' and hiredate < DATE '2013-01-01')
) INHERITS (p_emp);

CREATE TABLE p_emp_2013 (
CHECK (hiredate >= DATE '2013-01-01' and hiredate < DATE '2014-01-01')
) INHERITS (p_emp);

CREATE TABLE p_emp_last (
CHECK (hiredate >= DATE '2014-01-01')
) INHERITS (p_emp);

ALTER TABLE p_emp_old ADD CONSTRAINT p_emp_old_pkey PRIMARY KEY (empno);
ALTER TABLE p_emp_2011 ADD CONSTRAINT p_emp_2011_pkey PRIMARY KEY (empno);
ALTER TABLE p_emp_2012 ADD CONSTRAINT p_emp_2012_pkey PRIMARY KEY (empno);
ALTER TABLE p_emp_2013 ADD CONSTRAINT p_emp_2013_pkey PRIMARY KEY (empno);
ALTER TABLE p_emp_last ADD CONSTRAINT p_emp_last_pkey PRIMARY KEY (empno);

CREATE INDEX p_emp_ix01 ON p_emp(deptno);
CREATE INDEX p_emp_old_ix01 ON p_emp_old(deptno);
CREATE INDEX p_emp_2011_ix01 ON p_emp_2011(deptno);
CREATE INDEX p_emp_2012_ix01 ON p_emp_2012(deptno);
CREATE INDEX p_emp_2013_ix01 ON p_emp_2013(deptno);
CREATE INDEX p_emp_2014_ix01 ON p_emp_last(deptno);

CREATE OR REPLACE FUNCTION p_emp_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.hiredate < DATE '2011-01-01') THEN
        INSERT INTO p_emp_old VALUES (NEW.*);
    ELSIF ( NEW.hiredate >= DATE '2011-01-01' and NEW.hiredate < DATE '2012-01-01') THEN
        INSERT INTO P_emp_2011 VALUES (NEW.*);
    ELSIF ( NEW.hiredate >= DATE '2012-01-01' and NEW.hiredate < DATE '2013-01-01') THEN
        INSERT INTO P_emp_2012 VALUES (NEW.*);
    ELSIF ( NEW.hiredate >= DATE '2013-01-01' and NEW.hiredate < DATE '2014-01-01') THEN
        INSERT INTO P_emp_2013 VALUES (NEW.*);
    ELSE
        INSERT INTO P_emp_last VALUES (NEW.*);
    --ELSE
        --RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER trigger_p_emp_insert
    BEFORE INSERT ON p_emp
    FOR EACH ROW EXECUTE PROCEDURE p_emp_func_insert_trigger();

INSERT INTO P_EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-2010', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO P_EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-2011', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO P_EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-2014', 'DD-MON-YYYY'), 1250,  500, 30);

  
---------------------------------------------------------------------------------

scottdb=# select * from p_emp;
 empno | ename |   job    | mgr  |  hiredate  | sal  | comm | deptno
-------+-------+----------+------+------------+------+------+--------
  7499 | ALLEN | SALESMAN | 7698 | 2011-02-20 | 1600 |  300 |     30
  7369 | SMITH | CLERK    | 7902 | 2010-12-17 |  800 |      |     20
  7521 | WARD  | SALESMAN | 7698 | 2014-02-22 | 1250 |  500 |     30
(3 rows)


scottdb=# select * from p_emp_old;
 empno | ename |  job  | mgr  |  hiredate  | sal | comm | deptno
-------+-------+-------+------+------------+-----+------+--------
  7369 | SMITH | CLERK | 7902 | 2010-12-17 | 800 |      |     20
(1 row)


scottdb=# select * from p_emp_2011;
 empno | ename |   job    | mgr  |  hiredate  | sal  | comm | deptno
-------+-------+----------+------+------------+------+------+--------
  7499 | ALLEN | SALESMAN | 7698 | 2011-02-20 | 1600 |  300 |     30
(1 row)


scottdb=# select * from p_emp_2012;
 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)


scottdb=# select * from p_emp_2013;
 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)


scottdb=# select * from p_emp_last;
 empno | ename |   job    | mgr  |  hiredate  | sal  | comm | deptno
-------+-------+----------+------+------------+------+------+--------
  7521 | WARD  | SALESMAN | 7698 | 2014-02-22 | 1250 |  500 |     30
(1 row)

scottdb=#


DBMS 2013-11-08 (금) 11:46
안녕하세요
파티션 테이블을 보고있는데
좀 자세히 매뉴얼처럼 올려주시면 안될까요 실제 상용서버에서 적용하려 합니다.
주의사항 및 유의점
경험노하우 등

  pg_rules 로 관리하는 파티션테이블은 어떤가요?
트리거 말고요.

감사합니다.
댓글주소
   

postgresdba.com