기본적으로 HAWQ 는 postgresql 8.2 를 사용중이므로
아래 쿼리들은 몇몇 쿼리를 제외하고는 postgresql 8.2 에서도 사용 가능하지만
postgresql 9.x 에서는 8.x 와 엔진 자체가 달라서 사용 불가능하다.
mybatis 에서 사용하는 쿼리 형식 비슷하게 개발중이기 때문에
아래 쿼리 중에는 바로 실행 불가능한 쿼리들도 있다
간혹 dbSchemaTable 이란 변수가 있는데 이는
database.schema.table 형태로 써주면 된다. ex) gpadmin.public.table_name
설명
쿼리
현재 실행중인 쿼리의 pid 조회
SELECT pg_backend_pid();
autocommit 상태 조회
show autocommit;
hawq version 조회
SELECT
productversion
FROM
pg_catalog.gp_version_at_initdb;
모든 데이터베이스 database 조회
SELECT
datname "databaseName"
FROM
pg_database
ORDER BY
datname;
모든 스키마 schema 조회
SELECT
schema_name "schemaName"
FROM
information_schema.schemata
ORDER BY
schema_name;
기본 스키마 default schema 조회
SHOW search_path;
특정 database 의 schema 내 모든 테이블 table 조회
SELECT
t.table_catalog
,t.table_schema
,t.table_name
,t.table_type
,t.is_insertable_into
,t.is_typed
,c.relstorage
,c.oid::int
,'TABLE' object_type
,'('||pg_size_pretty(pg_relation_size(c.oid))||')' table_size
,'('||pg_size_pretty(
(
SELECT
sum(pg_relation_size(ns.nspname||'.'||pp.partitiontablename))
FROM
pg_class cl
INNER JOIN pg_namespace ns ON cl.relnamespace = ns.oid
INNER JOIN pg_partitions pp ON cl.relname = pp.tablename
WHERE pp.tablename = c.relname
)::bigint
)||')' size
FROM
information_schema.tables t
INNER JOIN pg_namespace s ON t.table_schema = s.nspname
INNER JOIN pg_class c ON t.table_name = c.relname AND s.oid = c.relnamespace
LEFT JOIN pg_partitions p ON t.table_name = p.partitiontablename
WHERE
p.tablename IS NULL
GROUP BY
t.table_catalog
,t.table_schema
,t.table_name
,t.table_type
,t.is_insertable_into
,t.is_typed
,c.relstorage
,p.partitiontablename
,c.oid
,c.relname
HAVING
t.table_catalog = #{databaseName} AND
t.table_schema = #{schemaName} AND
c.relstorage NOT IN ('v', 'x')
ORDER BY
t.table_name;
특정 database 의 schema 내 모든 뷰 view 조회
SELECT
c.oid::int
,t.table_catalog
,t.table_schema
,t.table_name
,t.table_type
,t.is_insertable_into
,t.is_typed
,'VIEW' object_type
FROM
information_schema.tables t
INNER JOIN pg_namespace s ON t.table_schema = s.nspname
INNER JOIN pg_class c ON t.table_name = c.relname AND s.oid = c.relnamespace
LEFT JOIN pg_partitions p ON t.table_name = p.partitiontablename
WHERE
t.table_catalog = #{databaseName} AND
t.table_schema = #{schemaName} AND
t.table_type = 'VIEW'
ORDER BY
t.table_name;
특정 database 의 schema 내 모든 external 테이블 external table 조회
SELECT
t.table_catalog
,t.table_schema
,t.table_name
,t.table_type
,t.is_insertable_into
,t.is_typed
,c.relstorage
,c.oid::int
,'EXTTABLE' object_type
FROM
information_schema.tables t
INNER JOIN pg_namespace s ON t.table_schema = s.nspname
INNER JOIN pg_class c ON t.table_name = c.relname AND s.oid = c.relnamespace
GROUP BY
t.table_catalog
,t.table_schema
,t.table_name
,t.table_type
,t.is_insertable_into
,t.is_typed
,c.relstorage
,c.oid
HAVING
t.table_catalog = #{databaseName} AND
t.table_schema = #{schemaName} AND
c.relstorage = 'x'
ORDER BY
t.table_name;
특정 database 의 schema 내 모든 함수 function 조회
SELECT
p.oid
,a.rolname
,n.nspname
,p.proname
,l.lanname
,p.pronargs
,p.prorettype::regtype
,oidvectortypes(p.proargtypes) proargtypes
,p.prosrc
,p.proname||'('||oidvectortypes(p.proargtypes)||')' procdesc
,'FUNCTION' object_type
FROM pg_proc p
INNER JOIN pg_namespace n ON n.oid = p.pronamespace
INNER JOIN pg_authid a ON a.oid = p.proowner
INNER JOIN pg_language l ON l.oid = p.prolang
WHERE
n.nspname = #{schemaName} AND
p.prorettype != 'trigger'::regtype
ORDER BY
p.proname;
특정 database 의 schema 내 object 의 모든 컬럼 column 조회
SELECT
table_catalog
,table_schema
,table_name
,column_name
,ordinal_position
,column_default
,is_nullable
,data_type
,character_maximum_length
,col_description(#{dbSchemaTable}::regclass, ordinal_position) column_comment
FROM
information_schema.columns
WHERE
table_catalog = #{databaseName} AND
table_schema = #{schemaName} AND
table_name = #{tableName}
ORDER BY
ordinal_position;
모든 tablespace 조회
SELECT
spcname
FROM
pg_tablespace
ORDER BY
spcname;
모든 custom formatter 조회
SELECT
p.proname
FROM
pg_proc p
INNER JOIN pg_namespace n ON n.oid = p.pronamespace
INNER JOIN pg_authid a ON a.oid = p.proowner
INNER JOIN pg_language l ON l.oid = p.prolang
WHERE
(p.prorettype = 'record'::regtype OR oidvectortypes(p.proargtypes) = 'record') AND
l.lanname != 'internal' AND
n.nspname = 'pg_catalog'
ORDER BY p.proname;
특정 database 의 schema 내 모든 제약사항 constraint 조회
SELECT
conname
,contype
,array_to_string(conkey, ',') conkeys
,array_upper(conkey, 1) length
,substring(consrc from 2 for char_length(consrc) - 2) consrc
FROM
pg_constraint
WHERE
#{dbSchemaTable}::regclass = conrelid
ORDER BY
length
,conname;
특정 테이블의 파티션정의 partition definition 조회
SELECT pg_get_partition_def(#{dbSchemaTable}::regclass) partitiondef;
특정 테이블의 파티션테이블 partition table 조회
SELECT
c.oid id
,c.oid
,ns.nspname
,c.relname
,c.relname table_name
,c.relname text
,c.relhassubclass
,not(c.relhassubclass) leaf
,pr.parname
,t.table_type
,'TABLE' object_type
,c.relstorage
FROM pg_class c
INNER JOIN pg_partition_rule pr ON c.oid = pr.parchildrelid
INNER JOIN pg_inherits i ON c.oid = i.inhrelid
INNER JOIN pg_namespace ns ON c.relnamespace = ns.oid
INNER JOIN information_schema.tables t ON (t.table_name = c.relname AND ns.oid = c.relnamespace)
WHERE
c.relkind = 'r' AND
i.inhparent = #{oid}::oid
ORDER BY
c.relname;
파티션 테이블 partition table 의 정보 조회
SELECT
schemaname
,tablename
,partitiontablename
,partitionname
,parentpartitiontablename
,parentpartitionname
,partitiontype
,partitionlevel
,partitionrank
,partitionposition
,partitionlistvalues
,partitionrangestart
,partitionstartinclusive
,partitionrangeend
,partitionendinclusive
,partitioneveryclause
,partitionisdefault
,partitionboundary
,parenttablespace
,partitiontablespace
FROM
pg_partitions
WHERE
schemaname = #{schemaName} AND
partitiontablename = #{tableName};
분산키 distributed key 조회
SELECT
cl.relname
,array_to_string(dp.attrnums, ',') attrnums
FROM
gp_distribution_policy dp
INNER JOIN pg_class cl ON dp.localoid = cl.oid
LEFT JOIN pg_namespace ns ON cl.relnamespace = ns.oid
WHERE
ns.nspname = #{schemaName} AND
cl.relname = #{tableName};
실행중인 쿼리 세션 중지 query session kill terminate
SELECT pg_terminate_backend(#{pid});
모든 리소스큐 resource queue 조회
SELECT
q.oid::int as queueid,
q.rsqname as rsqname,
t1.value::int as rsqcountlimit,
t2.value::int as rsqcountvalue,
t3.value::real as rsqcostlimit,
t4.value::real as rsqcostvalue,
t5.value::real as rsqmemorylimit,
t6.value::real as rsqmemoryvalue,
t7.value::int as rsqwaiters,
t8.value::int as rsqholders
FROM pg_resqueue q,
pg_resqueue_status_kv() t1 (queueid oid, key text, value text),
pg_resqueue_status_kv() t2 (queueid oid, key text, value text),
pg_resqueue_status_kv() t3 (queueid oid, key text, value text),
pg_resqueue_status_kv() t4 (queueid oid, key text, value text),
pg_resqueue_status_kv() t5 (queueid oid, key text, value text),
pg_resqueue_status_kv() t6 (queueid oid, key text, value text),
pg_resqueue_status_kv() t7 (queueid oid, key text, value text),
pg_resqueue_status_kv() t8 (queueid oid, key text, value text)
WHERE
q.oid = t1.queueid
AND t1.queueid = t2.queueid
AND t2.queueid = t3.queueid
AND t3.queueid = t4.queueid
AND t4.queueid = t5.queueid
AND t5.queueid = t6.queueid
AND t6.queueid = t7.queueid
AND t7.queueid = t8.queueid
AND t1.key = 'rsqcountlimit'
AND t2.key = 'rsqcountvalue'
AND t3.key = 'rsqcostlimit'
AND t4.key = 'rsqcostvalue'
AND t5.key = 'rsqmemorylimit'
AND t6.key = 'rsqmemoryvalue'
AND t7.key = 'rsqwaiters'
AND t8.key = 'rsqholders'
ORDER BY
q.rsqname
,t1.value;
모든 그룹롤 group role 조회
SELECT
oid::int
,rolname
,rolsuper
,rolinherit
,rolcreaterole
,rolcreatedb
,rolcatupdate
,rolcanlogin
,rolconnlimit
,rolvaliduntil
,rolconfig
,rolresqueue
,rolcreaterextgpfd
,rolcreaterexthttp
,rolcreatewextgpfd
,rolcreaterexthdfs
,rolcreatewexthdfs
FROM
pg_roles
WHERE
not(rolcanlogin)
ORDER BY
rolname;
모든 로그인롤 login role 조회
SELECT
oid::int
,rolname
,rolsuper
,rolinherit
,rolcreaterole
,rolcreatedb
,rolcatupdate
,rolcanlogin
,rolconnlimit
,rolvaliduntil
,rolconfig
,rolresqueue
,rolcreaterextgpfd
,rolcreaterexthttp
,rolcreatewextgpfd
,rolcreaterexthdfs
,rolcreatewexthdfs
FROM
pg_roles
WHERE
rolcanlogin
ORDER BY
rolname;
특정 롤 role 조회
SELECT
ro.oid::int
,ro.rolname
,ro.rolsuper
,ro.rolinherit
,ro.rolcreaterole
,ro.rolcreatedb
,ro.rolcatupdate
,ro.rolcanlogin
,ro.rolconnlimit
,to_char(ro.rolvaliduntil, 'YYYY-MM-DD') rolvaliduntil
,ro.rolconfig
,ro.rolresqueue
,rq.rsqname
,ro.rolcreaterextgpfd
,ro.rolcreaterexthttp
,ro.rolcreatewextgpfd
,ro.rolcreaterexthdfs
,ro.rolcreatewexthdfs
FROM
pg_roles ro
INNER JOIN pg_resqueue rq ON ro.rolresqueue = rq.oid
WHERE
ro.oid = #{oid};
실행중인 세션 session 조회
SELECT
datid
,datname
,procpid
,sess_id
,usesysid
,usename _username
,CASE WHEN length(current_query) > 1022 THEN current_query||'(...)'
ELSE current_query
END current_query
,waiting
,to_char(query_start, 'YYYY-MM-DD HH24:MI:SS:MS') query_start
,to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS:MS') backend_start
,client_addr
,client_port
,application_name
,to_char(xact_start, 'YYYY-MM-DD HH24:MI:SS:MS') xact_start
FROM
pg_stat_activity
WHERE
current_query not like '%<IDLE>%'
ORDER BY
query_start
LIMIT
${limit}
OFFSET
${start};
Lock table 조회
SELECT
DISTINCT
w.locktype
,w.relation::regclass AS relation
,w.mode
,w.pid AS waiting_pid
,w_stm.current_query waiting_query
,other.pid AS other_pid
,other_stm.current_query other_query
FROM
pg_catalog.pg_locks AS w
JOIN pg_catalog.pg_stat_activity AS w_stm ON w_stm.procpid = w.pid
JOIN pg_catalog.pg_locks AS other ON
(w.DATABASE = other.DATABASE AND
w.relation = other.relation) OR
w.transactionid = other.transactionid
JOIN pg_catalog.pg_stat_activity AS other_stm ON other_stm.procpid = other.pid
WHERE
NOT w.granted AND
w.pid <> other.pid;
데이터베이스 크기 database size 조회
SELECT
pg_size_pretty(pg_database_size(#{databaseName}))||
CASE WHEN pg_database_size(#{databaseName}) > 1023 THEN
' ('||
pg_database_size(#{databaseName})||
' bytes)'
ELSE '' END size;
스키마 크기 schema size 조회
SELECT
pg_size_pretty(sum(pg_relation_size(schemaname||'.'||tablename))::bigint)||
CASE WHEN sum(pg_relation_size(schemaname||'.'||tablename))::bigint > 1023 THEN
' ('||
sum(pg_relation_size(schemaname||'.'||tablename))::bigint||
' bytes)'
ELSE '' END size
FROM
pg_tables
WHERE
schemaname = #{schemaName};
테이블 크기 table size 조회
SELECT
pg_size_pretty(pg_relation_size(#{dbSchemaTable}))||
CASE WHEN pg_relation_size(#{dbSchemaTable}) > 1023 THEN
' ('||
pg_relation_size(#{dbSchemaTable})||
' bytes)'
ELSE '' END size;
어플리케이션 이름 세팅 set application name (쿼리 실행 전 실행해주면 세션 조회에서 조회 가능함)
set application_name=#{appName};
데이터베이스 삭제 drop database
END;
DROP DATABASE IF EXISTS ${dropDatabaseName};
BEGIN;
스키마 삭제 drop schema
DROP SCHEMA IF EXISTS ${schemaName} RESTRICT;
테이블 삭제 drop table
DROP TABLE IF EXISTS ${dbSchemaTable} RESTRICT;
external 테이블 삭제 drop external table
DROP EXTERNAL TABLE IF EXISTS ${dbSchemaTable} RESTRICT;
뷰 삭제 drop view
DROP VIEW IF EXISTS ${dbSchemaView} RESTRICT;
함수 삭제 drop function
DROP FUNCTION IF EXISTS ${dbSchemaFunction} (${proargtypes}) RESTRICT;
리소스큐 삭제 drop resource queue
DROP RESOURCE QUEUE ${queueName};
롤 삭제 drop role
DROP ROLE ${rolname};
