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


Powered by EnterpriseDB
총 게시물 87건, 최근 0 건
 

PostgreSQL 과 EDB 에서의 파티션키 업데이트 - 주의!

글쓴이 : PostgresDBA 날짜 : 2018-08-17 (금) 16:23 조회 : 82
PostgreSQL 과 EDB 모두 아래 파티션을 생성했습니다.

CREATE TABLE emp_root(id int, dept varchar, location int) PARTITION BY LIST(dept);
CREATE TABLE emp_subroot PARTITION OF emp_root FOR VALUES IN ('dept1', 'dept2') PARTITION BY RANGE(location);
CREATE TABLE subroot_part_1 PARTITION OF emp_subroot FOR VALUES FROM (1) to (10);
CREATE TABLE subroot_part_2 PARTITION OF emp_subroot FOR VALUES FROM (11) to (20);
CREATE TABLE emp_subroot2 PARTITION OF emp_root FOR VALUES IN ('dept3', 'dept4');

INSERT INTO emp_root values (1, 'dept1', 5), (2, 'dept2', 16), (3, 'dept3', 7), (4, 'dept4', 17);
SELECT tableoid::regclass, * from emp_root ORDER BY id;
   tableoid    | id | dept | location
----------------+----+-------+----------
subroot_part_1 |  1 | dept1 | 5
subroot_part_2 |  2 | dept2 | 16
emp_subroot2   | 3 | dept3 |      7
emp_subroot2   | 4 | dept4 |     17
(4 rows)

각각 파티션소속이 바뀌도록 파티션키를 업데이트했습니다
EBB 는 성공이지만 PostgreSQL 에서는 에러납니다. 주의하세요!
===========================================================================================================================
[PostgreSQL]
postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

UPDATE emp_root SET dept = 'dept1' WHERE id = 4;
ERROR:  new row for relation "emp_subroot2" violates partition constraint
DETAIL:  Failing row contains (4, dept1, 17).

===========================================================================================================================
SQL> select version();
+-------------------------------------------------------------------------------------------------------------+
|                                                   version                                                   |
+-------------------------------------------------------------------------------------------------------------+
| EnterpriseDB 10.4.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit |
+-------------------------------------------------------------------------------------------------------------+
(1 row)

UPDATE emp_root SET dept = 'dept1' WHERE id = 4;
UPDATE 1

PostgresDBA 2018-08-23 (목) 10:10
pg 10 과 달리 pg 11 에서는 에러없이 정상 업데이트 될거라하네요.
pg11 정식버전 나오면 테스트해봐야 겠네요
댓글주소
 

postgresdba.com