ycku

CREATE OR REPLACE FUNCTION usl(
    n_values double precision[], 
    tps_values double precision[]
)
RETURNS TABLE(
    lmbda double precision, 
    sigma double precision, 
    kappa double precision, 
    n_max double precision
) AS $$

ycku

pgbench -U pgbench -d pgbench -c 10 -j 10 -T 600 -P 60 -r -v

pgbench (17.5)
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 60.0 s, 2507.5 tps, lat 3.980 ms stddev 2.263, 0 failed
progress: 120.0 s, 2759.8 tps, lat 3.623 ms stddev 1.932, 0 failed
progress: 180.0 s, 2482.3 tps, lat 4.028 ms stddev 2.242, 0 failed
progress: 240.0 s, 2445.1 tps, lat 4.089 ms stddev 2.327, 0 failed

ycku

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,

ycku

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

ycku

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;

ycku

# pip install boto3

import boto3
from datetime import datetime, timedelta

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

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

ycku

Demo patroni on docker

• 可以自行建立 patroni 實驗環境
• 以 Failover 作為環境測試情境

1. Docker Environment

• 使用 Docker playground: https://labs.play-with-docker.com/
• 使用 DockerHub 帳號登入後, ADD NEW INSTANCE, 可以使用 4 小時

ycku

• grep CODE_ID and extract the VALUE

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

12345678
87654321

• https://askubuntu.com/questions/1167287/parse-json-with-default-bash-only

ycku

-- 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;

ycku

                                                               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)