쿼리를 실행했다가 에러가 발생했다던지 너무 오래걸려 강제종료를 했다던지 등의 행동을 했는데 쿼리가 계속 돌고 있어서 테이블에 대한 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에서 확인할 수 있습니다.

SQL 표준은 transaction isolation에 대해 네 가지 레벨로 정의하고 있습니다. 이 네 가지중 가장 엄격한 것은 Serializable이며, 이것은 마치 여러 세션의 같은 트랜잭션 작업을 한 줄로 세워 차례 대로 진행하는 것과 같은 결과를 보장합니다. 나머지 세 가지 레벨은 동시에 진행되는 트랜잭션들 사이 간 허용되는 작업의 범위에 따라서 구분됩니다. 표준안에서는 Serializable이 가능한 transaction isolation 수준은 동시에 진행되는 트랜잭션 사이의 상호 관계가 전혀 없어야 한다고 정의합니다. (이것은 놀라운 이야기입니다 -- 실 세계에서는 당연히 동시에 여러 트랜잭션들이 발생할 것이고, 이것들이 어떻게 서로 상호 관계를 안 할 수 있을까? 라는 의문점을 남깁니다.)

각 수준별 이름과 상호 작용 범위는 다음과 같습니다:


dirty read

한 트랜잭션은 다른 트랜잭션에 아직 커밋하지 않은 자료도 읽을 수 있습니다.

nonrepeatable read

한 트랜잭션은 다른 트랜잭션에서 커밋한 자료를 읽을 수 있습니다. (처음 어떤 자료를 읽고, 다시 읽으려고 하는데, 그 사이 다른 트랜잭션이 자료를 변경하고 커밋했다면, 다음 읽는 값이 커밋된 값으로 읽을 수 있습니다.)

phantom read

위와 같은 상황에서 다른 트랜잭션에 의한 커밋된 자료가 있다 하더라도, 항상 자신의 트랜잭션에서 조회 했던 그 자료값 그대로 보여줍니다.

serialization anomaly

트랜잭션 그룹을 성공적으로 커밋 한 결과는 한 번에 하나씩 모든 트랜잭션을 실행할 수 있는 순서와 일치하지 않습니다.


아래는 SQL 표준과 PostgreSQL에서 구현한 트랜잭션 isolation level을 나타낸 표입니다.

Isolation levelDirty ReadNonrepeatable ReadPhantom ReadSerialization Anomaly
Read uncommitted허용, PG에서는 없음가능가능가능
Read committed불가능가능가능가능
Repeatable read불가능불가능허용, PG에서는 없음가능
Serializable불가능불가능불가능불가능


PostgreSQL에서는 네 가지 표준 transaction isolation level 중 하나를 요청할 수 있지만 내부적으로 세 가지 별도의 isolation level만 구현됩니다. 즉, PostgreSQL의 Read Uncommitted 모드는 커밋이 된 자료만 읽기가 가능합니다. 이는 PostgreSQL의 다중 버전 동시성 제어 아키텍처를 표준 isolation level에 매핑하는 유일한 방법이기 때문입니다.

또한 위의 표는 PostgreSQL의 Repeatable read 구현이 phantom reads를 허용하지 않음을 보여줍니다. 더 엄격한 동작은 SQL 표준에 의해 허용됩니다. 네 가지 isolaction level은 어떤 현상이 발생해서는 안되고 어떤 현상이 발생해야 하는지를 정의합니다. 사용가능한 isolation level의 동작은 아래에서 자세히 설명하겠습니다.

만약 트랜잭션 isolation level을 지정하려면 SET TRANSACTION 명령을 사용하면 됩니다.

  • PostgreSQL의 몇몇 자료형과 함수는 트랜잭션 내 특별한 형태로 읽기 특성을 제공합니다. 특히 자동 증가 컬럼으로 사용하는 serial 자료형과 sequence같은 객체는 rollback이 없으며, 자료 변경 즉시 다른 모든 세션에서도 그 변경된 값을 볼 수 있습니다.

PostgreSQL에서의 트랜잭션 레벨

Read Committed Isolation Level

PostgreSQL에서의 기본 레벨임.

SELECT 쿼리

  • 쿼리가 시작했을 당시의 커밋된 데이터만 참조 가능
  • 쿼리가 실행되는 도중 커밋되지 않은 데이터 혹은 동시에 실행되고 있던 트랜잭션에서 커밋된 변경들은 참조하지 않음
  • 외부 트랜잭션이 아닌 자신의 트랜잭션 도중에 일어난 커밋되지 않은 수정 사항은 참조할 수 있음
  • 트랜잭션 내부에서 select가 여러번 있을 때, 각 select 쿼리 사이에 외부 트랜잭션이 커밋을 했으면 커밋된 다음에 실행된 select 쿼리는 커밋된 데이터를 참조하게 됨

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 쿼리

  • select 쿼리와 동일하게 동작
  • 단지 데이터를 변경하는 도중 다른 트랜잭션이 같은 로우에 접근하면 그 트랜잭션이 끝난 후에 변경사항에 대해서 작업을 진행하게 됨. 이 때, 외부 트랜잭션 때문에 변경된 로우가 현재 명령의 where 조건에 맞는지 다시 평가를 하게 됨


Read Committed는 복잡한 검색조건이 있는 상황에 맞지 않고, 은행 잔고 변경과 같은 단순한 경우에 적합.

Repeatable Read Isolaction Level

SELECT 쿼리

  • 현재 트랜잭션이 시작하기전에 커밋된 데이터만 참조
  • 외부 트랜잭션에서 커밋되거나 커밋되지 않은 변경을 참조하지 않음
  • 외부 트랜잭션이 아닌 자신의 트랜잭션 도중에 일어난 커밋되지 않은 수정 사항은 참조할 수 있음

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 쿼리

  • 데이터를 변경하는 도중 다른 트랜잭션이 같은 로우에 접근하면 그 트랜잭션이 끝난 후에 변경하려던 로우가 변경됐는지 확인해서 변경사항이 없으면 원래 진행하려던 명령을 실행하고, 변경됐으면 다음과 같은 에러메세지를 내면서 트랜잭션을 롤백 (ERROR: could not serialize access due to concurrent update)
  • 읽기만 하는 트랜잭션에서는 에러가 없음


Read Committed와 차이점은 현재 트랜잭션에 여러번의 쿼리가 있을 경우, 현재 트랜잭션의 쿼리 중간에 다른 트랜잭션에서 변경이 있었더라도 참조하지 않고, 현재 트랜잭션이 시작되었을 때의 데이터와 현재 트랜잭션이 수행되던 도중에 일어난 변경사항만 참조

이 레벨을 사용하면 실패했을 때 재시도하는 것을 어플리케이션에서 처리해야함

Serializable Isolation Level

가장 엄격한 트랜잭션 레벨

  • 트랜잭션들이 동시에 일어나지 않고, 하나씩 순서대로 실행되는 것처럼 작동
  • Repeatable Read처럼 어플리케이션에서 실패했을 때 재시도 처리를 해줘야함
  • Repeatable Read와 동일하게 작동하면서 트랜잭션들이 순서대로 실행중인지 모니터링
  • 잘 고려하여 사용하지 않으면 성능저하 이슈가 존재


참조문서:

https://www.postgresql.org/docs/current/static/transaction-iso.html

http://arisu1000.tistory.com/27756

+ Recent posts