PostgreSQL 的 update timestamp 探討

前言

在處理資料時,timestamp 是很重要的資料.想必大家也都會在建立table時會使用,並且會有類似created_at, updated_at.有的會用一個欄位,只放最新的變動時刻,這樣建立時也包含在內.也有使用兩個欄位分別對應建立與最新的變動時刻.

來看一下MySQL的方式

MySQL有提供很便利語法,可以參考這段例子:傳送門
可以看到最後的例子中,建立與變動的時刻有差異了.
MySQL在建立table時,欄位就可以指定 on update, 十分地便利.不愧為貼心小棉襖.

PostgreSQL的方式

create table t0105a (
  id int generated always as identity primary key
, val int
, ts timestamp default current_timestamp on update current_timestamp
, dt datetime default current_timestamp on update current_timestamp
);

ERROR:  42601: syntax error at or near "on"
LINE 4: , ts timestamp default current_timestamp on update current_t...

恩…很貼心的跟我們說哪裡語法錯了.
也就是說 PostgreSQL 不支援在欄位指定on update的語法.

自己寫個trigger

create table t0105a (
  id int generated always as identity primary key
, val int
, ts timestamp default current_timestamp
);

create or replace function track_t0105a_ts()
returns trigger language plpgsql as
$code$
begin
  new.ts = clock_timestamp();
  return new;
end;
$code$;

create trigger tri_bu_t0105a
before update 
on t0105a 
for each row
execute function track_t0105a_ts();

insert into t0105a(val) values
(1),(2),(3);

select *
  from t0105a;

 id | val |             ts             
----+-----+----------------------------
  1 |   1 | 2024-01-05 16:55:33.752251
  2 |   2 | 2024-01-05 16:55:33.752251
  3 |   3 | 2024-01-05 16:55:33.752251
(3 rows)

update t0105a
   set val = val + 1;

select *
  from t0105a;

 id | val |             ts             
----+-----+----------------------------
  1 |   2 | 2024-01-05 16:56:13.136066
  2 |   3 | 2024-01-05 16:56:13.136142
  3 |   4 | 2024-01-05 16:56:13.136156
(3 rows)

PostgreSQL 的 moddatetime extension

因為這個trigger 寫法很簡單,但是每次都要寫對應的,所以就提供了一個
extension 叫 moddatetime.提供了一個函數,可以輸入欄位名稱.

create extension moddatetime;

create table t0105b (
  id int generated always as identity primary key
, val int
, ts1 timestamp default clock_timestamp()
, ts2 timestamp default null
);

create trigger tri_bu_t0105b_ts2
before update
on t0105b
for each row
execute function moddatetime(ts2);

insert into t0105b(val) values
(1),(2),(3);

select *
  from t0105b;

 id | val |            ts1             | ts2  
----+-----+----------------------------+------
  1 |   1 | 2024-01-05 17:04:20.323484 | NULL
  2 |   2 | 2024-01-05 17:04:20.329817 | NULL
  3 |   3 | 2024-01-05 17:04:20.329836 | NULL
(3 rows)

update t0105b
   set val = val + 1;

select *
  from t0105b;

 id | val |            ts1             |           ts2            
----+-----+----------------------------+--------------------------
  1 |   2 | 2024-01-05 17:04:20.323484 | 2024-01-05 17:05:01.5984
  2 |   3 | 2024-01-05 17:04:20.329817 | 2024-01-05 17:05:01.5984
  3 |   4 | 2024-01-05 17:04:20.329836 | 2024-01-05 17:05:01.5984
(3 rows)

可以看到是使用current_timestamp的方式.
雖然減少寫trigger function.但是還是要指定trigger.提供了統一的函數.

設計哲學的差異

MySQL著重於平易近人,提供各種便利性.
PostgreSQL著重整體的統一性.
以這個例子來說,當我們想知道系統中有哪些欄位會有自動的補齊或是修正的功能在update時.
在PostgreSQL只要去查 information_schema 下的trigger,或是查pg_catalog.pg_trigger, (當然還要適當的join其他系統表,這裡先不展開.) 就能夠全部找到.
但是在MySQL的情況下,MySQL也是有trigger的功能的, 這時候除了查詢trigger以外,還要想到這個便利的 on update .
哪種方式較好? 這沒有優劣.只有我們對其理解更深,更能夠掌握,能駕馭,就都是好的.

雲端不能建立extension的情況

有些雲端服務,提供的PostgreSQL,會是固定不能自行建立extension.
這時候可以自行開發類似 moddatetime function 的 function.
回顧之前我寫的直轉橫的分享中,有使用了json_populate_record().
對應table的type, 而在trigger中會有 new 這個虛擬的record.
看看怎樣的組合運用.

create or replace function modts()
returns trigger
language plpgsql as
$code$
declare 
  colname name;
begin
  if (tg_nargs = 1) then
    colname = tg_argv[0];
  else
    raise exception 'modts(colname) (只)需要一個參數';
  end if;
  
  return json_populate_record(new, json_build_object(colname, clock_timestamp()));
end;
$code$;

create table t0105c (
  id int generated always as identity primary key
, val int
, ts1 timestamp default clock_timestamp()
, ts2 timestamp default null
);

create trigger tri_bu_t0105c_ts2
before update
on t0105c
for each row
execute function modts(ts2);

insert into t0105c(val) values
(1),(2),(3);

select *
  from t0105c;

 id | val |            ts1             | ts2  
----+-----+----------------------------+------
  1 |   1 | 2024-01-05 17:52:07.640035 | NULL
  2 |   2 | 2024-01-05 17:52:07.640646 | NULL
  3 |   3 | 2024-01-05 17:52:07.640658 | NULL
(3 rows)

update t0105c
   set val = val + 1;

select *
  from t0105c;
  
 id | val |            ts1             |            ts2             
----+-----+----------------------------+----------------------------
  1 |   2 | 2024-01-05 17:52:07.640035 | 2024-01-05 17:52:42.955641
  2 |   3 | 2024-01-05 17:52:07.640646 | 2024-01-05 17:52:42.957856
  3 |   4 | 2024-01-05 17:52:07.640658 | 2024-01-05 17:52:42.957914
(3 rows)

這樣我們就完成了一個通用的modts(colname)trigger function.

這樣是否較好?

通用的trigger function,這樣我們在查詢trigger時,可以看到使用的都是相同的.就不需要再去一一檢視.以軟體工程的一種角度來看,達到通用性,統一性.而且使用函數組合,感覺就很優雅.
但是! 優雅是要付出對應的代價的.雖然使用的資料量不大,但是畢竟也是要組合與展開.在大量資料需要更新的情況下,效能是否會比簡單粗暴的直接

new.ts = clock_timestamp();

為低呢?
我們其實不必非黑即白,當某些table確實需要注意效能時,就用自己的trigger function吧.當然我們取名時可以有好的規範,提高理解與管理便利性.而其他table 可以使用通用型的.

結語

PostgreSQL與MySQL都是很優秀的資料庫,裡面有許多功能,可以讓我們使用起來更靈活方便.