PGSQL16 新功能：GROUP BY 不用列出全部出現的 non-aggregation 欄位

MySQL/MariaDB 雖然超熱門，但有不少的 SQL 功能聽說高其他款的行為有點差異；例如 GROUP BY 的用法～

• GROUP BY list 的欄位，物以類聚
• SELECT list 套用 aggregation 的欄位，依照 GROUP BY 分類回傳結果並壓縮成一筆；window function 則是依照分類回傳但保持筆數（重複資料）
• SELECT list 中，既非 aggregation 欄位，又不是 GROUP BY list，卻又出現在 SQL 的欄位，資料會隨便亂回

對照之下，PGSQL 跟其他大品牌的關聯式資料庫，SQL 行為的規格會比較相近。

例如 GROUP BY 就比較標準：

• GROUP BY list 的欄位，物以類聚
• SELECT list 套用 aggregation 的欄位，依照 GROUP BY 分類回傳結果並壓縮成一筆；window function 則是依照分類回傳但保持筆數（重複資料）
• 「不允許」SELECT list 中，既非 aggregation 欄位，又不是 GROUP BY list，卻又出現在 SQL 的欄位

雖然有時令人稍微不習慣，但有時候又很需要的感覺：有偶爾的資料欄位之間有人為知道的關係（對 A 欄位做某種過濾或 JOIN 之後，B 欄位的值也會一樣均勻），但又不需要特地讓資料庫一直算（GROUP BY list 也要花力氣跑的），只想要某些欄位顯示出「一位代表」就好（因為預期都會長得一樣～）而不用寫落落長的 GROUP BY list。

到了 PGSQL16 ，就出一個很重要的功能～滿足這種不用每個欄位都放到 group by 的用途。

用 heredoc 在 PGSQL single-user mode 跑指令

 這只是一個刷存在感的雜記

在 PGSQL 裡面指定優先排序的清單

 這陣子遇到希望挑出特定內容往前排序的狀況。為了處理這個，想了一下子怎樣都要拆分資料做處理在合併，但寫好多行 script 處理還是有點痛苦。。。

不過～找了一陣子，總算讓我看到一個排序的奇妙範例。

長橫的跟長直的 postgres 測試表—VALUE() 跟 UNNEST()

純紀錄，沒什麼很大用處的小抄，但稍稍的方便作 demo。

PGSQL uuid 欄位搭配 hash index 草草筆記

UUID 是一個很像亂碼的東東，有些時候需要用他當作資料識別號。這時就要稍稍了解一下這個在 PGSQL 的加速方式。

在 PGSQL 把巢狀 JSON 攤平

儘管 PGSQL 已經提供超級全面的 JSON 功能存取內容（JSON operator、JSON path、JSON subscripting），但有時候考慮存取效率（JSON/JSONB 資料是整筆放到單一欄位，並且大多為 TOAST 壓縮結構，存取一個 attribute 的內部運作上跟存取一個欄位不太一樣），還是傾向拆解欄位好好的放資料。
目前還沒找到有超簡單攤平 JSON 的方法，不過在 PGSQL 倒是有系統性的手法展開欄位。
這篇筆記會分狀況說明拆解方式，並且拿一兩個開放資料的實際範例作拆解。

Note：這篇筆記使用套色標示～使用黑白螢幕（電子紙或是映像管～！？）的人要留意一下～

抓出 PGSQL 使用暫存表的 Session PID

 PGSQL 的暫存表（temp table）跟 Oracle 不同：PGSQL 的暫存表是 DB Session 私用的；暫存表的生命週期只在 Session 活動期間，登出後就在資料庫裡完全消失。

因此有時候遇到稍微惱人的使用狀況，像是開了很大的暫存表，或是活的很久的暫存表（常駐 AP 咬住的 DB Session），有辦法找出 DB Session 對應的暫存表的話，就蠻有用處的～

PGSQL 的 queryid 跟 planid 功能的外掛—pg_store_plans 筆記

 EDB/PGSQL 預設僅能透過 auto_explain 紀錄執行 SQL 的執行計畫到 DB Log 檔案內。

PGSQL 查看當前/歷史執行計畫的社群擴充外掛，可以直接以表查詢運行 SQL 的執行計畫，有以下兩個（截至 2022 年三月中為止）：

利用 Python3 內建 venv 管理 PGSQL PL/Python3 裡面可以使用的 Python 模組

使用 Python 有很多便利，但也很多困擾。。。其中之一就是 dependency hell。經典的圖就是 xkcd: Python Environment～

當然這狀況已經有許多手段可以預先規劃避免與處理，其中之一就是使用 virtualenv 製作隔離的 python runtime 環境。

那麼，當 Postgres 的內嵌 stored procdure PL/PythonU 遇到需要安裝模組要怎辦？

在 PGSQL 把 EAV 表格轉成 JSON 欄位與進一步攤平

Entity-Attribute-Value 型態（縮寫 EAV）的資料蠻常出現在實際應用上，最典型的就是資料分析時很多屬性標籤。

但是。。。PGSQL 其實不擅長對付這種屬性的資料。。。第二個無腦建議總是說：想辦法轉成關聯式結構的表格（第一個建議呢～就是建 GIN Index 之類的ㄅ～）

不過，這種屬性的資料正好可以用另一種主流資料儲存型態代換—JSON 型態。正好現在的 Postgres 也很擅長處理 JSON 資料。

另外值得提到的是，PGSQL 並不像 Cassandra 這種 Wide-Column Store 一樣可以開「很多」欄位，因此若 Attribute 標籤種類繁雜，就不是很適合在 PGSQL 開超多欄位的表格來存放。

因此這篇筆記嘗試把 EAV 轉換成 JSON 儲存的方式（測試版本：EDB PGSQL13，不過用到的功能基本上 PGSQL 9.5 以上都可以）。

另外，當標籤的類型很少，可以轉換成一般欄位的話（也就是關聯式表格），這篇筆記也嘗試演練一下～

如果看到這邊～還是不知道窩在講蝦米～請上網查一下 EAV pattern 與 Relational Model～

EDB 解決方案—EDB-Ansible 佈署 PostgreSQL 13 初步練習

一般來說，弄一個 PGSQL 資料庫到電腦上跑很容易，不過通常正式系統中會有一些東西需要微調，這部份沒有用一點心思去了解就會被遺漏。
不過現在流行把設置步驟整理成自動化作業，其中一個好處就是避免上面這狀況。其中一個流行的工具叫做 ansible，我最近才開始認識它～
EDB 公司也基於 ansible 提供了這樣的免費取得的工具（BSD 風格的授權），方便一般 PGSQL 使用者或是 EDB 訂閱客戶使用～不但避免了以上困擾，還可以讓大家直接套用原廠的最佳設置建議，同時又省下設定電腦流逝的光陰～
EnterpriseDB/edb-ansible: Ansible code for deploying EDB Postgres database clusters and related products.
Ansible Galaxy - edb_postgres

這篇筆記紀錄一些 EDB/Postgres 的 Ansible 佈署第一次操作體驗。

透過 XFS Project Quota 功能控制 PGSQL 個別帳號的空間用量

PGSQL 作為萬用型資料庫，有時候會有作中小型資料分析系統使用，這種系統有時候有不同人員需要使用，為避免使用者揮霍空間，就會有限制使用帳號可用資料量的想法，有一點像雲端的個人儲存空間～

但是 PGSQL 本身並沒有提供這種功能，只有一兩個稍嫌老舊的外掛雛型（應該還是能用）。。。有一個 Postgres 的延伸專案 Greenplum 由於是資料倉儲軟體，因此內建有 diskquota 外掛，不過 PGSQL 社群看起來不是很感興趣～

因此這邊透過 OS 內建功能達成：利用 Linux 底下 XFS 檔案系統才有的 Project Quota 功能，演練帳號空間控制～

PGSQL 有兩類型的 tablespace：
 - 普通用來放資料的，由 default_tablespace 指定
 - 暫存表跟查詢過程 temp file，由 temp_tablespace 指定

一般使用上，沒有需要特別指定 tablespace，資料跟暫存表都會放到 $PGDATA/base/ 跟 $PGDATA/base/pgsql_tmp/ 底下。

原則上這邊要限制的是 default_tablespace，至於 temp_tablespace 會涉及查詢成敗，因此要不是額外用較高速的共通磁碟空間，搭配 temp_file_limit 控制。

於是，這篇筆記目標就是使用 xfs_quota 的 Project Quota 功能控制個別 DB 帳號的個人使用空間（defaut_tablespace）。

從 Postgres 外部表查看即時的資料庫 Log

 聽說 Oracle 資料庫可以從資料庫裡面用表格查看資料庫 Alert Log（難道是把資料庫的 log 存放在資料庫裡面？這樣關掉資料庫不就看不到 log。。。？）

但是 EDB 跟 PGSQL 跟普通的程式一樣，寫檔案到 log 檔案。沒辦法直接用表格查詢查看（這是 SQL 控。。。。！？），對於只想要用 SQL 界面解決大小事的人們不太方便～

不過 PGSQL 有一個外部表模組，可以讀取 csv 檔案～

而 file_fdw 手冊頁面有一個雛型，可以直接查看 csv log 檔案內容；不過實際的 Log 會定時切換檔案，這篇筆記紀錄這樣的內容：透過 file_fdw 查看最目前的 DB log 檔案內容。

PGSQL 9.x 之後的交易流水帳 WAL 的管理變化—從 v9.4 到 v13

傳統的關聯式資料庫為了的設計上，大多有所謂的 Transaction Log （Tx Log） 的機制設計，將資料庫活動一五一十的記錄下來（優先於表格資料的實際回存到磁碟），以確保資料的高規格保全要求。

Transaction Log 通常稱作交易日誌，不過為了避免跟一般錯誤紀錄 Log 混淆，以下稱作交易紀錄流水帳。

在 PGSQL 與 EDB/PGSQL 企業版中，該機制稱為 Write-Ahead Logging，通常稱作 WAL。

這份筆記簡單紀錄 PGSQL 9.4 ~ PGSQL 13 的重要設定變化。

在 PGSQL 13 用 Stored Procedure 包裝 VACUUM 與 ANALYZE 指令

 使用 ETL 作業作資料匯入，常常會有大量資料匯入或異動。對 PGSQL 來說，這時候若可以在批次資料匯入/異動完畢後執行 ANALYZE 作一次資料表的抽樣統計收集，甚至是 VACUUM 維護作業，會有助於資料的查詢。

然而。在 PGSQL 裡面，可以執行 VACUUM 與 ANALYZE 指令的帳號，只有表格物件擁有者（ Table Owner ）以及 postgres/enterprisedb 等 Superuser 帳號，但是通常 ETL 程式用的帳號通常不會是這兩個。。。因此若有 vacuum analyze 包裝函數，搭配 Stored Function/Procedure 的 Security Definer 供其他帳戶呼叫，就會方便許多。

這篇筆記紀錄的就是 PGSQL 批次匯入後執行 ANALYZE 的 Stored Procedure，也順便練習 Stored Procedure 一下～

解剖 PGSQL 的資料檔：pg_filedump 工具練習

通常來說，使用 PGSQL 會避免人工去觸碰表格的資料檔：那些位於 $PGDATA/base/ 或是各個外部 Tablespace 裡面的檔案（原則上～除了 Log 目錄跟設定檔之外的東西不要亂碰比較好～）

不過在開發資料庫原始碼或是進行罕見問題除錯，還是有一點可能去碰的。

這邊紀錄的一個叫做 pg_filedump 的工具，專門將資料檔的內容「倒出來」。

PGSQL 外掛 DIY 筆記—數值 array 排序練習

開源軟體的特點，就是讓你可以跳下去寫程式。PGSQL 也不例外～～然而，除了核心專案本身的開發之外，熱門軟體常常有提供外掛開發 API，讓使用者擴充功能（例如，貼近生活的瀏覽器 Chrome 外掛 與 Firefox 外掛，文書編輯軟體 Libreoffice，或是工作上會使用的新興商業軟體，如 Tableau 等～）
在 Postgres 裡面，外掛的開發蠻貼近原始碼的，這邊就認識一下最簡易的開發，自製外掛～
雖然已經有幾篇不錯的參考教學了（見這篇筆記後的參考資料），但這邊還是紀錄一篇用自己的話來述說的筆記。

在 LXD 裡面設定 OpenLDAP Container (no SSL) 與 PGSQL 的 LDAP 認證介接

很多中大型的公司，會使用 LDAP 帳號管理服務，管理各種登入。不過多數時候不會有資料庫登入認證套用 LDAP 的時候：因為一般資料庫更多時候是設計好的程式跟資料庫互動訪問，比較少讓人類直接進入操作。

但還是有些時候，會有開放員工使用的資料庫，這時未惹不讓大家的腦袋被帳密塞爆，就會設定 LDAP 認證。

不過。。。LDAP 其實蠻難的。。這個筆記就準備一個 OpenLDAP 測試環境（主要內容），最後再用一個 PGSQL 的 LDAP 連線認證練習（附贈內容）～

「次」簡單裝 pgAdmin4 Server

撇開通用的資料庫連線 GUI 工具（例如這隻松鼠 SQiurreL 或是這隻海貍 DBeaver～或是要＄＄的蟾蜍跟貓），pgAdmin 是 PGSQL 長久以來的專屬 GUI 連線工具，比較入門都是先用這個（其實還有 phpPgAdmin、OmniDB，也是 OK 的選項～）。而 EnterpriseDB 的企業版管理平台 EDB Postgres Enterprise Manager 也是基於 pgAdmin 擴充而成的產品。

在 pgAdmin 從第三代改成第四代之後，pgAdmin4 變成一個網頁版 UI，可以安裝到獨立組機提供多人使用的服務。

這邊簡單的紀錄在 CentOS 7 Linux 底下的簡單版安裝步驟。

台灣時區的時戳資料超前 14 小時的神秘問題

PostgreSQL 跟 Linux 作業系統在時區設定上的狀況如下：

• Linux 電腦的 OS 時區設定成 IANA 時區識別代號 Asia/Taipei；在 SystemD 的系統中，timedatectl 會顯示 Asia/Taipei，而 date 指令則以 Timezone Abbreviation 呈現出容易在各種面向混淆的 CST 縮寫顯示。
• PostgreSQL 的時區在初始化 (initdb) 時決定（可以隨時調整）。沒有特別指定，維持預設。此時 Postgres initdb 會偵測 OS 的時區設定值，設定成相同時區。當 OS 設定成 Asia/Taipei 時，在 Postgres 會初始化成 ROC。
• Postgres 的時間函數，都會以 UTC offset 的方式顯示時區；然而，Postgres log 的時戳格式，則會以 Timezone Abbreviation 顯示。

然而，這樣會出現一個問題。