왕초보 프로그래머 Sky

기본적으로 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};