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 | +-------+-------+--------+ |
반응형