GPDB6 관리 alias

Greenplum에서 자주 사용하는 쿼리를 alias로 정리하였습니다.
Greenplum 6 버전입니다.

1. 테스트 쿼리
  1) 시나리오 
      - 락으로 인하여, 어떤 쿼리에 문제가 있는지 확인하는 방법
      - 카티션 조인 발생 후 truncate 실행
  2) 쿼리
     세션 #1, gpadmin=# select count(*) from aaa, aaa, aaa;
     세션 #2, gpadmin=# truncate table aaa;
     
2. 수행 Alias

## qq: 현재 수행되고 있는 세션 확인, waiting에 t가 대기하고 있는 세션
[gpadmin@mdw ~]$ qq 
    ?column?     | usename | client_addr | waiting |  pid  | sess_id 
-----------------+---------+-------------+---------+-------+---------
 00:00:10.35229  | gpadmin |             | f       | 66659 |    1980
 00:00:00        | gpadmin |             | f       | 73344 |    2149
 00:00:02.341306 | gpadmin |             | t       | 66412 |    1977
(3 rows)

## qqit: 현재 수행되고 있는 세션 확인, waiting에 t가 대기하고 있는 세션이며, 쿼리 앞부분 확인
[gpadmin@mdw ~]$ qqit
    backend_tiem     |  duration_time  | usename | client_addr | waiting |  pid  | sess_id |      substring       
---------------------+-----------------+---------+-------------+---------+-------+---------+----------------------
 2019-11-20 14:22:00 | 00:00:12.251838 | gpadmin |             | f       | 66659 |    1980 | select count(*) from
 2019-11-20 14:31:42 | 00:00:00        | gpadmin |             | f       | 73372 |    2150 | SELECT substring(bac
 2019-11-20 14:21:30 | 00:00:04.240854 | gpadmin |             | t       | 66412 |    1977 | truncate table aaa;
(3 rows)

## lt: locked 된 테이블의 Lock 유형과 락 프로세스 찾기
   - running pid: 현재 수행되고 있는 프로세스 ID, 즉 카티션 조인되는 쿼리
   - waiting pid: 현재 locking된 프로세스 ID, truncate 구문
[gpadmin@mdw ~]$ lt
 locktype | relation |        mode         | waiting_pid | running_pid | gp_segment_id 
----------+----------+---------------------+-------------+-------------+---------------
 relation | aaa      | AccessExclusiveLock |       66412 |       66659 |            -1
(1 row)

## locks: 현재 Lock 확인, 현 시점에서는 필요 없으나, 어떤 락이 걸리는지 확인
[gpadmin@mdw ~]$ locks
  pid  | relname | locktype |        mode         | gp_segment_id 
-------+---------+----------+---------------------+---------------
 66659 | aaa     | relation | AccessShareLock     |            -1
 66412 | aaa     | relation | AccessExclusiveLock |            -1
 42560 | aaa     | relation | AccessShareLock     |             0
 42561 | aaa     | relation | AccessShareLock     |             1
 43080 | aaa     | relation | AccessShareLock     |             2
 43081 | aaa     | relation | AccessShareLock     |             3
(6 rows)

## cq: 현재 수행중인 쿼리 확인
[gpadmin@mdw ~]$ cq
    ?column?     |  pid  | usename | sess_id |                                                            query                                                            
-----------------+-------+---------+---------+-----------------------------------------------------------------------------------------------------------------------------
 00:00:57.979556 | 66659 | gpadmin |    1980 | select count(*) from aaa a, aaa b, aaa c;
 00:00:49.968572 | 66412 | gpadmin |    1977 | truncate table aaa;
 00:00:00        | 73958 | gpadmin |    2163 | select now()-query_start, pid, usename, sess_id, query from pg_stat_activity where state not like '%idle%' order by 1 desc;
(3 rows)


## 카티션 조인 쿼리 취소, 
[gpadmin@mdw ~]$ psql
psql (9.4.24)
Type "help" for help.

gpadmin=# select pg_terminate_backend(66659); -- 카티션 조인 쿼리 프로세스
 pg_terminate_backend 
----------------------
 t
(1 row)

gpadmin=# select pg_terminate_backend(66659);
WARNING:  PID 66659 is not a PostgreSQL server process
 pg_terminate_backend 
----------------------
 f  ==> 세션 없어짐을 확인
(1 row)

gpadmin=# \q

## cq: 현재 수행 쿼리
[gpadmin@mdw ~]$ cq
 ?column? |  pid  | usename | sess_id |                                                            query                                                            
----------+-------+---------+---------+-----------------------------------------------------------------------------------------------------------------------------
 00:00:00 | 74500 | gpadmin |    2189 | select now()-query_start, pid, usename, sess_id, query from pg_stat_activity where state not like '%idle%' order by 1 desc;
(1 row)

[gpadmin@mdw ~]$ lt
 locktype | relation | mode | waiting_pid | running_pid | gp_segment_id 
----------+----------+------+-------------+-------------+---------------
(0 rows)

[gpadmin@mdw ~]$ 


## alias 적용 방법
gpadmin 계정 홈디렉토리의 .bashrc에 적용

[gpadmin@mdw ~]$ vi .bashrc


## 현재 실행중인 쿼리 세션
alias qq='psql -c " select now()-query_start, usename, client_addr, waiting, pid, sess_id, pg_resgroup from pg_stat_activity  where state not like '\''%idle%'\'' order by 4, 1 desc;"'

## 현재 실행중인 쿼리 세션 및 일부 쿼리
alias qqit='psql  -c "SELECT substring(backend_start,1,19) as backend_tiem, now()-query_start as duration_time, usename, client_addr, waiting, pid, sess_id, pg_resgroup, substring(query,1,20) FROM pg_stat_activity AS query_string WHERE state <> '\''idle'\'' ORDER BY waiting, duration_time desc;"'

## lock 정보
alias lt='psql  -c "SELECT distinct w.locktype, w.relation::regclass AS relation, w.mode, w.pid as waiting_pid, other.pid as running_pid, w.gp_segment_id FROM pg_catalog.pg_locks AS w JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.pid = 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.pid = other.pid) WHERE NOT w.granted and w.pid <> other.pid;"'
alias locks='psql -c " SELECT pid, relname, locktype, mode, a.gp_segment_id from pg_locks a, pg_class where relation=oid and relname not like '\''pg_%'\'' order by 3;"'

## 현재 실행 중인 쿼리
alias cq='psql -c "select now()-query_start, pid, usename, sess_id, query from pg_stat_activity where state not like '\''%idle%'\'' order by 1 desc;"'

## analyze 정보
alias na='psql -c "SELECT count(relname) from pg_class where reltuples=0 and relpages=0 and relkind='\''r'\'' and relname not like '\''t%'\'' and relname not like '\''err%'\'';" '
alias nan='psql -c "SELECT '\''analyze '\''||nspname||'\''.'\''||relname||'\'';'\'' from pg_class c, pg_namespace nc where nc.oid = c.relnamespace and c.reltuples=0 and c.relpages=0 and c.relkind='\''r'\'' and c.relname not like '\''t%'\'' and c.relname not like '\''err%'\'';"'

## Bloate 된 테이블 리스트 보기
alias bt='psql -c "select bdinspname schema_nm, bdirelname tb_nm, bdirelpages*32.0/1024.0 real_size_mb, bdiexppages*32.0/1024.0 exp_size_mb from gp_toolkit.gp_bloat_diag where bdirelpages*32.0/1024.0 > 100;" '

## Idle 세션 보기
alias is='psql -c " SELECT now()-query_start, usename, pid, sess_id, query from pg_stat_activity where state like '\''idle'\'' order by 1 desc;"'
alias it='psql  -c "SELECT now()-query_start, usename, pid, sess_id, query FROM pg_stat_activity where trim(query) like '\''%in transaction'\'' ORDER BY 1 DESC;"'

## analyze 안된 리스트
alias na='psql -c "SELECT count(relname) from pg_class where reltuples=0 and relpages=0 and relkind='\''r'\'' and relname not like '\''t%'\'' and relname not like '\''err%'\'';" '

## analyze 안된 리스트 쿼리 생성
alias nan='psql -c "SELECT '\''analyze '\''||nspname||'\''.'\''||relname||'\'';'\'' from pg_class c, pg_namespace nc where nc.oid = c.relnamespace and c.reltuples=0 and c.relpages=0 and c.relkind='\''r'\'' and c.relname not like '\''t%'\'' and c.relname not like '\''err%'\'';"'
alias pgoption='PGOPTIONS="-c gp_session_role=utility" psql -p 5432'

## 리소스 큐
alias rss='psql -c " select a.rsqname, a.rsqcountlimit as countlimit, a.rsqcountvalue as countvalue, a.rsqwaiters as waiters, a.rsqholders as running ,a.rsqcostlimit as costlimit, a.rsqcostvalue as costvalue, b.rsqignorecostlimit as ignorecostlimit, b.rsqovercommit as overcommit from pg_resqueue_status a, pg_resqueue b where a.rsqname =b.rsqname order by 1;"'
alias rs='psql  -c " select rsqname, rsqcountlimit cntlimit, rsqcountvalue cntval, rsqcostlimit costlimit, rsqcostvalue vostval, rsqmemorylimit memlimit, rsqmemoryvalue memval, rsqwaiters waiters, rsqholders holders from gp_toolkit.gp_resqueue_status;"'

## 임시 스키마 확인
alias ts='psql -c "select n.nspname from pg_namespace n where nspname not in (select '\''pg_temp_'\''||sess_id from pg_stat_activity) and nspname  like '\''pg_temp%'\'';"'

## Reorg 대상 테이블 추출 및 쿼리 생성
alias reorg='psql -AXtc "select '\''ALTER TABLE '\''||bdinspname||'\''.'\''||bdirelname||'\'' SET WITH (REORGANIZE=TRUE); '\'' qry from gp_toolkit.gp_bloat_diag where bdirelpages*32/1024 > 10 ;" '

# resource group
alias rga='psql -c "SELECT rolname, rsgname FROM pg_roles, pg_resgroup  WHERE pg_roles.rolresgroup=pg_resgroup.oid;"'
alias rg='psql -c "SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;"'
alias rgs='psql -c "SELECT * FROM gp_toolkit.gp_resgroup_status_per_segment;"'
alias rgd='psql -c "SELECT * FROM gp_toolkit.gp_resgroup_status;"'

빠른 확인이 가능해서 생각보다 편리합니다.

GPDB6. 자동 형 변환

Greenplum 6 자동 형변환 및 Operator 변환

Greenplum 4에서는 자동으로 Data Type 형변환이 있었지만, Greenplum 5, 6에서는 명시적으로 형 변환을 해야합니다.
자동으로 형 변환하기 위해서는 아래의 함수를 생성하시면 됩니다.

Soruce: https://gpdb.docs.pivotal.io/6-1/install_guide/43x_to_5x.html
        https://gist.github.com/ioguix/4dd187986c4a1b7e1160

1. Type Case 에러 유형

   1) 데이터 Type이 맞지 않은 조인의 경우

      CREATE TABLE foo (a int) DISTRIBUTED RANDOMLY ;
      CREATE TABLE bar (b text) DISTRIBUTED RANDOMLY ;
      
      
      SELECT * FROM foo, bar WHERE foo.a = bar.b;
      ERROR:  operator does not exist: integer = text
      LINE 1: SELECT * FROM foo, bar WHERE foo.a = bar.b;
                                                 ^
      HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
      --> 2.데이터 타입 자동 형변환 함수 생성 스크립트  수행하면 자동 형변환 됨.



   2) Equal = 수행 에러

      CREATE TABLE foo (a int) DISTRIBUTED RANDOMLY ;
      CREATE TABLE bar (b text) DISTRIBUTED RANDOMLY ;
      insert into foo values (1);
      insert into bar values ('1');      
      
      testdb=# SELECT foo.a = bar.b FROM foo, bar WHERE foo.a = bar.b;
      ERROR:  operator does not exist: integer = text
      LINE 1: SELECT foo.a = bar.b FROM foo, bar WHERE foo.a = bar.b;
                           ^
      HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
                                                 ^
      --> 3. = 오퍼레이터 자동 형 변환 함수 생성
       
   3) concat || 수행 에러
      - Greenplum 5에서는 에러 발생
      - Greenplum 6에서는 에러 발생하지 않음   
      --> 4. || 오퍼레이터 자동 형 변환 함수 생성
      
      
2. 데이터 타입 자동 형변환 함수 생성 스크립트

## Type cast
CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(smallint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int2out($1));';
CREATE CAST (smallint AS text) WITH FUNCTION pg_catalog.text(smallint) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(oid) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(oidout($1));';
CREATE CAST (oid AS text) WITH FUNCTION pg_catalog.text(oid) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(date) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(date_out($1));';
CREATE CAST (date AS text) WITH FUNCTION pg_catalog.text(date) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float8out($1));';
CREATE CAST (double precision AS text) WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(real) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float4out($1));';
CREATE CAST (real AS text) WITH FUNCTION pg_catalog.text(real) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(time with time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timetz_out($1));';
CREATE CAST (time with time zone AS text) WITH FUNCTION pg_catalog.text(time with time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(time without time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(time_out($1));';
CREATE CAST (time without time zone AS text) WITH FUNCTION pg_catalog.text(time without time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(timestamp with time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timestamptz_out($1));';
CREATE CAST (timestamp with time zone AS text) WITH FUNCTION pg_catalog.text(timestamp with time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(interval) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(interval_out($1));';
CREATE CAST (interval AS text) WITH FUNCTION pg_catalog.text(interval) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(numeric) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(numeric_out($1));';
CREATE CAST (numeric AS text) WITH FUNCTION pg_catalog.text(numeric) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(timestamp without time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timestamp_out($1));';
CREATE CAST (timestamp without time zone AS text) WITH FUNCTION pg_catalog.text(timestamp without time zone) AS IMPLICIT;


3. = 오퍼레이터 자동 형 변환
CREATE FUNCTION pg_catalog.texteqint(text, integer) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqint2(text, smallint) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int2out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqoid(text, oid) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(oidout($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqdate(text, date) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(date_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqfloat8(text, double precision) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float8out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqreal(text, real) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float4out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtimetz(text, time WITH time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timetz_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtime(text, time without time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(time_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtimestamptz(text, timestamp WITH time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamptz_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqinterval(text, interval) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(interval_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqint8(text, bigint) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int8out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqnumeric(text, numeric) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(numeric_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtimestamp(text, timestamp without time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamp_out($2)) = $1;$$;

CREATE FUNCTION pg_catalog.inteqtext(integer, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.int2eqtext(smallint, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int2out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.oideqtext(oid, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(oidout($1)) = $2;$$;
CREATE FUNCTION pg_catalog.dateeqtext(date, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(date_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.float8eqtext(double precision, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float8out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.realeqtext(real, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float4out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timetzeqtext(time WITH time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timetz_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timeeqtext(time without time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(time_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timestamptzeqtext(timestamp WITH time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamptz_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.intervaleqtext(interval, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(interval_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.int8eqtext(bigint, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int8out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.numericeqtext(numeric, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(numeric_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timestampeqtext(timestamp without time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamp_out($1)) = $2;$$;

CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint, LEFTARG=text, RIGHTARG=integer, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint2, LEFTARG=text, RIGHTARG=smallint, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqoid, LEFTARG=text, RIGHTARG=oid, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqdate, LEFTARG=text, RIGHTARG=date, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqfloat8, LEFTARG=text, RIGHTARG=double precision, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqreal, LEFTARG=text, RIGHTARG=real, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtimetz, LEFTARG=text, RIGHTARG=time WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtime, LEFTARG=text, RIGHTARG=time without time zone, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtimestamptz, LEFTARG=text, RIGHTARG=timestamp WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqinterval, LEFTARG=text, RIGHTARG=interval, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint8, LEFTARG=text, RIGHTARG=bigint, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqnumeric, LEFTARG=text, RIGHTARG=numeric, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtimestamp, LEFTARG=text, RIGHTARG=timestamp without time zone, COMMUTATOR=OPERATOR(pg_catalog.=));

CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.inteqtext, LEFTARG=integer, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.int2eqtext, LEFTARG=smallint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.oideqtext, LEFTARG=oid, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.dateeqtext, LEFTARG=date, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.float8eqtext, LEFTARG=double precision, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.realeqtext, LEFTARG=real, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timetzeqtext, LEFTARG=time WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timeeqtext, LEFTARG=time without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timestamptzeqtext, LEFTARG=timestamp WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.intervaleqtext, LEFTARG=interval, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.int8eqtext, LEFTARG=bigint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.numericeqtext, LEFTARG=numeric, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timestampeqtext, LEFTARG=timestamp without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));


4. Concat, || 오퍼레이터 자동 형 변환

CREATE FUNCTION pg_catalog.textconcatint(text, integer)                           RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatint2(text, smallint)                         RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatoid(text, oid)                               RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatdate(text, date)                             RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatfloat8(text, double precision)               RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatreal(text, real)                             RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattimetz(text, time WITH time zone)            RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattime(text, time without time zone)           RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattimestamptz(text, timestamp WITH time zone)  RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatinterval(text, interval)                     RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatint8(text, bigint)                           RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatnumeric(text, numeric)                       RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattimestamp(text, timestamp without time zone) RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;

CREATE FUNCTION pg_catalog.intconcattext(integer, text)                           RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.int2concattext(smallint, text)                         RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.oidconcattext(oid, text)                               RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.dateconcattext(date, text)                             RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.float8concattext(double precision, text)               RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.realconcattext(real, text)                             RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timetzconcattext(time WITH time zone, text)            RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timeconcattext(time without time zone, text)           RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timestamptzconcattext(timestamp WITH time zone, text)  RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.intervalconcattext(interval, text)                     RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.int8concattext(bigint, text)                           RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.numericconcattext(numeric, text)                       RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timestampconcattext(timestamp without time zone, text) RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;

CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatint, LEFTARG=text, RIGHTARG=integer, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatint2, LEFTARG=text, RIGHTARG=smallint, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatoid, LEFTARG=text, RIGHTARG=oid, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatdate, LEFTARG=text, RIGHTARG=date, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatfloat8, LEFTARG=text, RIGHTARG=double precision, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatreal, LEFTARG=text, RIGHTARG=real, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattimetz, LEFTARG=text, RIGHTARG=time WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattime, LEFTARG=text, RIGHTARG=time without time zone, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattimestamptz, LEFTARG=text, RIGHTARG=timestamp WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatinterval, LEFTARG=text, RIGHTARG=interval, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatint8, LEFTARG=text, RIGHTARG=bigint, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatnumeric, LEFTARG=text, RIGHTARG=numeric, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattimestamp, LEFTARG=text, RIGHTARG=timestamp without time zone, COMMUTATOR=OPERATOR(pg_catalog.||));

CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.intconcattext,     LEFTARG=integer, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.int2concattext,    LEFTARG=smallint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.oidconcattext,     LEFTARG=oid, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.dateconcattext,    LEFTARG=date, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.float8concattext,  LEFTARG=double precision, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.realconcattext,    LEFTARG=real, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timetzconcattext,  LEFTARG=time WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timeconcattext,    LEFTARG=time without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timestamptzconcattext, LEFTARG=timestamp WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.intervalconcattext,    LEFTARG=interval, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.int8concattext,        LEFTARG=bigint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.numericconcattext,     LEFTARG=numeric, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timestampconcattext,   LEFTARG=timestamp without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));