Postgresql

使用 PostgreSQL 中的歷史記錄更新版本化行表

  • March 5, 2022

我有一個版本化行的主表：

CREATE TABLE master (
   id SERIAL PRIMARY KEY,
   rec_id integer, 
   val text, 
   valid_on date[], 
   valid_during daterange
);

INSERT INTO master (rec_id, val, valid_on, valid_during) VALUES
   (1, 'a', '{2015-01-01,2015-01-05}', '[2015-01-01,infinity)'),
   (2, 'b', '{2015-01-01,2015-01-05}', '[2015-01-01,infinity)'),
   (3, 'c', '{2015-01-01,2015-01-05}', '[2015-01-01,infinity)');

SELECT * FROM master ORDER BY rec_id, id;
/*
    id | rec_id | val |        valid_on         |     valid_during
   ----+--------+-----+-------------------------+-----------------------
     1 |      1 | a   | {2015-01-01,2015-01-05} | [2015-01-01,infinity)
     2 |      2 | b   | {2015-01-01,2015-01-05} | [2015-01-01,infinity)
     3 |      3 | c   | {2015-01-01,2015-01-05} | [2015-01-01,infinity)
*/

rec_id是記錄的自然鍵，是valid_on記錄有效的日期數組，valid_during是描述記錄有效時間間隔的日期範圍。rec_id（如果沒有具有更新值的相同記錄，則 valid_during 的上限為“無窮大” valid_on。）

給定第二張更新記錄表，以及每條記錄有效的新日期：

CREATE TABLE updates (id SERIAL PRIMARY KEY, rec_id integer, val text, valid_on date); 
INSERT INTO updates (rec_id, val, valid_on) VALUES
(1, 'a', '2015-01-03'), -- (1) same "val" for id 1, just add valid_on date
(2, 'd', '2015-01-06'), -- (2) different val for id 2,
(3, 'e', '2015-01-03'); -- (3) different val for id 3 with new date 
                       --     intersecting old date range

SELECT * FROM updates;
/*
    id | rec_id | val |  valid_on
   ----+--------+-----+------------
     1 |      1 | a   | 2015-01-03
     2 |      2 | d   | 2015-01-06
     3 |      3 | e   | 2015-01-03
*/

我想插入/更新主表以結束這樣的事情：

-- The goal
SELECT rec_id, val, valid_on, valid_during FROM master ORDER BY rec_id, id;
/*
    rec_id | val |        valid_on                    |     valid_during
   --------+-----+------------------------------------+-----------------------
     1     | a   | {2015-01-01,2015-01-05,2015-01-03} | [2015-01-01,infinity)
     2     | b   | {2015-01-01,2015-01-05}            | [2015-01-01,2015-01-06)
     2     | d   | {2015-01-06}                       | [2015-01-06,infinity)
     3     | c   | {2015-01-01}                       | [2015-01-01,2015-01-03)
     3     | e   | {2015-01-03}                       | [2015-01-03,2015-01-05)
     3     | c   | {2015-01-05}                       | [2015-01-05,infinity)
*/

具體來說：

  • 如果主表中存在新記錄rec_id且具有相同的val，但新valid_on日期不在主表的valid_on數組中，只需將新日期添加到主表的valid_on欄位（參見rec_id1）
  • 如果新記錄rec_id存在不同的val，則將新記錄插入主表。主表中的舊記錄的valid_during值應在新記錄的日期結束valid_on（參見rec_id2）
  • 如果新記錄的valid_on日期與舊記錄的valid_during範圍相交，則舊記錄應出現在更新記錄的“兩側”（見rec_id3）

我可以到達那裡的大部分路。第一種情況很簡單：我們只需要更新主表中的欄位（我們將在單獨的步驟中暫時valid_on擔心該欄位）：valid_during

UPDATE master m
SET valid_on = m.valid_on || u.valid_on
FROM updates u
WHERE m.rec_id = u.rec_id 
   AND m.val = u.val 
   AND NOT m.valid_on @> ARRAY[u.valid_on];

SELECT * FROM master ORDER BY rec_id, id;
/*
    id | rec_id | val |              valid_on              |     valid_during
   ----+--------+-----+------------------------------------+-----------------------
     1 |      1 | a   | {2015-01-01,2015-01-05,2015-01-03} | [2015-01-01,infinity)
     2 |      2 | b   | {2015-01-01,2015-01-05}            | [2015-01-01,infinity)
     3 |      3 | c   | {2015-01-01,2015-01-05}            | [2015-01-01,infinity)
*/

對於案例#2，我們可以做一個簡單的插入：

INSERT INTO master (rec_id, val, valid_on)
SELECT u.rec_id, u.val, ARRAY[u.valid_on]
FROM updates u 
   LEFT JOIN master m ON u.rec_id = m.rec_id AND u.val = m.val
WHERE m.id IS NULL;

SELECT * FROM master ORDER BY rec_id, id;
/*
    id | rec_id | val |              valid_on              |     valid_during
   ----+--------+-----+------------------------------------+-----------------------
     1 |      1 | a   | {2015-01-01,2015-01-05,2015-01-03} | [2015-01-01,infinity)
     2 |      2 | b   | {2015-01-01,2015-01-05}            | [2015-01-01,infinity)
     4 |      2 | d   | {2015-01-06}                       |
     3 |      3 | c   | {2015-01-01,2015-01-05}            | [2015-01-01,infinity)
     5 |      3 | e   | {2015-01-03}                       |
*/

現在，我們可以valid_during通過加入一個子查詢來更正範圍，該子查詢使用一個視窗函式來檢查具有相同記錄的下一個有效日期rec_id：

-- Helper function...
CREATE OR REPLACE FUNCTION arraymin(anyarray) 
RETURNS anyelement AS $$ 
   SELECT min($1[i]) 
   FROM generate_series(array_lower($1,1), array_upper($1,1)) g(i); 
$$ language sql immutable strict; 


UPDATE master m
SET valid_during = daterange(arraymin(valid_on), new_valid_until)
FROM (
   SELECT
       id,
       lead(arraymin(valid_on), 1, 'infinity'::date)
       OVER (partition by rec_id ORDER BY arraymin(valid_on)) AS new_valid_until
   FROM master ) t
WHERE
   m.id = t.id;

SELECT * FROM master ORDER BY rec_id, id;
/*
    id | rec_id | val |              valid_on              |      valid_during
   ----+--------+-----+------------------------------------+-------------------------
     1 |      1 | a   | {2015-01-01,2015-01-05,2015-01-03} | [2015-01-01,infinity)
     2 |      2 | b   | {2015-01-01,2015-01-05}            | [2015-01-01,2015-01-06)
     4 |      2 | d   | {2015-01-06}                       | [2015-01-06,infinity)
     3 |      3 | c   | {2015-01-01,2015-01-05}            | [2015-01-01,2015-01-03)
     5 |      3 | e   | {2015-01-03}                       | [2015-01-03,infinity)
*/

這就是我卡住的地方：rec_id1 和 2 正是我想要的，但rec_id需要再次插入 3 才能在“2015-01-05”上顯示有效。我似乎無法圍繞數組操作來執行插入操作。關於不涉及取消嵌套主表的方法的任何想法？或者這是這裡唯一/最好的方法？

我正在使用 PostgreSQL 9.3（但如果在較新的版本中有一種優雅的方式可以升級到 9.4，我會很高興地升級到 9.4）。

第一種情況

你似乎忘記了valid_during範圍。正如您的第三種情況所暗示的那樣，每個 可以有多個條目(rec_id, val)，因此您必須選擇正確的條目：

UPDATE master m
SET    valid_on = f_array_sort(m.valid_on || u.valid_on) -- sorted array, see below
FROM   updates u
WHERE  m.rec_id = u.rec_id 
AND    m.valid_during @> u.valid_on  -- additional check!
AND    m.val = u.val 
AND    NOT m.valid_on @> ARRAY[u.valid_on];

我假設每個現有的始終涵蓋整個rec_id可能的日期範圍，並且valid_during不應重疊rec_id，否則您將不得不做更多。

安裝附加模組後btree_gist，添加排除約束以排除重疊的日期範圍（如果您還沒有）：

ALTER TABLE master ADD CONSTRAINT EXCLUDE
USING gist (rec_id WITH =, valid_during WITH &&)  -- disallow overlap

實現這一點的 GiST 索引也是查詢的完美匹配。細節：

第 2 / 第 3 種情況

假設每個日期範圍都從（現已排序！）數組中的最小日期開始：lower(m.valid_during) = m.valid_on[1]。我會通過CHECK約束來強制執行。

這裡我們需要創建一兩個新行 在第二種情況下，縮小舊行的範圍並插入一個新行就足夠了 在第三種情況下，我們用數組和範圍的左半部分更新舊行，插入新行，最後插入數組和範圍的右半部分。

輔助函式

為了簡單起見，我引入了一個新的約束：每個數組都是排序的。使用這個輔助函式：

CREATE OR REPLACE FUNCTION f_array_sort(anyarray) 
 RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY(SELECT unnest($1) ORDER BY 1)$$;

我們不再需要您的輔助函式arraymin()，但可以簡化為：

CREATE OR REPLACE FUNCTION f_array_min(anyarray) 
 RETURNS anyelement LANGUAGE sql IMMUTABLE AS
$$SELECT min(a) FROM unnest($1) a$$;

另外兩個在給定元素處拆分數組的左半部分和右半部分：

-- split left array at given element
CREATE OR REPLACE FUNCTION f_array_left(anyarray, anyelement) 
 RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY(SELECT * FROM unnest($1) a WHERE a < $2 ORDER BY 1)$$;

-- split right array at given element
CREATE OR REPLACE FUNCTION f_array_right(anyarray, anyelement) 
 RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY(SELECT * FROM unnest($1) a WHERE a >= $2 ORDER BY 1)$$;

詢問

這完成了所有其餘的工作：

WITH u AS (  -- identify candidates
  SELECT m.id, rec_id, m.val, m.valid_on, m.valid_during
       , u.val AS u_val, u.valid_on AS u_valid_on
  FROM   master  m
  JOIN   updates u USING (rec_id)
  WHERE  m.val <> u.val
  AND    m.valid_during @> u.valid_on
  FOR    UPDATE  -- lock for update
  )
, upd1 AS (  -- case 2: no overlap, no split
  UPDATE master m  -- shrink old row
  SET    valid_during = daterange(lower(u.valid_during), u.u_valid_on)
  FROM   u
  WHERE  u.id = m.id
  AND    u.u_valid_on > m.valid_on[array_upper(m.valid_on, 1)]
  RETURNING m.id
  )
, ins1 AS (  -- insert new row
  INSERT INTO master (rec_id, val, valid_on, valid_during)
  SELECT u.rec_id, u.u_val, ARRAY[u.u_valid_on]
       , daterange(u.u_valid_on, upper(u.valid_during))
  FROM   upd1
  JOIN   u USING (id)
  )
, upd2 AS (  -- case 3: overlap, need to split row
  UPDATE master m  -- shrink to first half
  SET    valid_during = daterange(lower(u.valid_during), u.u_valid_on)
       , valid_on = f_array_left(u.valid_on, u.u_valid_on)
  FROM   u
  LEFT   JOIN upd1 USING (id)
  WHERE  upd1.id IS NULL  -- all others
  AND    u.id = m.id
  RETURNING m.id, f_array_right(u.valid_on, u.u_valid_on) AS arr_right
  )
INSERT INTO master (rec_id, val, valid_on, valid_during)
         -- new row
SELECT u.rec_id, u.u_val, ARRAY[u.u_valid_on]
    , daterange(u.u_valid_on, upd2.arr_right[1])
FROM   upd2
JOIN   u USING (id)
UNION ALL  -- second half of old row
SELECT u.rec_id, u.val, upd2.arr_right
    , daterange(upd2.arr_right[1], upper(u.valid_during))
FROM   upd2
JOIN   u USING (id);

db<>fiddle here

舊sqlfiddle

筆記

在接觸這個之前，您需要了解數據修改 CTE（可寫 CTE）的概念。從您提供的程式碼來看，您了解 Postgres。

FOR UPDATE是為了避免並發寫訪問的競爭條件。如果您是唯一寫入表的使用者，則不需要它。

我拿了一張紙畫了一個時間表，以免迷失在這一切中。

每行只更新/插入一次，操作簡單粗略優化。沒有昂貴的視窗功能。這應該表現良好。在任何情況下都比您以前的方法快得多。

如果您對and使用不同的列名，這將不會那麼混亂，它們是相關但不同的東西。u.valid_on``m.valid_on

RETURNING我在CTE upd2:的子句中計算拆分數組的右半部分f_array_right(u.valid_on, u.u_valid_on) AS arr_right，因為我在下一步中需要它多次。這是節省更多 CTE 的（合法）技巧。

至於*“不涉及取消嵌套主表”的解決方案：您必須以*任何一種方式取消嵌套數組valid_on，拆分它，至少只要它沒有排序。此外，無論如何，您的輔助函式arraymin()已經取消嵌套。

引用自：https://dba.stackexchange.com/questions/95845

相關問答

Postgresql

您如何使用 CTE 編寫有序的多插入？

  • August 18, 2022
檢視問答
Postgresql

使用 READ COMMITTED 對並發寫入安全的查詢，對 SERIALIZABLE 隔離級別也安全？

  • August 7, 2022
檢視問答
Postgresql

索引不用於日期範圍條件

  • April 5, 2022
檢視問答
Postgresql

SELECT 中的廉價函式如何使整個查詢變慢？

  • March 27, 2022
檢視問答
Postgresql

在 UPDATE 中的 CTE 中是否需要顯式 FOR UPDATE 鎖？

  • March 15, 2022
檢視問答
Postgresql

postgresql 遞歸更新導致順序掃描

  • February 24, 2022
檢視問答