Yung-Chung Ku ycku

Demo patroni on docker

echo '{"CODE_ID":"12345678","CODE_NOTE":"abcdefg","CODE_ID":"87654321"}' | grep -oP '"CODE_ID":"\K.*?(?=")'

12345678
87654321

	export RDSHOST="rds.hostname"
	export SECRETID="secret-id"
	export SECRETSTRING=$(aws secretsmanager get-secret-value --secret-id "${SECRETID}" --query SecretString --output text)
	export PGHOST="${RDSHOST}"
	export PGUSER=$(echo "${SECRETSTRING}" \| jq -r '.username')
	export PGPASSWORD=$(echo "${SECRETSTRING}" \| jq -r '.password')

	-- Excel NORM.DIST(x, mean, stddev. FALSE)
	SELECT (1 / (stddev * sqrt(2 * pi()))) * exp(-1 * power(x - mean, 2) / (2 * power(stddev, 2))) AS pdf
	FROM (
	SELECT 5.5 AS x, 5.0 AS mean, 1.0 AS stddev
	) t;

	-- Excel NORM.DIST(x, mean, stddev. TRUE)
	SELECT 0.5 * (1 + erf((x - mean) / (stddev * sqrt(2)))) AS cdf
	FROM (
	SELECT 5.5 AS x, 5.0 AS mean, 1.0 AS stddev

	# pip install boto3

	import boto3
	from datetime import datetime, timedelta

	# 初始化 Cost Explorer 客戶端
	client = boto3.client('ce')

	# 設定日期範圍
	end_date = datetime.now().strftime('%Y-%m-%d')

	-- SELECT sqlcheck('select * from not_exist_table');
	-- 42P01: undefined_table
	-- SELECT sqlcheck('select * from not_exist_table wrong_syntax');
	-- 42601: syntax_error
	-- 語法檢查會優先於物件檢查

	CREATE OR REPLACE FUNCTION sqlcheck(query text) RETURNS text AS
	$$
	DECLARE
	sql text;

	QUERY PLAN
	-----------------------------------------------------------------------------------------------------------------------------------------
	Aggregate (cost=10438.81..10438.82 rows=1 width=8) (actual time=80.819..84.547 rows=1 loops=1)
	-> Gather (cost=5774.06..10438.80 rows=1 width=0) (actual time=52.857..83.197 rows=27183 loops=1)
	Workers Planned: 1
	Workers Launched: 1
	-> Parallel Hash Anti Join (cost=4774.06..9438.70 rows=1 width=0) (actual time=46.786..67.737 rows=13592 loops=2)
	Hash Cond: (s.key = t.key)
	-> Parallel Seq Scan on s (cost=0.00..2843.47 rows=117647 width=4) (actual time=0.004..8.666 rows=100000 loops=2)
	-> Parallel Hash (cost=2843.47..2843.47 rows=117647 width=4) (actual time=22.881..22.882 rows=100000 loops=2)

	QUERY PLAN
	------------------------------------------------------------------------------------------------------------------------------------------------
	Finalize Aggregate (cost=10449.10..10449.11 rows=1 width=8) (actual time=101.312..104.828 rows=1 loops=1)
	-> Gather (cost=10448.99..10449.10 rows=1 width=8) (actual time=100.990..104.822 rows=2 loops=1)
	Workers Planned: 1
	Workers Launched: 1
	-> Partial Aggregate (cost=9448.99..9449.00 rows=1 width=8) (actual time=90.238..90.241 rows=1 loops=2)
	-> Parallel Hash Anti Join (cost=4774.06..9446.73 rows=904 width=0) (actual time=64.249..89.494 rows=13592 loops=2)
	Hash Cond: (s.key = t.key)
	-> Parallel Seq Scan on s (cost=0.00..2843.47 rows=117647 width=4) (actual time=0.007..11.334 rows=100000 loops=2)

	QUERY PLAN
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------
	Finalize Aggregate (cost=247880438.47..247880438.48 rows=1 width=8) (actual time=1219349.866..1219350.085 rows=1 loops=1)
	-> Gather (cost=247880438.25..247880438.46 rows=2 width=8) (actual time=1219349.805..1219350.074 rows=3 loops=1)
	Workers Planned: 2
	Workers Launched: 2
	-> Partial Aggregate (cost=247879438.25..247879438.26 rows=1 width=8) (actual time=1218624.458..1218624.460 rows=1 loops=3)
	-> Parallel Index Only Scan using s_key_idx on s (cost=0.42..247879334.09 rows=41667 width=0) (actual time=326.082..1218608.814 rows=9061 loops=3)
	Filter: (NOT (SubPlan 1))
	Rows Removed by Filter: 57606

	QUERY PLAN
	--------------------------------------------------------------------------------------------------------------------------------------------------------------
	Aggregate (cost=6135.53..6135.54 rows=1 width=8) (actual time=35.063..36.716 rows=1 loops=1)
	-> Gather (cost=3630.09..6135.53 rows=1 width=0) (actual time=19.535..35.008 rows=37156 loops=1)
	Workers Planned: 2
	Workers Launched: 2
	-> Parallel Hash Anti Join (cost=2630.09..5135.43 rows=1 width=0) (actual time=11.705..23.096 rows=12385 loops=3)
	Hash Cond: (s.key = t.key)
	-> Parallel Index Only Scan using s_key_idx on s (cost=0.29..2088.96 rows=41667 width=4) (actual time=0.069..4.765 rows=33333 loops=3)
	Heap Fetches: 0