<div id="readability-page-1" class="page"><div>안녕하세요.

이번 주제는 postgresql의 upsert를 구현해보는 포스팅입니다.

흔히 jpa를 쓸때 save() 메소드는 있으면 insert, 없으면 update를 한다고 알고 있는데요.&nbsp;

JPA는 대략 아래처럼 SimpleJpaRepository에 구현되어 있습니다.
<pre><code>@Transactional
@Override
public &lt;S extends T&gt; S save(S entity) {

 Assert.notNull(entity, "Entity must not be null.");

 if (entityInformation.isNew(entity)) {
 em.persist(entity);
 return entity;
 } else {
 return em.merge(entity);
 }
}</code></pre>
entity 정보가 isNew에 참이면 persist - 등록하고 아니면 merge를 하게 됩니다. 이건 이제 JPA로 넘어온 어플리케이션 영역이고, sql 단에서는 어떻게 이것을 구현할 수 있을지 알아보겠습니다.

<hr contenteditable="false" data-ke-type="horizontalRule" data-ke-style="style3">

우선 저는 두개의 테이블을 설계하였습니다.

<pre><code>create table room (
 seq serial primary key,
 column_1 varchar(10),
 column_2 varchar(10),
 column_3 varchar(10),
 column_4 varchar(10)
);

create table room_new_data (
 room_seq integer
 primary key
 references room,
 column_1 varchar(10),
 column_2 varchar(10)
);</code></pre>
메인 테이블인 room과 room의 primary key를 참조하는 room_new_dat 테이블입니다.&nbsp;

room 테이블은 기존에 별도의 데이터가 아래와 같이 들어있습니다.
<figure data-ke-mobilestyle="widthOrigin" data-origin-width="1414" data-origin-height="248"><img src="https://blog.kakaocdn.net/dn/bMVRCP/btr4uQT1I13/fMXE9CrX6fkQd3b24cjupK/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="1414" data-origin-height="248"><figcaption>부모 테이블 room</figcaption>
</figure>

이제 room 테이블의 레코드를 채웠으니 room_new_data가 room 테이블을 참조할 데이터가 생겼습니다.

근데.. 단일 스레드가 아닌 멀티스레드로 동작을 하다보니 다음과 같은 문제가 벌어졌습니다.
<figure data-ke-mobilestyle="widthOrigin" data-origin-width="2022" data-origin-height="1162"><img src="https://blog.kakaocdn.net/dn/BXiEa/btr4uI9GpM3/GUfgEdBtWqCBtv0KRG60m1/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="2022" data-origin-height="1162"></figure>

동일한 시간에 두개의 세션에서 다음과 같은 insert SQL을 동시에 날렸다면.. 먼저 커밋된 Transaction A가 정상적으로 커밋되고 Transaction B에서는 Error가 발생하였습니다. 재시도를 해도 동일한 에러메시지가 노출되어 insert 구문을 완성할 수 없습니다.

현재 저의 요구사항은 어쨌든 가장 최근 정보로 column이 들어가야하는데 거의 동시에 두개의 트랜잭션이 데이터를 삽입하고자 하니 primary key에 대한 제약조건 에러가 발생하게 됩니다.

이때 Transaction B의 데이터가 최종적으로 select SQL에서 노출되게 하기 위해서는 어떻게 해야할까요? DBMS가 insert 쿼리를 받는 순간 위의 JPA 로직처럼 개발자가 제어할 수 있는 방법은 더이상 없습니다. 단순한 Insert 쿼리로는요.

이러한 제어를 위해 On Conflict 문이 존재합니다. 쿼리를 아래와 같이 수정해보겠습니다.
<figure data-ke-mobilestyle="widthOrigin" data-origin-width="2022" data-origin-height="1098"><img src="https://blog.kakaocdn.net/dn/dfdFbP/btr4tPOXzGA/SZBJw8tMifvX3umEK9jKlk/img.png" onerror="this.onerror=null; this.src='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png'; this.srcset='//t1.daumcdn.net/tistory_admin/static/images/no-image-v1.png';" data-origin-width="2022" data-origin-height="1098"></figure>

이번엔 두개의 동작 모두 성공하였습니다.&nbsp;
<pre><code>insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT (room_seq)
DO UPDATE SET
 column_1 = 'data_1',
 column_2 = 'data_1'
;</code></pre>

저의 경우 충돌이 예상되는 room_seq 컬럼을 on conflict를 주고 실행하다 conflict가 발생하는 상황에는 DO 다음에 있는 행위를 하라는 의미의 SQL을 작성하였습니다.

<hr contenteditable="false" data-ke-type="horizontalRule" data-ke-style="style3">

근데 보통 jdbcTemplate이나 native query, mybatis 등 쿼리문을 직접 작성하여 삽입할 데이터를 바인딩할 때, values 절에다가만 기재합니다.

저렇게 update 문에도 하나하나 적어줄 수 있지만 dbms가 이러한 키워드를 제공합니다. (excluded)
<pre><code>insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT (room_seq)
DO UPDATE SET
 column_1 = excluded.column_1,
 column_2 = excluded.column_2
;</code></pre>
excluded는 마치 특정 참조처럼 동작하여 column1, column2에 삽입하려던 데이터를 바인딩해줍니다.


<hr contenteditable="false" data-ke-type="horizontalRule" data-ke-style="style3">
위의 방법은 컬럼 기준으로 동작하는 것이고 컬럼이 여러가지에 묶여 SQL문이 복잡하게 보인다면 constraint로 깔끔하게 줄일 수도 있습니다.&nbsp;
<pre><code>insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT ON CONSTRAINT room_new_data_pkey
DO UPDATE SET
 column_1 = excluded.column_1,
 column_2 = excluded.column_2
;</code></pre>
위의 room_new_data_pkey는 테이블을 만들때 기본으로 생성하는 primary key의 name입니다. 따라서 해당 제약조건이 위배되었을 경우 dbms는 이를 conflict라고 판단하여 아래의 update문을 실행할 수 있습니다.

<hr contenteditable="false" data-ke-type="horizontalRule" data-ke-style="style3">
DO NOTHING 이라는 행위도 있는데 참고삼아 한번 알아보겠습니다.
<pre data-ke-language="javascript"><code>insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT ON CONSTRAINT room_new_data_pkey
DO NOTHING;</code></pre>
DO NOTHING의 경우 conflict에 대한 제약조건의 위배가 발동되었을 경우, 아무행위도 하지 않습니다.
따라서 최초로 삽입되는 경우의 insert 쿼리만 허용한다면 해당 조건은 괜찮을 것 같습니다. 해당 쿼리가 어떤 요구사항을 갖는지에 따라 이러한 clause의 사용이 의미가 있을지 없을지가 결정이 된다고 생각하고 있습니다.

<hr contenteditable="false" data-ke-type="horizontalRule" data-ke-style="style3">

DO NOTHING의 경우에 처음에는 아무런 행위도 하지 않는다는 것이 조금 껄끄럽다고 생각했었는데. insert 쿼리의 동시성 이슈가 발생하는 상황에서 어느 비즈니스를 가져가느냐에 따라 다른 것 같네요.&nbsp;

감사합니다.</div></div>

코딩하는 흑구

안녕하세요.
 
이번 주제는 postgresql의 upsert를 구현해보는 포스팅입니다.
 
흔히 jpa를 쓸때 save() 메소드는 있으면 insert, 없으면 update를 한다고 알고 있는데요. 
 
JPA는 대략 아래처럼 SimpleJpaRepository에 구현되어 있습니다.
@Transactional
@Override
public <S extends T> S save(S entity) {
 Assert.notNull(entity, "Entity must not be null.");
 if (entityInformation.isNew(entity)) {
 em.persist(entity);
 return entity;
 } else {
 return em.merge(entity);
 }
}
entity 정보가 isNew에 참이면 persist - 등록하고 아니면 merge를 하게 됩니다. 이건 이제 JPA로 넘어온 어플리케이션 영역이고, sql 단에서는 어떻게 이것을 구현할 수 있을지 알아보겠습니다.
 
 
우선 저는 두개의 테이블을 설계하였습니다.
 
create table room (
 seq serial primary key,
 column_1 varchar(10),
 column_2 varchar(10),
 column_3 varchar(10),
 column_4 varchar(10)
);
create table room_new_data (
 room_seq integer
 primary key
 references room,
 column_1 varchar(10),
 column_2 varchar(10)
);
메인 테이블인 room과 room의 primary key를 참조하는 room_new_dat 테이블입니다. 
 
room 테이블은 기존에 별도의 데이터가 아래와 같이 들어있습니다.
이제 room 테이블의 레코드를 채웠으니 room_new_data가 room 테이블을 참조할 데이터가 생겼습니다.
 
근데.. 단일 스레드가 아닌 멀티스레드로 동작을 하다보니 다음과 같은 문제가 벌어졌습니다.
동일한 시간에 두개의 세션에서 다음과 같은 insert SQL을 동시에 날렸다면.. 먼저 커밋된 Transaction A가 정상적으로 커밋되고 Transaction B에서는 Error가 발생하였습니다. 재시도를 해도 동일한 에러메시지가 노출되어 insert 구문을 완성할 수 없습니다.
 
현재 저의 요구사항은 어쨌든 가장 최근 정보로 column이 들어가야하는데 거의 동시에 두개의 트랜잭션이 데이터를 삽입하고자 하니 primary key에 대한 제약조건 에러가 발생하게 됩니다.
 
이때 Transaction B의 데이터가 최종적으로 select SQL에서 노출되게 하기 위해서는 어떻게 해야할까요? DBMS가 insert 쿼리를 받는 순간 위의 JPA 로직처럼 개발자가 제어할 수 있는 방법은 더이상 없습니다. 단순한 Insert 쿼리로는요.
 
이러한 제어를 위해 On Conflict 문이 존재합니다. 쿼리를 아래와 같이 수정해보겠습니다.
이번엔 두개의 동작 모두 성공하였습니다. 
insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT (room_seq)
DO UPDATE SET
 column_1 = 'data_1',
 column_2 = 'data_1'
;
 
저의 경우 충돌이 예상되는 room_seq 컬럼을 on conflict를 주고 실행하다 conflict가 발생하는 상황에는 DO 다음에 있는 행위를 하라는 의미의 SQL을 작성하였습니다.
 
 
근데 보통 jdbcTemplate이나 native query, mybatis 등 쿼리문을 직접 작성하여 삽입할 데이터를 바인딩할 때, values 절에다가만 기재합니다.
 
저렇게 update 문에도 하나하나 적어줄 수 있지만 dbms가 이러한 키워드를 제공합니다. (excluded)
insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT (room_seq)
DO UPDATE SET
 column_1 = excluded.column_1,
 column_2 = excluded.column_2
;
excluded는 마치 특정 참조처럼 동작하여 column1, column2에 삽입하려던 데이터를 바인딩해줍니다.
 
 
위의 방법은 컬럼 기준으로 동작하는 것이고 컬럼이 여러가지에 묶여 SQL문이 복잡하게 보인다면 constraint로 깔끔하게 줄일 수도 있습니다. 
insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT ON CONSTRAINT room_new_data_pkey
DO UPDATE SET
 column_1 = excluded.column_1,
 column_2 = excluded.column_2
;
위의 room_new_data_pkey는 테이블을 만들때 기본으로 생성하는 primary key의 name입니다. 따라서 해당 제약조건이 위배되었을 경우 dbms는 이를 conflict라고 판단하여 아래의 update문을 실행할 수 있습니다.
 
DO NOTHING 이라는 행위도 있는데 참고삼아 한번 알아보겠습니다.
insert into room_new_data (room_seq, column_1, column_2)
values (1, 'data_1', 'data_1')
ON CONFLICT ON CONSTRAINT room_new_data_pkey
DO NOTHING;
DO NOTHING의 경우 conflict에 대한 제약조건의 위배가 발동되었을 경우, 아무행위도 하지 않습니다.
따라서 최초로 삽입되는 경우의 insert 쿼리만 허용한다면 해당 조건은 괜찮을 것 같습니다. 해당 쿼리가 어떤 요구사항을 갖는지에 따라 이러한 clause의 사용이 의미가 있을지 없을지가 결정이 된다고 생각하고 있습니다.
 
 
DO NOTHING의 경우에 처음에는 아무런 행위도 하지 않는다는 것이 조금 껄끄럽다고 생각했었는데. insert 쿼리의 동시성 이슈가 발생하는 상황에서 어느 비즈니스를 가져가느냐에 따라 다른 것 같네요. 
 
감사합니다.

[PostgreSQL] Insert On Conflict (jpa save / upsert 구현)

댓글