'EDB(PAS) and PostgreSQL' 카테고리의 글 목록 (6 Page)

EDB(PAS) and PostgreSQL

View의 특이 사항 2022.06.19

View의 특이 사항

2022. 6. 19. 11:00

728x90

PostgreSQL의 View는 약간 다르게 동작하는 부분이 있어서 테스트 진행

View 특징

1. View에서 참조하는 테이블의 이름이 변경되면, View안의 테이블이름도 변경된다

2. View가 존재 하는 원본 테이블을 삭제 하려고 하거나, View에서 참조하는 칼럼을 변경하려고 하면 에러가 난다.

1. 원본 테이블 명 변경 시 View 내용 변경

9.3 ~ 14까지 다 테스트를 진행해 보니 실제로 참조 테이블에서 이름을 변경하게 되면 View안에서 해당 테이블 이름도 자동으로 변경 된다.

Why ?

PostgreSQL은 다른 DBMS와 다르게 View 생성 SQL문을 저장해 놓고 있지 않다. 즉, view에 대한 SQL문이 저장되어서 쿼리 수행 시 Rewrite가 되는 것이 아니라,

parse tree 형태로 저장하기 때문이다. 해당 parse tree 형태에서는 테이블 이름을 저장하고 있지 않고, 테이블의 OID값을 사용하기 때문에 테이블을 rename 하더라도

기존의 OID가 변경되지 않기 때문에 자동으로 변경 된 이름의 테이블 명을 그대로 참조하게 된다.

create table emp (
empno    numeric(4,0) not null constraint emp_pk primary key
, ename    varchar(10)
, job      varchar(9)
, mgr      numeric(4,0)
, hiredate timestamp
, sal      numeric(7,2)
, comm     numeric(7,2)
, deptno   numeric(2,0)
);

insert into emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
values
(7369, 'SMITH' , 'CLERK'     , 7902, '17-DEC-80 00:00:00', 800.00,    NULL,     20),
(7499, 'ALLEN' , 'SALESMAN' , 7698, '20-FEB-81 00:00:00', 1600.00, 300.00,     30),
(7521, 'WARD'   , 'SALESMAN' , 7698, '22-FEB-81 00:00:00', 1250.00, 500.00,     30),
(7566, 'JONES' , 'MANAGER'   , 7839, '02-APR-81 00:00:00', 2975.00,    NULL,     20),
(7654, 'MARTIN' , 'SALESMAN' , 7698, '28-SEP-81 00:00:00', 1250.00, 1400.00,     30),
(7698, 'BLAKE' , 'MANAGER'   , 7839, '01-MAY-81 00:00:00', 2850.00,    NULL,     30),
(7782, 'CLARK' , 'MANAGER'   , 7839, '09-JUN-81 00:00:00', 2450.00,    NULL,     10),
(7788, 'SCOTT' , 'ANALYST'   , 7566, '19-APR-87 00:00:00', 3000.00,    NULL,     20),
(7839, 'KING'   , 'PRESIDENT' , NULL, '17-NOV-81 00:00:00', 5000.00,    NULL,     10),
(7844, 'TURNER' , 'SALESMAN' , 7698, '08-SEP-81 00:00:00', 1500.00,    0.00,     30),
(7876, 'ADAMS' , 'CLERK'     , 7788, '23-MAY-87 00:00:00', 1100.00,    NULL,     20),
(7900, 'JAMES' , 'CLERK'     , 7698, '03-DEC-81 00:00:00', 950.00,    NULL,     30),
(7902, 'FORD'   , 'ANALYST'   , 7566, '03-DEC-81 00:00:00', 3000.00,    NULL,     20),
(7934, 'MILLER' , 'CLERK'     , 7782, '23-JAN-82 00:00:00', 1300.00,    NULL,     10);

create view v_emp
as
select * from emp ;

select pg_get_viewdef('v_emp') ;
+----------------------+
|    pg_get_viewdef    |
+----------------------+
| SELECT emp."EMPNO",+|
|     emp."ENAME",    +|
|     emp."JOB",      +|
|     emp."MGR",      +|
|     emp."HIREDATE", +|
|     emp."SAL",      +|
|     emp."COMM",     +|
|     emp."DEPTNO"    +|
|    FROM emp;         |
+----------------------+

select oid , relname from pg_class where relname in ('emp' , 'v_emp') ;
+-------+---------+
| oid | relname |
+-------+---------+
| 16481 | v_emp   |
| 16462 | emp     |
+-------+---------+

select ev_action from pg_rewrite where ev_class = (select oid from pg_class where relname = 'v_emp') ;

({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSe
curity false :isReturn false :cteList <> :rtable ({RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("EMPNO" "ENAME" "JOB" "MGR" "HIREDATE" "SAL" "COMM" "DEPTNO")} :rtekind 0 :relid 16481 :relkind v :rellockmode 1 :tablesample <> :lat
eral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("EMPNO" "ENAME" "JO
B" "MGR" "HIREDATE" "SAL" "COMM" "DEPTNO")} :rtekind 0 :relid 16481 :relkind v :rellockmode 1 :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQu
als <>} {RTE :alias <> :eref {ALIAS :aliasname emp :colnames ("EMPNO" "ENAME" "JOB" "MGR" "HIREDATE" "SAL" "COMM" "DEPTNO")} :rtekind 0 :relid 16462 :relkind r :rellockmode 1 :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :select
edCols (b 8 9 10 11 12 13 14 15) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 701 :vartypmod -1 :varcol
lid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 1 :location 28} :resno 1 :resname EMPNO :ressortgroupref 0 :resorigtbl 16462 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnosyn 3 :var
attnosyn 2 :location 28} :resno 2 :resname ENAME :ressortgroupref 0 :resorigtbl 16462 :resorigcol 2 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 3 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnosyn 3 :varattnosyn 3 :location 28} :resno 3 :re
sname JOB :ressortgroupref 0 :resorigtbl 16462 :resorigcol 3 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 4 :vartype 701 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 4 :location 28} :resno 4 :resname MGR :ressortgroupref 0 :resorigtbl
16462 :resorigcol 4 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 5 :vartype 1184 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 5 :location 28} :resno 5 :resname HIREDATE :ressortgroupref 0 :resorigtbl 16462 :resorigcol 5 :resjunk fals
e} {TARGETENTRY :expr {VAR :varno 3 :varattno 6 :vartype 701 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 6 :location 28} :resno 6 :resname SAL :ressortgroupref 0 :resorigtbl 16462 :resorigcol 6 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :var
attno 7 :vartype 701 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 7 :location 28} :resno 7 :resname COMM :ressortgroupref 0 :resorigtbl 16462 :resorigcol 7 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 8 :vartype 701 :vartypmod -1 :var
collid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 8 :location 28} :resno 8 :resname DEPTNO :ressortgroupref 0 :resorigtbl 16462 :resorigcol 8 :resjunk false}) :override 0 :onConflict <> :returningList <> :groupClause <> :groupDistinct false :groupingSets <> :havingQual <
> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <> :setOperations <> :constraintDeps <> :withCheckOptions <> :stmt_location 0 :stmt_len 39})

테이블 이름 변경

alter table emp rename to emp_bak ;

select oid , relname from pg_class where relname in ('emp_bak' , 'v_emp') ;
+-------+---------+
| oid | relname |
+-------+---------+
| 16481 | v_emp   |
| 16462 | emp_bak |
+-------+---------+

select pg_get_viewdef('v_emp') ;
+--------------------------+
|      pg_get_viewdef      |
+--------------------------+
| SELECT emp_bak."EMPNO",+|
|     emp_bak."ENAME",    +|
|     emp_bak."JOB",      +|
|     emp_bak."MGR",      +|
|     emp_bak."HIREDATE", +|
|     emp_bak."SAL",      +|
|     emp_bak."COMM",     +|
|     emp_bak."DEPTNO"    +|
|    FROM emp_bak;         |
+--------------------------+

select ev_action from pg_rewrite where ev_class = (select oid from pg_class where relname = 'v_emp') ;

({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSe
curity false :isReturn false :cteList <> :rtable ({RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("EMPNO" "ENAME" "JOB" "MGR" "HIREDATE" "SAL" "COMM" "DEPTNO")} :rtekind 0 :relid 16481 :relkind v :rellockmode 1 :tablesample <> :lat
eral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("EMPNO" "ENAME" "JO
B" "MGR" "HIREDATE" "SAL" "COMM" "DEPTNO")} :rtekind 0 :relid 16481 :relkind v :rellockmode 1 :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQu
als <>} {RTE :alias <> :eref {ALIAS :aliasname emp :colnames ("EMPNO" "ENAME" "JOB" "MGR" "HIREDATE" "SAL" "COMM" "DEPTNO")} :rtekind 0 :relid 16462 :relkind r :rellockmode 1 :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :select
edCols (b 8 9 10 11 12 13 14 15) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 701 :vartypmod -1 :varcol
lid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 1 :location 28} :resno 1 :resname EMPNO :ressortgroupref 0 :resorigtbl 16462 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnosyn 3 :var
attnosyn 2 :location 28} :resno 2 :resname ENAME :ressortgroupref 0 :resorigtbl 16462 :resorigcol 2 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 3 :vartype 25 :vartypmod -1 :varcollid 100 :varlevelsup 0 :varnosyn 3 :varattnosyn 3 :location 28} :resno 3 :re
sname JOB :ressortgroupref 0 :resorigtbl 16462 :resorigcol 3 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 4 :vartype 701 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 4 :location 28} :resno 4 :resname MGR :ressortgroupref 0 :resorigtbl
16462 :resorigcol 4 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 5 :vartype 1184 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 5 :location 28} :resno 5 :resname HIREDATE :ressortgroupref 0 :resorigtbl 16462 :resorigcol 5 :resjunk fals
e} {TARGETENTRY :expr {VAR :varno 3 :varattno 6 :vartype 701 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 6 :location 28} :resno 6 :resname SAL :ressortgroupref 0 :resorigtbl 16462 :resorigcol 6 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :var
attno 7 :vartype 701 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 7 :location 28} :resno 7 :resname COMM :ressortgroupref 0 :resorigtbl 16462 :resorigcol 7 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 8 :vartype 701 :vartypmod -1 :var
collid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 8 :location 28} :resno 8 :resname DEPTNO :ressortgroupref 0 :resorigtbl 16462 :resorigcol 8 :resjunk false}) :override 0 :onConflict <> :returningList <> :groupClause <> :groupDistinct false :groupingSets <> :havingQual <
> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <> :setOperations <> :constraintDeps <> :withCheckOptions <> :stmt_location 0 :stmt_len 39})

rename전과 비교해 보면 ev_action 부분에 변경된 부분이 없다.

2. view가 참조하는 원본 테이블 변경

View는 원본 테이블에 대한 Dependency가 존재 하게 된다.

그래서 View에서 참조하는 테이블 또는 칼럼이 변경이 되지 않게 하고 있어서, 해당 뷰를 삭제하지 않고는 뷰에서 참조하는 테이블이나 칼럼을 변경 할 수 없게 된다.

drop view v_emp ;

create view v_emp
as select empno , ename , sal from emp ;

select pg_get_viewdef('v_emp') ;
+--------------------+
|   pg_get_viewdef   |
+--------------------+
| SELECT emp.empno,+|
|     emp.ename,    +|
|     emp.sal       +|
|    FROM emp;       |
+--------------------+

                            Table "public.emp"
+----------+-----------------------------+-----------+----------+---------+
| Column |            Type             | Collation | Nullable | Default |
+----------+-----------------------------+-----------+----------+---------+
| empno    | numeric(4,0)                |           | not null |         |
| ename    | character varying(10)       |           |          |         |
| job      | character varying(9)        |           |          |         |
| mgr      | numeric(4,0)                |           |          |         |
| hiredate | timestamp without time zone |           |          |         |
| sal      | numeric(7,2)                |           |          |         |
| comm     | numeric(10,2)               |           |          |         |
| deptno   | numeric(2,0)                |           |          |         |
+----------+-----------------------------+-----------+----------+---------+
Indexes:
    "emp_pk" PRIMARY KEY, btree (empno)

칼럼의 사이즈 변경

alter table emp alter column sal type numeric(10,2) ;
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view v_emp depends on column "sal"

칼럼 삭제

alter table emp drop column sal ;
ERROR: cannot drop column sal of table emp because other objects depend on it
DETAIL: view v_emp depends on column sal of table emp
HINT: Use DROP ... CASCADE to drop the dependent objects too

참조하지 않는 칼럼 사이즈 변경

alter table emp alter column comm type numeric(10,2) ;
ALTER TABLE

참조하는 테이블 삭제

drop table emp ;
ERROR: cannot drop table emp because other objects depend on it
DETAIL: view v_emp depends on table emp
HINT: Use DROP ... CASCADE to drop the dependent objects too.

참조 테이블에 새로운 칼럼 추가

alter table emp add column new_col varchar(10) ;
ALTER TABLE

View에서 참조하는 테이블 및 칼럼 확인

select v.oid::regclass as view
       , a.attrelid::regclass as table
       , a.attname as column
from pg_attribute as a
join pg_depend as d on d.refobjsubid = a.attnum and d.refobjid = a.attrelid
join pg_rewrite as r on r.oid = d.objid
join pg_class as v on v.oid = r.ev_class
where v.relkind = 'v'    -- only interested in views
and   d.classid = 'pg_rewrite'::regclass
and   d.refclassid = 'pg_class'::regclass
and   d.deptype = 'n'    -- normal dependency
and   v.relname = 'v_emp' ;

+-------+-------+--------+
| view | table | column |
+-------+-------+--------+
| v_emp | emp   | empno |
| v_emp | emp   | ename |
| v_emp | emp   | sal    |
+-------+-------+--------+

저작자표시 비영리 변경금지

PREV 1 ···3 4 5 6 7 8 9 ···25 NEXT

멋지게 놀아라

EDB(PAS) and PostgreSQL

View의 특이 사항

+ Recent posts

티스토리툴바