PostgreSQL

前言 這篇文章主要是探討 PostgreSQL 的 Constraints 情境下的幾種 ACTION，以作為一個簡單的學習紀錄 何謂 Constraint？ 眾所皆知，Foreign key(FK) 是 Table 中一個或一組用來關聯另一張表的 Primary key(PK) 的欄位，其中： 關聯另張表的表叫做 referencing table 或 child table 被關聯的表則稱為 referenced table 或 parent table 當 parent table 的某欄位和 child table 的某欄位有關聯時，例如: PK(Primary Key)和 FK(Foreign Key)的關係時，此時對 parent table 這個欄位做出修改(update)或刪除(delete)的操作，勢必會連帶影響到 child table。此時，child table 就需要明確的告訴 parent table，該筆操作對 child table的欄位要有什麼影響(ACTION)? 所以如何決定這些跨表參照欄位的存亡關係，就取決於 Constraint 的設定．換言之，SQL 透過 Constraint 的限制來協助我們維持父子表之間的參照完整性(referential integrity) 而 Constraint 又分為被更新(ON_UPDATE)以及被刪除(ON_DELETE)時，分別有對應的影響(ACTION)可以設定，實務上有 PK 和 FK 關係的欄位，大多會用 ON_DELETE居多． 在 PostgreSQL 中的語法結構大致如下(參考該網站)： [CONSTRAINT fk_name] FOREIGN KEY(fk_columns) REFERENCES parent_table(parent_key_columns) [ON DELETE delete_action] [ON UPDATE update_action] ACTION 有哪些？ 可設置的 delete_action 和 update_action 都稱為 ACTION，分為以下幾種:...

前言 前陣子因為某接案需求要研究 Medusa 這個開源的電商專案，發現底層設計的 Event Architecture 有參考到 Transactionally Staged Job Drain 這個事務處理機制，因此拜讀了一下這篇 2017 年的文章，當時作者也在 Hacker News 上與網友有不少討論，相信對於研究底層的設計可以讓自己功力提升，因而有了這篇文章的誕生． 問題描述：Event Queue + ACID 聲明：本篇取材自 Transactionally Staged Job Drain 的整理消化，圖片皆來自於此． 文章探討的情境是: 任務佇列(Job Queue)消化的速度太快，導致後台服務(background worker)在該事務 commit 前就嘗試取用，進而無法訪問到預期可用的資料，也就是如何搭配 ACID 與 Event Queue 解決上述事務的問題． 底下是一個顯而易見的例子： BEGIN TRANSACTION; /* db_op1 */ INSERT INTO USER(id, name, age, email) VALUES (1, 'Madi', 27, 'example@gamil.com'); /* queue_job */ -- Some enqueuer worker /* db_op2 */ INSERT INTO USER(id, name, age, email) VALUES (2, 'Kevin', 20, 'example2@gamil....

前言 最近因為工作需求碰到地理空間搜尋與儲存的技術，其中包含 PostGIS 這個 PostgreSQL 實作的地理物件函式庫，裏頭涵蓋各地區座標系統的 SRID(Spatial Reference Identifier)，令人欣慰的是台灣地區常用的 TWD97 與 TWD67 兩種座標系統也涵蓋如此，但在實作過程發現 TWD67 的座標點位有高達數百公尺的誤差，追溯源頭後意外發現，原來是 PostGIS 內的參數設定有誤，解決之後就順手紀錄一下踩坑的過程。 座標參考系格式 - WKT vs proj.4 vs SRID 開始以前要先提一下地理知識，因為地球是圓的不是平的，所以要將一個橢圓形的物件投影到紙面上勢必會有投影扭曲的問題，而為了降低投影扭曲的影響，學術上有很多轉換方式，包含等面積投影、等角投影(麥卡托投影)…，但不管選用哪一種投影都只能降低扭曲的幅度，並不存在絕對好的投影方式。 為了描述座標參考系(CRS, Coordinate Reference System)，訂了幾種常用的描述格式，包含: WKT(Well-Known Text): 是一種描述點、線、多邊形、幾何形狀的文本格式，例如: POINT(121.5 22.5)，相較於其他格式更 human-readable。 舉例來說，Google map 使用的坐標系 WGS84 的 WKT 格式: GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]] 在 PostGIS 中被應用在 spatial_ref_sys 表內的 srtext 欄位中 proj.4: 是另一種描述投影的文本格式，例如: +proj=tmerc +lat_0=0 +lon_0=121 +k=0.9999 +x_0=250000 +y_0=0 +ellps=aust_SA +units=m +no_defs，用幾個參數如橢圓角度、基準、長度單位…來定義投影的方式。 Proj.4 是開源社群常用的地圖投影資料庫，據我所知 PostGIS 和 GDAL/OGR 這些軟體都直接或間接的使用到 Proj.4 資料庫 在 PostGIS 中被應用在 spatial_ref_sys 表內的 proj4text 欄位中 SRID:...