흑백과 디지털

 오늘 회사에서 Postgresql 의 Upsert 를 이용해서 로직을 만들었다. 이게 하나의 query 뿐만 아니라 multi로 Upsert 도 가능 한 것 같아서 query 를 효율적으로 짜기 위해 Multiple Upsert 를 구현했다. 

Upsert 는 Insert 시 뭔가 insert 를 할 수 없는 경우(특히 primary key 가 중복된다든지 Unique 조건이 있는데, 이미 Unique 한 값이 있다든지 하는 경우) 기존 row 를 업데이트 하는 쿼리를 말한다. 

CREATE TABLE test_table(
    id SERIAL PRIMARY KEY,
    name varchar,
    visit int
);

라는 test_table 을 생성한 경우 
아래 query 를 날리면

INSERT INTO test_table (id, name , visit) 
	VALUES(1, 'Bill', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

처음에 insert 될 때는
1, 'Bill', 1 인 상태이지만 2번째에 다시 해당 query 가 동작하면 1, 'Bill', 2   로 +1씩 visit 를 증가시키는 동작을 할 수 있다.

default 키워드

이런 동작을 할 때, Primary Key 가 꼭 필요하다. 그런데 Insert 를 할 때는 일반적으로 Primary 가 있을리가 없다. 그래서 column 자체를 갖지 않는 경우가 많은데, 프로그램에서 Query 를 이용하다보면 primary key 대신 뭔가 자리를 차지할 값을 넣어야 할 때가 있다. 이럴 때는 default 라는 키워드를 대신 사용할 수 있다.

INSERT INTO test_table (id, name , visit) VALUES(default, 'Jone', 1)

이렇게 사용할 수 있다. (이 구문은 정말 신기하다. ) 
(이게 동작하지 않는 다면 nextval 함수를 이용해야 한다.)
(위에서 강제로 primary key 를 대입했기 때문에 이 구문 동작시키면 에러가 발생할 수 있다. 다시 한 번 동작하면 정상적으로 동작한다. primary key 를 강제로 넣는 동작고 auto increment 하는 것을 섞어 쓰면 안되는 것 같다.)

이 구문을 Upsert 로 만든면

INSERT INTO test_table (id, name , visit) 
	VALUES(default, 'Jane', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

라고 적을 수 있으며 당연히 insert 동작만 하게 된다.

Multiple Upsert

프로그램에서 sql 을 이용할 때, 일반적으로 query 갯수를 줄이면 좋다.  여러개의  Upsert 하는 것보다는 한 번에 Upsert 를 하는게 일반적으로 더 빠른 것이다. 
여러개의 Upsert 는 다음과 같이 할 수 있다. 

INSERT INTO test_table (id, name , visit) 
	VALUES(4, 'Mary', 1), (5, 'Anna', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

values 다음에 여러 리스트를 적으면 된다.

여기서 데이터의 추가가 필요하다면 존재하지 않는 primary key 대신 default 를 하면 된다.

INSERT INTO test_table (id, name , visit) 
	VALUES(4, 'Mary', 1), (5, 'Anna', 1), (default, 'Henry', 1)
ON CONFLICT (id)
DO UPDATE
SET visit = test_table.visit + 1;

excluded 키워드 이용

경우에 따라서 update 시 insert 에서 이용한 값을 재이용하고 싶을 때가 있을 것이다. 이 때는 excluded  키워드를 사용한다.

INSERT INTO test_table (id, name , visit) 
	VALUES(1, 'Mary', 1), (2, 'Anna', 1)
ON CONFLICT (id)
DO UPDATE
SET 
	visit = test_table.visit + 1,
	name = excluded.name

sqlalchemy

sqlalchemy 에서 default  라는 Keyword 를 이용할 때는 sqlalchemy.text('default') 해야 한다. 
upsert 는  docs.sqlalchemy.org/en/14/dialects/postgresql.html#updating-using-the-excluded-insert-values
를 참고하면 된다. 

explain.dalibo.com/

회사에서 query 최적화 할일이 있어 위 사이트의 도움을 많이 받고 있다. 

내가 분석하고자 하는 쿼리에 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)  를 붙여서 내 서버에서 분석코드를 뽑은 후, 그 분석 코드를 저 웹사이트에 넣으면 예쁘게 tree 를 만들어서 보여준다.

위 스크린 샷 처럼 보여준다. 왼쪽 부분에서 막대표시가 해당 부분의 소모시간을 보여주고 있다. 이 시간 소모가 큰 것부터 최적화 대상으로 삼으면서 해결해 나가고 있다. 

 외부 서비스를 이용해서 홈페이지를 만들었는데, 기본 템플릿으로 만들어진 사이트가 IE11 에서만 엄청 느리게 동작하는 현상이 있었다. 고민하다가 Edge 로 동작시키는 방법이 있지 않을까 하고 열심히 찾았다. 그러다 
stackoverflow.com/a/65258124 이런 글을 보았다.

주소에  microsoft-edge:google.com 이런 식으로 주소를 이동시키면 Edge 가 있는 경우 Edge 브라우저를 강제로 열 수가 있다. chrome 에서도 된다. 아래와 같은 팝업이 뜬다.

IE11 에서는 위와 같은 창이 없이 그대로 뜬다. 
IE11 일 때 edge 로 이동시키고 싶으면 아래와 같은 자바스크립트를 삽입하면 동작한다. 

<script>
if(navigator.userAgent.indexOf('MSIE')!==-1
|| navigator.appVersion.indexOf('Trident/') > -1){
    window.location.href = 'microsoft-edge:' + window.location.href;
}
</script>

IE11 일 때만 Edge 를 강제로 열어서 현재 주소로 이동시킨다. 다만 Edge 가 없는 환경일 때는 문제가 발생한다. 자바스크립트만으로는 Edge 브라우저가 있는지 없는지 알 수 없다. 그러나 IE11 사용자부터가 소수이고, windows 7은 이미 기술지원이 완료되었기 때문에 IE11 사용자의 컴퓨터 환경에 Edge 브라우저가 있다고 생각된다.  때문에 이렇게 사용하면 더 많은 사용자를 고려할 수 있을 거라 판단된다. 

예전에는 IE6 이 없어져야 한다고 생각했는데, 이제는 IE11 이다. IE11 때문에 개발하기 너무 힘들다.