반응형

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    |
+-------+-------+--------+

 

반응형

+ Recent posts