[SQL] PostgreSQL의 버전 - 만 SQL을 사용하여 반환 사전 UPDATE 열 값

PostgreSQL의 버전 - 만 SQL을 사용하여 반환 사전 UPDATE 열 값

나는 관련 질문이 있습니다, 그러나 이것은 내 퍼즐의 또 다른 부분이다.

트리거를 사용하지 않고 (나 저장 프로 시저,이나 다른 추가 비 SQL / - 쿼리 개체) - I가 업데이트 된 행에서 열의 이전 값을 좀하고 싶습니다.

내가 가진 쿼리는 다음과 같다 :

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                        SELECT trans_nbr
                          FROM my_table
                         GROUP BY trans_nbr
                        HAVING COUNT(trans_nbr) > 1
                         LIMIT our_limit_to_have_single_process_grab
                       )
RETURNING row_id;

내가 서브 쿼리의 끝에 "MY_TABLE ON UPDATE은"할 수 있다면, 그 디바인 수 (내 기타 질문 / 문제를 해결하는) 것입니다. 그러나, 그것은하지 않습니다 작업이있을 수 없습니다 AND (trans_nbr의의 COUNT를 파악에 필요한)는 "GROUP BY". 그럼 난 그냥 그 trans_nbr의 걸릴 수와 (곧-BE-) 전 processing_by 값을 얻기 위해 먼저 쿼리를 수행.

내가 좋아하는 일을 시도했다 :

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table old_my_table
     JOIN (
             SELECT trans_nbr
               FROM my_table
           GROUP BY trans_nbr
             HAVING COUNT(trans_nbr) > 1
              LIMIT our_limit_to_have_single_process_grab
          ) sub_my_table
       ON old_my_table.trans_nbr = sub_my_table.trans_nbr
    WHERE     my_table.trans_nbr = sub_my_table.trans_nbr
      AND my_table.processing_by = old_my_table.processing_by
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by

그러나 그것은 작동하지 않을 수 있습니다; 조인 표시는 외부 old_my_table 아니다; 복귀 절은 장님이다.

나는이 만든 모든 시도의 손실 계수 이후 긴했습니다; 말 그대로 시간이 연구되고있다.

서브 쿼리가 발생했을 때 만 행 및 - - 난 그냥 내 하위 쿼리의 행을 잠글 수있는 방탄 방법을 찾을 수 있다면 나는 사라질 것 피하기 위해 노력하고있어 모든 동시성 문제를 ...

UPDATE : [닦음 EGG OFF FACE] 난의 제네릭이 아닌 코드에서 오타했다 좋아, 그래서 위의 내가 "하지 작동합니까"쓴; 그것은 않습니다 ... 그것은 것 진술 아래 어윈 Brandstetter 덕분에, 나는 다시 한 그것을 (밤의 수면, 상쾌 눈, 및 bfast에 대한 바나나 후). 그것은 이러한 종류의 솔루션을 찾기 위해 너무 오래 / 하드 걸렸다 때문에, 아마 내 당황은 가치가있다? 적어도이 ... 이제 후손을 위해 SO에 있습니다>

내가 지금 (작품 것을)가 무엇과 같이이다 :

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table AS old_my_table
    WHERE trans_nbr IN (
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(*) > 1
                           LIMIT our_limit_to_have_single_process_grab
                       )
      AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

카운트 (*)는 내 다른 (위 링크) 질문의 댓글에서 Flimzy에서 제안 당이다. (I 필요 이상으로 특정 하였다. 이때.])

제대로 동시성 심지어 비 차단 버전을 구현하기위한 내 다른 질문을 참조하십시오; 업데이 트에서 이전 및 새 값을 얻을 수있는 방법이 쿼리는 단지 쇼, 나쁜 / 잘못된 동시성 비트를 무시합니다.

해결법

1. ==============================

   1.수동 설명 :

   수동 설명 :

   강조 광산. 돌아 오는 절에서 이전 행에 액세스 할 수있는 방법은 없습니다. 당신은 @Flimzy 및 @wildplasser은 주석으로 트랜잭션에 싸여, 또는 @MattDiPasquale 게시 등의 CTE에 싸여 업데이트하기 전에 트리거 또는 별도의 SELECT으로 그렇게 할 수 있습니다.

   그러나 완벽하게 정상적으로 당신이 FROM 절에있는 테이블의 다른 인스턴스에 가입하면 작품을 달성하려고하는 무엇을 :

   UPDATE tbl x
   SET    tbl_id = 23
        , name = 'New Guy'
   FROM   tbl y                -- using the FROM clause
   WHERE  x.tbl_id = y.tbl_id  -- must be UNIQUE NOT NULL
   AND    x.tbl_id = 3
   RETURNING y.tbl_id AS old_id, y.name AS old_name
           , x.tbl_id          , x.name;
   

   보고:

    old_id | old_name | tbl_id |  name
   --------+----------+--------+---------
     3     | Old Guy  | 23     | New Guy
   

   SQL 바이올린.

   나는 8.4에서 9.6로 PostgreSQL의 버전이 테스트.

   이 INSERT에 대해 서로 다른입니다 :

   동시 쓰기 작업과 경쟁 조건을 방지하는 방법에는 여러 가지가 있습니다. 단순한는 느리고 확인 (하지만 비싼) 방법은 SERIALIZABLE 격리 수준으로 거래를 실행하는 것입니다.

   BEGIN ISOLATION LEVEL SERIALIZABLE;
   UPDATE ..;
   COMMIT;
   

   그러나 아마 잔인한 사람입니다. 그리고 당신은 당신이 직렬화 실패를 얻는 경우에 작업을 반복 할 준비를해야 할 것입니다. 간단하고 빠른 (동시 쓰기로드와 마찬가지로 신뢰할 수) 업데이트 할 수있는 하나의 행에 명시 적으로 잠금입니다 :

   UPDATE tbl x
   SET    tbl_id = 24
        , name = 'New Gal'
   FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
   WHERE  x.tbl_id = y.tbl_id
   RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name;
   

   이 관련된 질문 아래에 더 설명, 예제 및 링크 :

2. ==============================

   2.당신은 SELECT 하위 쿼리를 사용할 수 있습니다.

   당신은 SELECT 하위 쿼리를 사용할 수 있습니다.

   예 : 이전 값을 반환하는 사용자의 이메일을 업데이트합니다.

3. ==============================

   3.@MattDiPasquale에 의해 제안 된 같은 CTE의 변형도 작동합니다. CTE를 I의 편안한 수단으로, 더 명확하지만 것입니다 :

   @MattDiPasquale에 의해 제안 된 같은 CTE의 변형도 작동합니다. CTE를 I의 편안한 수단으로, 더 명확하지만 것입니다 :

   WITH sel AS (
      SELECT tbl_id, name FROM tbl WHERE tbl_id = 3  -- assuming unique tbl_id
      )
   , upd AS (
      UPDATE tbl SET name = 'New Guy' WHERE tbl_id = 3
      RETURNING tbl_id, name
      )
   SELECT s.tbl_id AS old_id, s.name As old_name
        , u.tbl_id, u.name
   FROM   sel s, upd u;
   

   테스트없이 나는이 작품을 주장 : SELECT 및 UPDATE가 동일한 데이터베이스 스냅 샷을 참조하십시오. 선택은 UPDATE가 정의하여 새 값을 반환하면서, (당신이 UPDATE와 CTE 후 CTE를 배치하는 경우에도) 이전 값을 반환 할 수밖에 없다. 짜잔.

   그러나 그것은 내 첫 번째 대답보다 느리게 될 것입니다.

4. ==============================

   4.이 딜레마에 직면했을 때 나는 테이블에 정크 열을 추가하고 내가 기록을 업데이트 할 때 그때는 (그때 반환하는) 정크 열에 이전 값을 복사합니다. 이 표를 조금 bloats하지만 대한 조인의 필요성을 방지 할 수 있습니다.

   이 딜레마에 직면했을 때 나는 테이블에 정크 열을 추가하고 내가 기록을 업데이트 할 때 그때는 (그때 반환하는) 정크 열에 이전 값을 복사합니다. 이 표를 조금 bloats하지만 대한 조인의 필요성을 방지 할 수 있습니다.

from https://stackoverflow.com/questions/7923237/return-pre-update-column-values-using-sql-only-postgresql-version by cc-by-sa and MIT license