[PostgreSQL] 테이블 스키마, 소유자 및 오브젝트 소유자 변경

테이블 스키마, 소유자 및 오브젝트 소유자 변경 관련 쿼리입니다.
간단한 문구로 소유자 변경이 가능합니다.
 

테이블 소유자 변경
alter table {table_name} owner to {new_role};

테이블 스키마 변경
ALTER TABLE {table_name} SET SCHEMA {new_schema_name};

특정 소유자가 소유한 모든 객체 owner 변경
REASSIGN OWNED BY {old_role} TO {new_role};

 
 
하지만 ! 변경하려는 오브젝트가 사용중인 트랜잭션이 있다면.. Lock대기가 발생하겠죠
운영중에는 영향도를 파악하여 진행해야합니다.
 
관련 실습예제입니다.

/*
버전 : PostgreSQL 15.8 (Ubuntu 15.8-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit

실습내용 : User1이 소유한 testdb 스키마의 mytable을 user2로 소유권 변경
세션1 - mytable insert 
세션2 - owner 변경 진행
Session3 - lock 조회
모든 세션은 postgres : superuser 로 진행 
*/

--session1 : 트랜잭션활성화하여 테이블에 insert 실행
begin; 
 insert into testdb.my_table values(2,'d',1);

--session 2 : my_table의 소유자 변경
alter table testdb.my_table owner to user2;

----session 2 대기진행중 

--session3 : lock 조회
SELECT
    w.query AS waiting_query,
    w.pid AS waiting_pid,
    w.usename AS waiting_user,
    w.wait_event_type,
    l.query AS locking_query,
    l.pid AS locking_pid,
    l.usename AS locking_user,
    t.schemaname || '.' || t.relname AS tablename,
    l.wait_event_type
 FROM pg_stat_activity w
       JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
       JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted
       JOIN pg_stat_activity l ON l2.pid = l.pid
       JOIN pg_stat_user_tables t ON l1.relation = t.relid;

-> insert 트랜잭션에 의해 소유자 변경 세션이 lock 대기상태가됨을 확인