쿼리를 실행했다가 에러가 발생했다던지 너무 오래걸려 강제종료를 했다던지 등의 행동을 했는데 쿼리가 계속 돌고 있어서 테이블에 대한 transaction lock이 걸린 경우, 실행 중인 쿼리를 종료해야 할 때 유용하게 쓰입니다.

현재 실행 중인 쿼리 및 pid 확인

SELECT * FROM pg_stat_activity ORDER BY query_start ASC;

실행 중인 쿼리 종료

SELECT pg_cancel_backend(pid값); -- 성공하면 true, 실패하면 false 반환


검색하고자 하는 테이블을 참조하고 있는 테이블 및 컬럼 리스트 확인

SELECT kcu.table_name AS child_table,
	kcu.table_schema AS child_schema,
    kcu.column_name AS child_column,
    kcu.constraint_name AS child_constraint
 FROM information_schema.table_constraints tc
   JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
   JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' and ccu.table_name = '테이블명';
GROUP BY kcu.table_name, kcu.table_schema, kcu.column_name, kcu.constraint_name;

현재 테이블이 참조하고 있는 테이블 및 컬럼 확인

 SELECT tc.table_name AS child_table,
    kcu.column_name AS child_column,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column
   FROM information_schema.table_constraints tc
     JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
     JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text
  WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_name = '테이블명';


위의 FOREIGN KEY 대신 PRIMARY KEY 라던지 제약조건을 작성하면 해당하는 결과가 떨어짐


-- split_part(컬럼,자르고자하는 문자, 인덱스)


-- ex
select split_part(tel,'-',4)
from test
-- test 테이블의 tel 컬럼에서 - 문자로 자른 후 4번째에 있는 데이터를 출력 (index는 0이 아닌 1부터 시작)



-- (length(특정컬럼) - length(replace(특정컬럼, 찾고자 하는 문자 , ''))) = 찾고자 하는 개수


-- ex)
select *
from test
where (length(tel) - length(replace(tel, '-' , ''))) = 3
-- test테이블 내 tel 컬럼에서 - 값이 3개인 값


  1. 2017.09.21 15:32

    비밀댓글입니다

PostgreSQL 및 여러 DB 가상 실행: http://sqlfiddle.com/

왼쪽의 build schema를 누르면 작성된 내용대로 스키마가 생성되고 오른쪽의 run sql을 누르면 작성한 sql이 실행되어 하단에 결과 테이블이 출력됩니다.

예제 템플릿 : http://sqlfiddle.com/#!15/58d92/1



컬럼을 지정한 다음 중복된 데이터라면 그 이전 데이터의 값과 다음 데이터의 값을 확인하려고 할 때 사용할 수 있습니다.

테스트 데이터

먼저 테스트 대상 테이블입니다.

Client | Rate | StartDate|EndDate     
 A      | 1000 | 2005-1-1 |2005-12-31
 A      | 2000 | 2006-1-1 |2006-12-31
 A      | 3000 | 2007-1-1 |2007-12-31  
 B      | 5000 | 2006-1-1 |2006-12-31  
 B      | 8000 | 2008-1-1 |2008-12-31  
 C      | 2000 | 2006-1-1 |2006-12-31  


다음은 원하고자 하는 결과 테이블입니다.

Client | Rate | StartDate|EndDate    |Pre Rate | Pre StartDate |Pre EndDate    
 A      | 1000 | 2005-1-1 |2005-12-31 |         |               |             
 A      | 2000 | 2006-1-1 |2006-12-31 | 1000    | 2005-1-1      |2005-12-31            
 A      | 3000 | 2007-1-1 |2007-12-31 | 2000    | 2006-1-1      |2006-12-31  
 B      | 5000 | 2006-1-1 |2006-12-31 |         |               |              
 B      | 8000 | 2008-1-1 |2008-12-31 | 5000    | 2006-1-1      |2006-12-31   
 C      | 2000 | 2006-1-1 |2006-12-31 

결과

window function을 사용하여 아래와 같이 작성합니다.

SELECT client, 
       rate,
       startdate, 
       enddate, 
       lag(rate) over client_window as pre_rate,
       lag(startdate) over client_window as pre_startdate,
       lag(enddate) over client_window as pre_enddate
FROM the_table
WINDOW client_window as (partition by client order by startdate)
ORDER BY client, stardate;

해당 결과는 이전 데이터만 확인하는 lag() 함수를 사용했습니다. 만약 다음 데이터를 확인하고 싶은 경우엔 lead()함수를 사용하면 됩니다.

응용

이전 데이터 뿐만 아니라, 이전전의 데이터와 다음다음의 데이터를 확인하고 싶으면 아래와 같이 작성합니다. (위의 테스트 테이블과 별개의 쿼리)

select 
    *,
    lag(previous_id) over w as pre_previous_id, 
    lag(previous_title) over w as pre_previous_title, 
    lead(next_id) over w as next_next_id, 
    lead(next_title) over w as next_next_title
from (
    select 
        *,
        lag(id) over w as previous_id, 
        lag(title) over w as previous_title, 
        lead(id) over w as next_id, 
        lead(title) over w as next_title
    from 
        the_table
    
    window w as (partition by category order by insert_time)
) as onestep_table
window w as (partition by category order by insert_time)


window function은 aggregate function처럼 어떤 계산을 도와줍니다. 하지만 aggregate function의 결과가 하나의 row로 보여진다면, 이 window function은 row마다 결과를 보여줍니다. 결과적으로 동작이 조금 달라서 이름을 다르게 지었다고 보면 됩니다. 실제로 PostgreSQL에서 Aggregate function을 만들고, 그녀석을 window function처럼 사용할 수 있습니다.

예제

avg()

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

위의 sql 을 해석하면 아래와 같습니다.

  • empsalary table 에서 salary에 대한 avg() 를 구함. 그런데 avg() 는 "depname 칼럼의 값"을 구분(partition)해서 구함.
  • 주의할 점은 OVER가 들어가야 window function으로 인식. 그렇지 않으면 그냥 aggregation이 됨.

만약 group by 를 사용한다면, 아래처럼 바꿀 수 있을 것입니다.

SELECT depname, avg(salary) OVER (PARTITION BY depname) FROM empsalary GROUP BY depname;

결과

depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

rank()

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

empsalary table에서 depname의 값별로 salary에 대한 rank 를 구합니다.

ORDER BY

 이 window function에서 OVER부분에 들어가는 ORDER BY는 역할이 좀 다릅니다. 이 partition 안에서 row들의 집합을 window frame이라고 하는데, 많은 window function이 partition전체에서 동작하지 않고 이 window frame의 row 들에서 동작합니다.

ORDER BY가 없으면 기본적으로 window frame은 partition의 모든 row가 됩니다. 그런데 ORDER BY가 OVER에 들어가면 ORDER BY한 column 각각의 row가 window frame이 됩니다. 이 때 중복된 값은 같은 frame이 됩니다.

예제

SELECT salary, sum(salary) OVER () FROM empsalary;
salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)


SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

2개의 column 값 더해서 새로운 값

이번에는 2개의 rank 를 구하고, 이것을 더해 보겠습니다.

SELECT t.code, rank+rank_2 AS sum_rank
from
(
 SELECT code, market, 
  rank() OVER (PARTITION BY market ORDER BY price DESC) AS rank,
  rank() OVER (ORDER BY price DESC) AS rank_2
 FROM test_market
) AS t 
ORDER BY sum_rank DESC;

test_market이라는 table에서 2개의 rank를 구하는데, 1개는 market값 당 price로 내림차순 정렬하여 rank를 구하고, 다른 한 개(rank_2)는 그냥 price 당 내림차순 정렬해서 구합니다. 이렇게 나온 순위(rank)를 더해서 sum_rank 를 구하고 내림차순을 한 쿼리입니다.

partition by

partition by는 위의 예제에서 확인한 것처럼 PARTITION BY로 정의하는 목록이 같은 그룹인지, 다른 그룹인지 구분하는 기준으로 사용되고, 같은 그룹 내에서는 현재 로우 값도 포함해서 계산된 윈도우 함수의 결과 값을 공유합니다.

응용

여러 개의 윈도우 함수를 함께 쓸 때는 각각 OVER 절을 사용해야하는데, 이 때 지정할 원도우 프래임이 복잡하다면, 중복 입력해야하는 이슈가 발생해 오류가 발생할 가능성이 커집니다. 이 문제점을 줄이기 위해서 WINDOW절을 이용합니다

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

함수 종류

아래는 윈도우 함수의 리스트입니다.

  1. row_number
  2. rank
  3. dense_rank
  4. percent_rank
  5. cume_dist
  6. ntile
  7. lag
  8. lead
  9. first_value
  10. last_value
  11. nth_value


더 자세한 내용을 아래 링크에서 확인할 수 있습니다.

공식문서: https://www.postgresql.org/docs/9.5/static/tutorial-window.html

번역문서: http://postgresql.kr/docs/9.5/tutorial-window.html

PostgreSQL에서 타입을 변경하는 함수는 아래와 같이 지원됩니다. 

CAST()

먼저 SQL 표준인 CAST()입니다.

CAST(value AS type)
 
-- 예시
CAST(1234 AS TEXT)

::

다른 한가지는 PostgreSQL에서 지원하는 :: 입니다.

value::type
 
-- 예시
1234::TEXT

PostgreSQL에서 지원하는 ::이 사용되는 특별한 경우는 '{apple,cherry apple, avocado}'::text[];를 문자열 리터럴인 {apple,cherry apple, avocado}로 변환하고 PostgreSQL이 text의 어레이로 해석하도록 합니다.

함수형 형변환

마지막으로 함수형처럼 사용할 수 있는 형식입니다.

type(value)
 
-- 예시
TEXT(1234)


이 방식은 항상 작용하는 것이 아니라 유효한 유형에만 작동합니다. 예를들어 double 표현은 사용할 수 없지만 float8은 사용이 가능합니다.

SELECT DOUBLE(1234) -- 타입 변환 에러
SELECT FLOAT8(1234) -- 성공


또한 interval, time, timestamp는 아래와 같이 큰 따옴표(")로 묶여버리기 때문에 사용을 할 수 없습니다. 

SELECT TIMESTAMP('20170101') -- ERROR:  syntax error at or near "'20170101'" 에러
SELECT '20170101'::TIMESTAMP -- 성공


그러므로 함수와 유사한 타입 변환 구문은 사용에 제약이 발생하여 피하고 CAST() 또는 :: 방식을 사용하는 것을 권합니다.


자세한 내용은 https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC에서 확인할 수 있습니다.

주어진 날짜에서 일주일의 시작 날짜와 마지막 날짜를 PostgreSQL에서 지원하는 date_trunc()함수를 사용하여 계산할 수 있습니다.

시작날짜 구하기

SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp); 
-> 2012-07-23 00:00:00

만약 timestamp가 아닌 date만 원한다면 아래와 같이 date 타입으로 변환하여 출력할 수 있습니다.


SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp)::date; 
-> 2012-07-23

마지막날짜 구하기

SELECT (date_trunc('week', '2012-07-25 22:24:22'::timestamp)+ '6 days'::interval)::date; 
->2012-07-29

응용

주어진 날짜가 이번주에 속해 있는지 확인하려면 아래와 같이 사용할 수 있습니다.

date_trunc('week', now())::date <= 임의의 날짜변수 ::date and 임의의 날짜변수::date <= (date_trunc('week', now()) + '6 days')::date


PostgreSQL의 datetime 함수에 더 자세한 내용은 https://www.postgresql.org/docs/9.5/static/functions-datetime.html에서 확인할 수 있습니다.

Connection Pool이란?

서버는 동시에 사용할 수 있는 사람의 수라는 개념이 존재합니다. 일반적인 Connection Pool을 이용하면 동시 접속자 수를 벗어나게 될 경우 에러(예외)가 발생하게 됩니다. 예외가 발생하면 그 접속자는 더이상 처리를 하지 못하므로, 사이트 이용자는 다시 접속을 시도해야하는 불편함이 있습니다. 이를 해결하기 위해 탄생한 것이 Connection Pool 입니다. Connection Pool이란 동시 접속자가 가질 수 있는 Connection을 하나로 모아놓고 관리한다는 개념입니다. 누군가 접속하면 자신이 관리하는 Pool에서 남아있는 Connection을 제공합니다. 하지만 남아있는 Connection이 없는 경우라면 해당 클라이언트는 대기 상태로 전환시킵니다. 그리고 Connection이 다시 Pool에 들어오면 대기 상태에 있는 클라이언트에게 순서대로 제공합니다. 

다시말해 데이터베이스와 연결된 Connection을 미리 만들어서 pool 속에 저장해 두고 있다가 필요할 때 Connection을 Pool에서 쓰고 다시 Pool에 반환하는 기법을 말합니다. 웹 프로그램에서는 데이터베이스의 환경설정과 연결 관리 등을 따로 XML파일이나 속성 파일을 사용해서 관리하고, 이렇게 설정된 정보를 이름을 사용하여 획득하는 방법을 사용합니다. 웹 컨테이너가 실행되면서 Connection(connection) 객체를 미리 Pool(pool)에 생성해 둡니다. DB와 연결된 Connection(connection)을 미리 생성해서 Pool(pool) 속에 저장해 두고 있다가 필요할 때에 가져다 쓰고 반환합니다. 미리 생성해두기 때문에 데이터베이스에 부하를 줄이고 유동적으로 연결을 관리 할 수 있습니다.


이렇게 Pool 속에 미리 생성되어 있는 Connection을 가져다가 사용하고, 사용이 끝나면 Connection을 Pool에 반환합니다.

설명

만약 한명의 접속자가 웹 사이트에 접속했다고 가정합니다. 해당 웹 사이트에서 접속자는 게시판을 확인하고 자신이 쓴 게시물을 수정하고 또 새로운 게시글을 등록합니다. 그럼 이 한명의 접속자로 인해 DB접속은 아래와 같이 발생합니다.

  1. 데이터 취득
  2. 검색 후 데이터 취득
  3. 데이터 갱신
  4. 데이터 새등록

한명의 접속자로 인해 단 시간에 4번의 DB 접속이 일어납니다. 만약 접속자가 1000명 이라면 몇번의 DB 접속이 일어날까요? 이러한 오버헤드를 방지하기위해 미리 Connection 객체를 생성하고 해당 Connection 객체를 관리하는것을 의미합니다. 즉 'Connection Pool에 DB와 연결을 해 놓은 객체를 두고 필요할 때마다 Connection Pool에서 빌려오는 것' 라고 생각하면 됩니다. 그리고 연결이 끝나면 다시 Pool에 돌려줍니다. Connection Pool을 너무 크게 해놓으면 당연히 메모리 소모가 클것이고, 적게 해놓으면 Connection이 많이 발생할 경우 대기시간이 발생하기때문에 웹 사이트 동시 접속자 수 등 서버 부하에 따라 크기를 조정해야 합니다.

특징

Pool 속에 미리 Connection이 생성되어 있기 때문에 Connection을 생성하는 데 드는 연결 시간이 소비되지 않습니다. Connection을 계속해서 재사용하기 때문에 생성되는 Connection 수가 많지 않습니다. Connection Pool을 사용하면 Connection을 생성하고 닫는 시간이 소모되지 않기 때문에 그만큼 어플리케이션의 실행 속도가 빨라지며, 또한 한 번에 생성될 수 있는 Connection 수를 제어하기 때문에 동시 접속자 수가 몰려도 웹 어플리케이션이 쉽게 다운되지 않습니다.

Connection Pool에서 생성되어 있는 Connection의 갯수는 한정적입니다. 동시 접속자가 많아지면 Connection Pool은 누군자 접속하면 Connection Pool에 남아 있는 Connection을 제공하는 식이다. 하지만 남아있는 Connection이 없을 경우 해당 클라이언트는 대기 상태로 전환이 되고, Connection이 반환되면 대기하고 있는 순서대로 Connection이 제공됩니다.

'DB' 카테고리의 다른 글

[DB]Connection Pool  (0) 2017.02.11
[SQL] WHERE절에서 IF문처럼 AND 사용  (0) 2017.02.02
[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (0) 2016.11.18

+ Recent posts