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

	DROP TABLE IF EXISTS "focus_data";

	CREATE TABLE "focus_data" (
	"AvailabilityZone" varchar(50) DEFAULT NULL,
	"BilledCost" decimal(18,11) DEFAULT NULL,
	"BillingAccountId" varchar(100) DEFAULT NULL,
	"BillingAccountName" varchar(100) DEFAULT NULL,
	"BillingCurrency" varchar(5) DEFAULT NULL,
	"BillingPeriodEnd" timestamptz NULL DEFAULT NULL,
	"BillingPeriodStart" timestamptz NULL DEFAULT NULL,

	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')

	CREATE FUNCTION norm_dist(x real, mean real, stddev real, cumulative boolean) RETURNS real AS $$
	DECLARE p real;
	BEGIN
	IF cumulative = false THEN
	SELECT (1 / (stddev * sqrt(2 * pi()))) * exp(-1 * power(x - mean, 2) / (2 * power(stddev, 2))) INTO p;
	ELSE
	SELECT 0.5 * (1 + erf((x - mean) / (stddev * sqrt(2)))) INTO p;
	END IF;

	RETURN p;

	# 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