CREATE TABLE IF NOT EXISTS public.table_monitor
(
table_name text COLLATE pg_catalog."default" NOT NULL,
update_at timestamp without time zone NOT NULL DEFAULT now(),
update_count bigint NOT NULL DEFAULT 0,
CONSTRAINT table_monitor_pkey PRIMARY KEY (table_name)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.table_monitor
OWNER to XXX;
-- 這一段只要執行一次
CREATE OR REPLACE FUNCTION public.table_monitor_trigger_fnc()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
Update public.table_monitor Set update_count = update_count + 1, update_at = now() Where table_name = TG_TABLE_NAME;
RETURN NEW;
END;
$BODY$;
ALTER FUNCTION public.table_monitor_trigger_fnc()
OWNER TO XXX;
-- 對於被監控的 table, 要執行以下的指令，記得 monitor_table_name 要改為 table 的名字(要符合大小寫)
INSERT INTO public.table_monitor (table_name, update_at, update_count) VALUES ('monitor_table_name', '2000-01-01', 0) ON CONFLICT DO NOTHING;
CREATE OR REPLACE TRIGGER table_monitor_trigger
AFTER INSERT OR DELETE OR UPDATE
ON monitor_table_name
FOR EACH STATEMENT
EXECUTE FUNCTION public.table_monitor_trigger_fnc();
監控用的程式碼:
public static void UpdateTableCache()
{
//標記自已是正在執行的 Thread. 讓舊的 Thread 在執行完畢之後, 應該會自動結束. 以防舊的 Thread 執行超過 10 秒.
CurrentThreadId = System.Threading.Thread.CurrentThread.ManagedThreadId;
try
{
//確認自已是正在執行的 Thread, 重覆執行. (另一個 Thread 插入執行)
while (CurrentThreadId == System.Threading.Thread.CurrentThread.ManagedThreadId)
{
LastUpdateDate = DateTime.Now;
foreach (var dbId in CachedDbs)
{
var sql = @"select * from table_monitor";
var dt = Su.PgSql.DtFromSql(sql, dbId);
foreach (DataRow row in dt.Rows)
{
string changeId = row["update_count"].DBNullToDefault();
string CacheKey = TableCacheKey(dbId, row["table_name"].DBNullToDefault());
ObjectCache cache = MemoryCache.Default;
string OldValue = (string)cache[CacheKey];
if (OldValue == null)
{
cache.Set(CacheKey, changeId, DateTime.MaxValue);
}
else
{
if (changeId != OldValue)
{
cache.Remove(CacheKey);
cache.Set(CacheKey, changeId, DateTime.MaxValue);
}
}
}
}
//每兩秒檢查一次
System.Threading.Thread.Sleep(2000);
}
}
catch (Exception)
{
//依經驗, 只要 DB 能通, 這裡幾乎不會有問題, 所以這裡暫時不處理, 未來有問題時可以考慮寫入文字檔比較好.
}
}