Foreword

In PostgreSQL, there isn’t a native foreach loop construct in C, because C itself doesn’t have a foreach loop as you might find in higher-level languages like Python or PHP. However, PostgreSQL often implements loop iterations over elements using Macros that simplify the handling of data structures, such as linked lists, which are commonly used within its codebase.

Common Loop Macros in PostgreSQL

1. lfirst(lc):

   • This macro retrieves the data stored in a ListCell. The ListCell structure typically contains a union that can hold various types of pointers (like void*, int, etc.). The ptr_value is a generic pointer that can point to any node or structure, and lfirst simply casts it back from the void *.

2. lfirst_node(type, lc):

   • This macro is used when the list elements are known to be of a specific node type, which is common in the parser and planner where lists often contain specific types of nodes (e.g., expression or plan nodes). lfirst_node uses castNode to cast the pointer retrieved by lfirst to the specified type, ensuring type safety and readability in the code.

3. castNode(_type_, nodeptr):

   • A simple cast to the specified type _type_. It enhances readability and ensures that the casting is explicit in the code, which is crucial for understanding that a type conversion is taking place, particularly when navigating complex data structures common in PostgreSQL’s internals.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#define lfirst(lc)				((lc)->ptr_value)
#define lfirst_node(type,lc)	castNode(type, lfirst(lc))
#define castNode(_type_, nodeptr) ((_type_ *) (nodeptr))
#define true	1
#define false	0
#define foreach(cell, lst)	\
	for (ForEachState cell##__state = {(lst), 0}; \
		 (cell##__state.l != NIL && \
		  cell##__state.i < cell##__state.l->length) ? \
		 (cell = &cell##__state.l->elements[cell##__state.i], true) : \
		 (cell = NULL, false); \
		 cell##__state.i++)

#define NIL						((List *) NULL)

The ListCell union consists of a single member, ptr_value, which is a generic pointer (void *).

This pointer can hold a reference to any type of data, allowing for flexibility in what kind of data the list can contain.
This structure is useful for managing lists of generic data types.

The List structure represents a dynamic list in PostgreSQL.
It contains:

• length: An integer that specifies the current number of elements in the list.
• elements: A pointer to an array of ListCell elements, which holds the actual data in the list. This array can be re-allocated as the list grows or shrinks, allowing for dynamic resizing.
• The comment suggests that sometimes ListCell elements may be allocated directly alongside the List structure itself. This can optimize memory usage and improve performance.

1
2
3
4
5
6
7
8
9
10

typedef union ListCell
{
	void	   *ptr_value;
} ListCell;

typedef struct List
{
	int			length;			/* number of elements currently present */
	ListCell   *elements;		/* re-allocatable array of cells */
} List;

Foreword

Using gdb for command-line debugging still feels inconvenient. I initially wanted to find a simpler way to directly debug the PostgreSQL source code under Windows. After searching for a while, I found that Visual Studio (VS) was the only option available, but it is heavy and the steps are quite complex. Since most real environments run on Linux, it is better to debug the PostgreSQL source code under Linux.

How to build & install PostgreSQL from source code.

I used Ubuntu Linux environment, the first step we might need to install pre-requirement tool for PostgreSQL build.

1
2

sudo apt-get update
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache libsystemd-dev -y

download PostgreSQL source code.

1
2
3

wget https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz
tar xvfz postgresql-14.8.tar.gz
cd postgresql-14.8

we would need to make sure the path (--prefix) exist in your system.

1
2
3
4
5
6
7
8

./configure --prefix=/home/daniel/postgresql-14.8/pgsql --with-icu --with-openssl --with-systemd --with-libxml --enable-debug

#or debug -g3 mode

./configure --prefix=/home/daniel/postgresql-14.8/pgsql --with-icu --with-openssl --with-systemd --with-libxml --enable-debug CFLAGS="-DGCC_HASCLASSVISIBILITY -O0 -Wall -W -g3 -gdwarf-2"

make -j 8
make install

we must build with --enable-debug parameter, otherwise we can’t debug with our source code.

Introduction

This ariticle will guide us how to do long-term backup (more than 35 days) on Azure PostgreSQL Flexible.

Prerequisites:

• A Postgres flexible server and an Azure VM (Linux (ubuntu 20.04)) that has access to it.
• A MI (Managed Identity) in your subscription.
• Please kindly make sure backup Postgres flexible server version align with pg_dump version.

Here is the sample code of Azure PostgreSQL Flexible long term backup with Managed Identity.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

echo 'start program'
echo '====================='
echo 'start installing postgresql client suit'
sudo apt update
sudo apt -y install postgresql-client
echo '======================'
echo 'postgresql client install ends'
echo 'start mount storage'

wget https://packages.microsoft.com/config/ubuntu/20.04/packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
sudo apt-get update
sudo apt-get install blobfuse -y
sudo apt-get install jq -y

if [ ! -f blob.conf  ]; then
    echo 'accountName <<Your blob accountName>>' >> blob.conf 
	echo 'authType MSI' >> fuse_connection.cfg
	echo 'identityObjectId <<Your blob accountKey>>' >> blob.conf 
	echo 'containerName <<Your blob Container Name>>' >> blob.conf 
fi
#create a folder which can mount to blob
mkdir ~/data
sudo blobfuse ~/data --tmp-path=/mnt/resource/mycontainer  --config-file=./blob.conf -o attr_timeout=240 -o entry_timeout=240 -o negative_timeout=120

echo '======= starting backup============'
DNAME=`date +%Y%m%d%H%M%S`
export PGPASSWORD=`curl -s 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.windows.net&client_id=<<MI client ID>>' -H Metadata:true | jq -r .access_token`
pg_dump --host=test-conn.postgres.database.azure.com --username='MI-Demo' -Fc -c  testdb > ~/data/dump$DNAME.sql

Experiments:

Here is the guideline for using managed identity to connect to your DB server in VM.

We would make sure the Authentication setting that was PostgreSQL and Azure Active Directory authentication.

前言

因為工作需要最近在研究 postgresql DB，postgresql DB 是一個 Open Source RDBMS，所以有任何問題疑問都可以把 source code 下載並 debug 了解原因，本篇希望可以快速幫助想要透過 source code 安裝 postgresql DB 的人

Install Postgresl

假如你跟我一樣是 Ubuntu 在安裝前需要先把開發環境設定完畢，詳細資訊可參考 Postgresql Compile_and_Install_from_source_code

1
2
3

sudo apt-get update

sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache

我們可以透過 FTP Source Code 要安裝 postgres source code 版本

假如我是要安裝 v12.9 的 postgres 版本，我會跑下面命令下載 source code & 解壓縮

解壓縮完畢後，應該會在該目錄看到一個該版號資料夾裡面存放的就是該版本使用 Postgresql source code.

1
2
3
4

wget https://ftp.postgresql.org/pub/source/v12.9/postgresql-12.9.tar.gz
tar xvfz postgresql-12.9.tar.gz

cd postgresql-12.9 

利用 source code 安裝 postgres

前言

我算是一個雲端小白，自從進入目前公司後才開始接觸雲端相關概念並感受到雲端服務威力

我會想參加 SSA 考試主要有下面幾個原因

1. 主要是想趕在 SAA-C02 (2022-08) 換考試範圍前來測驗
2. 感覺放在履歷上，增加職場競爭力
3. 驗收自己目前對於雲端相關知識
4. 目前在架構 Team 擔任工程師，希望規劃出好的系統架構

考試重點

S3 (考試必考)

• S3 Standard
• S3 IA (Infrequent Access)
• Intelligent-Tiering：設計來優化成本，基於其自動移動 data 到相對便宜的 tier，不影響 performance，也不需提前處理。
• S3 One Zone-IA
• S3 Glacier：價格便宜，適合存放 Archive 檔案 (預設會加密)
  • Instant Retrieval：最少存放 90 天
  • Flexible Retrieval (formerly S3 Glacier)：最少存放 90 天
    • Expedited：1~5 min
    • Standard：3~5 hours
    • Bulk：5~12 hours
  • Deep Archive - for long term storage: 最少存放 180 天
    • Standard：12 hours
    • Bulk：48 hours

重點功能

S3 Lifecycle rule 控制 S3 存放物件存放規則 S3 Type，這樣可以讓S3使用費用更有效率

前言

AWS lambda 作為 serverless 服務，之前有介紹過 AWS Lambda 初體驗 by .net core，本次要介紹 serverless 框架搭配 AWS CloudFormation (IaC)

Serverless 預設使用 provider 是 AWS

AWS is the default cloud provider used by Serverless Framework.

建立第一個 serverless

本次案例我們利用 serverless cli 建立 dotnet template，利用 nqm 安裝

1

npm install -g serverless

安裝完後建立一個 dotnet core serverless project

1

serverless create -t aws-csharp -n dotnetServerless

本次使用參數說明

前言

AutoVacuum 在 Postgresql 是一個很重要的機制(甚至可以說最重要也不為過)，但裡面有些地方需要了解今天就帶大家初探

資料 & 測試資料資訊

本次執行 Sample Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

CREATE TABLE T1 (
    ID INT NOT NULL PRIMARY KEY,
	val INT NOT NULL,
	col1 UUID NOT NULL,
	col2 UUID NOT NULL,
	col3 UUID NOT NULL,
	col4 UUID NOT NULL,
	col5 UUID NOT NULL,
	col6 UUID NOT NULL
);


INSERT INTO T1
SELECT i,
       RANDOM() * 1000000,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid
FROM generate_series(1,20000000) i;


CREATE TABLE T2 (
    ID INT NOT NULL PRIMARY KEY,
	val INT NOT NULL,
	col1 UUID NOT NULL,
	col2 UUID NOT NULL,
	col3 UUID NOT NULL,
	col4 UUID NOT NULL,
	col5 UUID NOT NULL,
	col6 UUID NOT NULL
);

INSERT INTO T2
SELECT i,
       RANDOM() * 1000000,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid,
	   md5(random()::text || clock_timestamp()::text)::uuid
FROM generate_series(1,1000000) i;

vacuum ANALYZE T1;
vacuum ANALYZE T2;

查詢 sample code

1
2
3
4
5

EXPLAIN (ANALYZE,TIMING ON,BUFFERS ON)
SELECT t1.*
FROM T1 
INNER JOIN T2 ON t1.id = t2.id 
WHERE t1.id < 1000000 

此次查詢如期走 Merge Join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

"Gather  (cost=1016.37..30569.85 rows=53968 width=104) (actual time=0.278..837.297 rows=999999 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=38273 read=21841"
"  ->  Merge Join  (cost=16.37..24173.05 rows=22487 width=104) (actual time=11.993..662.770 rows=333333 loops=3)"
"        Merge Cond: (t2.id = t1.id)"
"        Buffers: shared hit=38273 read=21841"
"        ->  Parallel Index Only Scan using t2_pkey on t2  (cost=0.42..20147.09 rows=416667 width=4) (actual time=0.041..69.947 rows=333333 loops=3)"
"              Heap Fetches: 0"
"              Buffers: shared hit=6 read=2732"
"        ->  Index Scan using t1_pkey on t1  (cost=0.44..48427.24 rows=1079360 width=104) (actual time=0.041..329.874 rows=999819 loops=3)"
"              Index Cond: (id < 1000000)"
"              Buffers: shared hit=38267 read=19109"
"Planning:"
"  Buffers: shared hit=4 read=8"
"Planning Time: 0.228 ms"
"Execution Time: 906.760 ms"

假如更新如多資料，但未觸發臨界值

前言

在巨量資料下，資料庫合理的分片(DB sharding)可以幫助我們承受更高量的資料量(前提Index還有設定都調教到很好在考慮 sharding)

而在 Redis 世界裡也有 redis cluster 來做 sharding 事情，今天就來跟大家分享介紹

我使用是 bitnami redis-cluster 這個 Image 來做這次 Poc

此案例是 3 master - 3 slave Redis server 範例

建立 Redis Cluster

How to Use

1

docker-compose up -d

跑完之後就會出現 6 台 Redis Container，如下表

1
2
3
4
5
6
7
8

$ docker ps
CONTAINER ID   IMAGE                       COMMAND                  CREATED          STATUS          PORTS                                       NAMES
1a0c740cbb96   bitnami/redis-cluster:6.2   "/opt/bitnami/script…"   31 seconds ago   Up 27 seconds   0.0.0.0:8105->6379/tcp, :::8105->6379/tcp   rediscluster_redis-node-5_1
1651a81a286f   bitnami/redis-cluster:6.2   "/opt/bitnami/script…"   37 seconds ago   Up 30 seconds   0.0.0.0:8102->6379/tcp, :::8102->6379/tcp   rediscluster_redis-node-2_1
5d93edfc55e6   bitnami/redis-cluster:6.2   "/opt/bitnami/script…"   37 seconds ago   Up 31 seconds   0.0.0.0:8104->6379/tcp, :::8104->6379/tcp   rediscluster_redis-node-4_1
8ab5bbbb7364   bitnami/redis-cluster:6.2   "/opt/bitnami/script…"   37 seconds ago   Up 29 seconds   0.0.0.0:8103->6379/tcp, :::8103->6379/tcp   rediscluster_redis-node-3_1
8edf90bed3fb   bitnami/redis-cluster:6.2   "/opt/bitnami/script…"   37 seconds ago   Up 29 seconds   0.0.0.0:8101->6379/tcp, :::8101->6379/tcp   rediscluster_redis-node-1_1
e11ac0ec56aa   bitnami/redis-cluster:6.2   "/opt/bitnami/script…"   37 seconds ago   Up 30 seconds   0.0.0.0:8100->6379/tcp, :::8100->6379/tcp   rediscluster_redis-node-0_1

前言

當一個專案越來越大，在開發新產品時同時要兼顧程式碼品質會越來越困難，今天要介紹的　Ndepend 是一個很優秀靜態分析 .Net 專案好物

會幫我們產生一個 DashBoard 來了解目前專案大致上分析後的問題，並提供建議解法和處理方向，讓我們提前更快速抓出未來或目前存在 bug，減少發生需要晚上起來 support 機率，讓客戶，老闆，工程師都開心的三大歡喜

另外我們也可以透過 Ndepend 來撰寫我們自己 Code Rule 並在 CI/CD 流程中進行靜態掃描，讓我們出產軟體更有品質

本文我使用我自己的開源專案 ElectronicInvoice_TW 來當作分析案例

建立 Ndepend 開始分析

安裝完 Ndepend 後我們可以在 virtual studio 上面的[延伸模組] 出現建立 Ndepend 項目，一開始我們利用 Attach New Ndepend Project to Current VS Solution ，來建立分析項目

NDepend 分析完程式後我們會在 sln 檔同級目錄下看到一個 NDependOut 資料夾跟 .ndproj 檔案

• .ndproj 是 XML 格式儲存的專案檔，內容包含 NDepend 專案資訊及分析設定資訊
• NDependOut 資料夾中會有一個 .ndar 檔案這次一個二進制格式儲存的分析後結果
• NDependOut 中有一個 NDependReport.html 是一個分析後 Html 報表

前言

在 Postgresql 世界裡每建立一個 Connection，都會有一個 process 來服務該請求，Postgresql 沒有 ThreadPool 概念所以在 Connection 使用量大時會發生一些問題

• 資源浪費
• 超過同時間超過 max_connection 後面連接會無法連接

一個 open source 插件 PgBouncer 就是幫我們解決上面問題，不過如果你是使用 AWS RDS 來當作 DB 就很適合使用 RDS Proxy

本次使用免費帳號範圍提供的機器 db.t2.micro

另外因為 RDS Proxy 在 EC2 內網才能連接，所以我也利用免費帳號準備一台 EC2 並安裝 pgbench 進行壓力測試

壓測前準備 & 說明

我把 max_connections 最大連接數量調整成 550