-- ============================================================================
-- PostgreSQL 18 — JSON 与 XML 数据处理 (JSON and XML Data Handling)
-- 行业: 国际化珠宝集团 OA/ERP/CRM/HRM/MES/SCM/WMS/KMS 统一中台
-- 场景: 万亿数据高并发 / 国际时区时间差 / 国际多种货币结算
-- 本文件【自包含·完全幂等】: 重复运行结果逐字节一致 (setseed 保证造数一致)
-- 连接方式: PGPASSWORD=<your_password> psql -U postgres -h localhost -p 5433 -d jewelry_pg -f 本文件
--
-- 本专题聚焦: 在关系型主模型之外, 用 JSON/XML 承载半结构化数据, 并明确其边界。
-- - JSON/JSONB 存储与算子 (-> ->> #> #>> @> ? ?| ?&)
-- - JSONPath ($.x.y, jsonb_path_query/exists) : 跨形态查询
-- - GIN 索引 (jsonb_path_ops) : 半结构化列的高并发检索
-- - JSON 聚合 (jsonb_agg / jsonb_object_agg) 与 变更 (jsonb_set/insert)
-- - XML 存储 / xpath() / xpath_exists / 构造 (xmlelement/xmlforest)
-- - 半结构化边界: 热路径标量从 JSON 抽取为生成列 + B-tree 索引
-- - 国际时区 (JSON 内 UTC 字符串经 AT TIME ZONE 折算)
-- - 国际多币种 (JSON 内 {orig,currency,usd}, 跨币种不可直接相加)
-- ============================================================================
\timing off
SET client_min_messages = warning; -- 抑制 DROP/CREATE/VACUUM 的 NOTICE, 保证重跑幂等
SET max_parallel_workers_per_gather = 0; -- 关并行, 使块统计稳定(万亿级可据需开启)
SET random_page_cost = 1.1;
SET seq_page_cost = 1.0;
SET effective_cache_size = '4GB';
SET TIME ZONE 'UTC'; -- 使 extract()/AT TIME ZONE 计算基于 UTC, 与时区演示形成可比基准
在进入具体演示之前，先用一张表把 JSONB 与 XML 两条技术路线横向对齐，便于后续对照阅读：
维度
JSONB
XML
存储类型
jsonb（二进制解析、去重键、可索引）；json 仅保留原始文本
xml（保留文档结构，可用 IS DOCUMENT 校验合法性）
索引方式
GIN 索引，jsonb_path_ops 操作符类，加速 @> 包含检索
无专用 GIN 操作符类，通常用表达式索引（如 (xpath(...))[1]::text）或生成列 + B-tree
查询语法
-> / ->> / #> / #>> / @> / ? / ?| / ?&，以及 JSONPath jsonb_path_query / jsonb_path_exists
xpath() 返回节点数组、xpath_exists() 做谓词判断，路径形如 /product/price/@currency
聚合与构造函数
jsonb_agg（行转数组）、jsonb_object_agg（键值映射）、jsonb_build_object / jsonb_build_array、jsonb_set / jsonb_insert
xmlelement / xmlforest 构造、xmlserialize 转文本、xmlparse(document ...) 解析
适用场景
订单富载荷：字段多变、嵌套深、需高频按键/包含检索
供应商目录、发票：结构稳定、需保留文档语义与属性
在本脚本中，pg_order_doc 对应 JSONB 一侧，用 order_json 承载订单号、客户多语言名、商品数组与多币种金额，并以 jsonb_path_ops 的 GIN 索引支撑高并发检索；pg_catalog_xml 对应 XML 一侧，用 catalog_xml 保存供应商产品目录，通过 xpath() / xpath_exists() 抽取名称、价格与币种属性。两者共同说明：半结构化数据适合承载易变载荷，而热路径标量仍应抽取为生成列并建立 B-tree 索引。
-- ----------------------------------------------------------------------------
-- 0. 度量框架 (DROP + CREATE, 幂等)
-- pg_plan_stats : 抓真实执行计划 (Actual Rows / 共享块 / 扫描节点类型)
-- pg_find_scan : 下钻到真正的扫描节点 (顶层常是 Aggregate/WindowAgg/...)
-- pg_plan_text_has : 检查计划文本是否含某关键字 (FOR 循环拼接多行)
-- pg_demo_v : 自检登记
-- pg_demo_err_probe : 捕获真实报错
-- ----------------------------------------------------------------------------
DROP FUNCTION IF EXISTS pg_plan_stats(text) CASCADE;
CREATE OR REPLACE FUNCTION pg_plan_stats(
p_sql text,
OUT actual_rows bigint,
OUT shared_blks bigint,
OUT node_type text)
RETURNS record LANGUAGE plpgsql AS $$
DECLARE j json;
BEGIN
EXECUTE 'EXPLAIN (ANALYZE, TIMING OFF, FORMAT JSON) ' || p_sql INTO j;
actual_rows := (j->0->'Plan'->>'Actual Rows')::numeric::bigint;
shared_blks := COALESCE((j->0->'Plan'->>'Shared Hit Blocks')::numeric::bigint,0)
+ COALESCE((j->0->'Plan'->>'Shared Read Blocks')::numeric::bigint,0);
node_type := pg_find_scan(j->0->'Plan');
END $$;
DROP FUNCTION IF EXISTS pg_find_scan(json) CASCADE;
CREATE OR REPLACE FUNCTION pg_find_scan(cur json) RETURNS text LANGUAGE plpgsql AS $$
DECLARE nt text;
BEGIN
nt := cur->>'Node Type';
IF nt LIKE '%Scan' THEN RETURN nt; END IF;
IF cur->'Plans' IS NOT NULL AND json_array_length(cur->'Plans') > 0 THEN
RETURN pg_find_scan(cur->'Plans'->0);
END IF;
RETURN nt;
END $$;
DROP FUNCTION IF EXISTS pg_plan_text_has(text, text) CASCADE;
CREATE OR REPLACE FUNCTION pg_plan_text_has(p_sql text, p_sub text) RETURNS boolean LANGUAGE plpgsql AS $$
DECLARE r record; t text := '';
BEGIN
FOR r IN EXECUTE 'EXPLAIN (FORMAT TEXT) ' || p_sql LOOP
t := t || r."QUERY PLAN";
END LOOP;
RETURN position(p_sub in t) > 0;
END $$;
DROP TABLE IF EXISTS pg_demo_check;
CREATE TABLE pg_demo_check (
name text PRIMARY KEY,
pass int,
detail text
);
DROP FUNCTION IF EXISTS pg_demo_v(text,int,text) CASCADE;
CREATE OR REPLACE FUNCTION pg_demo_v(p_name text, p_pass int, p_detail text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO pg_demo_check(name,pass,detail) VALUES (p_name,p_pass,p_detail)
ON CONFLICT (name) DO UPDATE SET pass=EXCLUDED.pass, detail=EXCLUDED.detail;
END $$;
DROP TABLE IF EXISTS pg_demo_probe;
CREATE TABLE pg_demo_probe (
label text PRIMARY KEY,
sqlstate text,
msg text
);
DROP FUNCTION IF EXISTS pg_demo_err_probe(text,text) CASCADE;
CREATE OR REPLACE FUNCTION pg_demo_err_probe(p_label text, p_sql text)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE e text; s text;
BEGIN
BEGIN
EXECUTE p_sql;
INSERT INTO pg_demo_probe(label,sqlstate,msg) VALUES (p_label,'OK','执行成功')
ON CONFLICT (label) DO UPDATE SET sqlstate='OK', msg='执行成功';
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS s=RETURNED_SQLSTATE, e=MESSAGE_TEXT;
INSERT INTO pg_demo_probe(label,sqlstate,msg) VALUES (p_label,s,e)
ON CONFLICT (label) DO UPDATE SET sqlstate=EXCLUDED.sqlstate, msg=EXCLUDED.msg;
END;
END $$;
-- ----------------------------------------------------------------------------
-- 1. 事实表 pg_txn (八域中台 10 万行交易事实; 与同系列脚本同种子, 保证语境一致)
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS pg_txn CASCADE;
CREATE TABLE pg_txn (
txn_id bigint PRIMARY KEY,
system_code text NOT NULL,
customer_id int,
product_id int,
supplier_id int,
warehouse_id int,
emp_id int,
country_code text,
currency_code text,
amount_orig numeric(14,2),
amount_usd numeric(14,4),
created_utc timestamptz,
tz text GENERATED ALWAYS AS (
CASE country_code
WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'HK' THEN 'Asia/Hong_Kong'
WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo'
WHEN 'DE' THEN 'Europe/Berlin' WHEN 'FR' THEN 'Europe/Paris'
WHEN 'GB' THEN 'Europe/London' WHEN 'SG' THEN 'Asia/Singapore'
WHEN 'AU' THEN 'Australia/Sydney' WHEN 'IN' THEN 'Asia/Kolkata'
ELSE 'UTC' END) STORED,
create_local_date date GENERATED ALWAYS AS (
(created_utc AT TIME ZONE (
CASE country_code
WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'HK' THEN 'Asia/Hong_Kong'
WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo'
WHEN 'DE' THEN 'Europe/Berlin' WHEN 'FR' THEN 'Europe/Paris'
WHEN 'GB' THEN 'Europe/London' WHEN 'SG' THEN 'Asia/Singapore'
WHEN 'AU' THEN 'Australia/Sydney' WHEN 'IN' THEN 'Asia/Kolkata'
ELSE 'UTC' END))::date) STORED,
reference_no text UNIQUE,
note text,
rand_val double precision
);
DO $$
BEGIN
PERFORM setseed(0.20261020);
INSERT INTO pg_txn (txn_id, system_code, customer_id, product_id, supplier_id, warehouse_id,
emp_id, country_code, currency_code, amount_orig, amount_usd,
created_utc, reference_no, note, rand_val)
SELECT n,
(ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)],
1+((n+n/8)%10000),
1+(n%2000),
1+(n%200),
1+(n%50),
1+(n%500),
(ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+((n/8)%10)],
(ARRAY['CNY','HKD','USD','JPY','EUR','EUR','GBP','SGD','AUD','INR'])[1+((n/8)%10)],
round((100 + random()*90000)::numeric,2),
round(((100 + random()*90000) /
(CASE (ARRAY['CNY','HKD','USD','JPY','EUR','EUR','GBP','SGD','AUD','INR'])[1+((n/8)%10)]
WHEN 'CNY' THEN 7.10 WHEN 'HKD' THEN 7.80 WHEN 'USD' THEN 1.00
WHEN 'JPY' THEN 150.0 WHEN 'EUR' THEN 0.92 WHEN 'GBP' THEN 0.79
WHEN 'SGD' THEN 1.35 WHEN 'AUD' THEN 1.52 WHEN 'INR' THEN 83.0 END))::numeric,4),
'2025-01-01 00:00:00+00'::timestamptz + ((random()*300*24*3600)::integer * interval '1 second'),
'REF-'||lpad(n::text,8,'0'),
(ARRAY['Diamond','Gold','Pearl','Ruby','Sapphire','Emerald','Platinum','Tanzanite','Jade','Onyx','Zirconia','Topaz'])[1+(n%12)] || ' order for VIP client',
random()
FROM generate_series(1,100000) n;
END $$;
CREATE INDEX idx_txn_cover ON pg_txn (system_code, amount_usd);
CREATE INDEX idx_txn_created ON pg_txn (created_utc);
CREATE INDEX brin_txn_created ON pg_txn USING brin (created_utc);
VACUUM ANALYZE pg_txn;
-- ----------------------------------------------------------------------------
-- 2. 半结构化表 A: pg_order_doc (JSON/JSONB) —— KMS/CRM/ERP 订单富载荷
-- order_json 承载: 订单号 / 客户(含多语言本地化名) / 状态 / 商品数组 / 金额(原币+USD)
-- vip_level 用「生成列」从 JSON 抽取到关系列 —— 热路径标量走 B-tree, 保持边界清晰
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS pg_order_doc CASCADE;
CREATE TABLE pg_order_doc (
doc_id bigint PRIMARY KEY,
system_code text NOT NULL,
country_code text,
order_json jsonb NOT NULL,
created_utc timestamptz,
vip_level int GENERATED ALWAYS AS ((order_json->'customer'->>'vip_level')::int) STORED
);
DO $$
BEGIN
PERFORM setseed(0.20261021);
INSERT INTO pg_order_doc (doc_id, system_code, country_code, order_json, created_utc)
SELECT n,
(ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)],
(ARRAY['CN','HK','US','JP','DE','FR','GB','SG','AU','IN'])[1+((n/8)%10)],
jsonb_build_object(
'order_no', 'ORD-'||lpad(n::text,8,'0'),
'customer', jsonb_build_object(
'id', 1+(n%10000),
'vip_level', 1+(n%5),
'localized_name', jsonb_build_object(
'en', 'Client '||n, 'zh', '客户'||n, 'de', 'Kunde '||n)
),
'status', (ARRAY['paid','pending','shipped','refunded'])[1+(n%4)],
'items', jsonb_build_array(
jsonb_build_object('sku','SKU-'||(1+(n%2000)),
'qty', 1+(n%5),
'unit_usd', round((10+random()*1000)::numeric,2))
),
'amount', jsonb_build_object(
'orig', round((100+random()*90000)::numeric,2),
'currency', (ARRAY['CNY','HKD','USD','JPY','EUR','GBP','SGD','AUD','INR'])[1+((n/8)%9)],
'usd', round(((100+random()*90000)/
(CASE (ARRAY['CNY','HKD','USD','JPY','EUR','GBP','SGD','AUD','INR'])[1+((n/8)%9)]
WHEN 'CNY' THEN 7.10 WHEN 'HKD' THEN 7.80 WHEN 'USD' THEN 1.00
WHEN 'JPY' THEN 150.0 WHEN 'EUR' THEN 0.92 WHEN 'GBP' THEN 0.79
WHEN 'SGD' THEN 1.35 WHEN 'AUD' THEN 1.52 WHEN 'INR' THEN 83.0 END))::numeric,4)
)
),
'2025-01-01 00:00:00+00'::timestamptz + ((random()*300*24*3600)::integer * interval '1 second')
FROM generate_series(1,50000) n;
END $$;
CREATE INDEX idx_order_vip ON pg_order_doc (vip_level); -- 关系列 B-tree (热路径)
CREATE INDEX idx_order_json_gin ON pg_order_doc USING gin (order_json jsonb_path_ops); -- 半结构检索
CREATE INDEX idx_order_sys ON pg_order_doc (system_code);
VACUUM ANALYZE pg_order_doc;
-- ----------------------------------------------------------------------------
-- 3. 半结构化表 B: pg_catalog_xml (XML) —— OA/ERP/SCM 供应商产品目录 / 发票
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS pg_catalog_xml CASCADE;
CREATE TABLE pg_catalog_xml (
cat_id bigint PRIMARY KEY,
system_code text NOT NULL,
catalog_xml xml NOT NULL
);
DO $$
BEGIN
PERFORM setseed(0.20261021);
INSERT INTO pg_catalog_xml (cat_id, system_code, catalog_xml)
SELECT n,
(ARRAY['OA','ERP','CRM','HRM','MES','SCM','WMS','KMS'])[1+(n%8)],
xmlparse(document
'<product>'||
'<sku>SKU-'||(1+(n%2000))||'</sku>'||
'<name>'||(ARRAY['Diamond Ring','Gold Necklace','Pearl Earring','Ruby Bracelet','Sapphire Pendant'])[1+(n%5)]||'</name>'||
'<price currency="'||(ARRAY['CNY','HKD','USD','JPY','EUR','GBP','SGD','AUD','INR'])[1+((n/8)%9)]||'">'||round((100+random()*90000)::numeric,2)||'</price>'||
'<weight unit="ct">'||round((0.5+random()*10)::numeric,2)||'</weight>'||
'</product>')
FROM generate_series(1,30000) n;
END $$;
CREATE INDEX idx_catalog_sys ON pg_catalog_xml (system_code);
VACUUM ANALYZE pg_catalog_xml;
-- ----------------------------------------------------------------------------
-- 4. 物化视图: 从 JSON 抽取指标预聚合 (万亿级看板最快路径)
-- ----------------------------------------------------------------------------
DROP MATERIALIZED VIEW IF EXISTS mv_json_metrics CASCADE;
CREATE MATERIALIZED VIEW mv_json_metrics AS
SELECT system_code,
count(*) AS orders,
sum((order_json->'amount'->>'usd')::numeric) AS gmv_usd,
avg((order_json->'customer'->>'vip_level')::int) AS avg_vip
FROM pg_order_doc
GROUP BY system_code;
CREATE UNIQUE INDEX ON mv_json_metrics (system_code);
VACUUM ANALYZE mv_json_metrics;
-- 万亿级语境内存提示: 真实环境 pg_order_doc 为分区表 (按 created_utc 月分区) + 每分区独立
-- GIN/统计; 以下 JSON/XML 演示在 5 万/3 万行样本上给出可复现结论, 方法对万亿行等量放大即可。
-- ============================================================================
-- 5. 演示: JSON 与 XML 数据处理
-- ============================================================================
\echo '=== §1 JSON 基础算子: -> (取json) / ->> (取text) / #> (路径) / #>> (路径取text) ==='
SELECT '{"a":1,"b":"x"}'::jsonb -> 'a' AS j_a,
'{"a":1,"b":"x"}'::jsonb ->> 'a' AS t_a,
'{"c":{"d":9}}'::jsonb #> '{c,d}' AS path_j,
'{"c":{"d":9}}'::jsonb #>> '{c,d}' AS path_t;
\echo '=== §2 JSON 包含与键判断: @> (包含) / ? (顶层键) / ?| ?& (多键) ==='
SELECT ('{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb) AS contains,
('{"a":1,"b":2}'::jsonb ? 'a') AS has_key,
('{"a":1,"b":2,"c":3}'::jsonb ?& ARRAY['a','c']) AS has_all,
('{"a":1,"b":2,"c":3}'::jsonb ?| ARRAY['x','b']) AS has_any;
\echo '=== §3 JSONPath ($ 语法) + jsonb_path_query / jsonb_path_exists ==='
SELECT jsonb_path_query_first('{"price":99.5,"qty":3}'::jsonb, '$.price') AS price,
(jsonb_path_query_first('{"price":99.5,"qty":3}'::jsonb, '$.price'))::numeric AS price_num,
jsonb_path_exists('{"amount":{"usd":1200}}'::jsonb, '$.amount.usd ? (@ > 1000)') AS usd_gt_1000;
\echo '=== §4 业务检索: 各域「已支付(paid) 且 金额USD>1000」的 JSON 订单数 (jsonb_path_exists) ==='
SELECT system_code,
count(*) FILTER (WHERE jsonb_path_exists(order_json, '$.amount.usd ? (@ > 1000)')) AS paid_big,
count(*) AS total
FROM pg_order_doc
WHERE order_json @> '{"status":"paid"}'
GROUP BY system_code
ORDER BY system_code;
\echo '=== §5 GIN 索引检索: order_json @> {"status":"paid"} 命中数 (走 GIN, 计划形态由 V5 自检校验) ==='
SELECT count(*) AS paid_orders FROM pg_order_doc WHERE order_json @> '{"status":"paid"}';
\echo '=== §6 JSON 聚合: jsonb_agg (行->数组) 与 jsonb_object_agg (键->值) ==='
SELECT jsonb_agg(order_json->'order_no') AS order_nos,
jsonb_object_agg(order_json->>'order_no', (order_json->'amount'->>'usd')::numeric) AS order_to_usd
FROM pg_order_doc
WHERE system_code = 'CRM';
\echo '=== §7 JSON 变更: jsonb_set 改写金额.currency / jsonb_insert 追加审计标记 ==='
SELECT doc_id,
jsonb_set(order_json, '{amount,currency}', '"USD"') AS set_currency,
jsonb_insert(order_json, '{audit}', '{"by":"system","ts":"2026-10-21"}'::jsonb) AS with_audit
FROM pg_order_doc
WHERE doc_id <= 3
ORDER BY doc_id;
\echo '=== §8 XML 存储与 xpath(): 抽取 <name> 与 <price> 属性 ==='
SELECT cat_id,
(xpath('/product/name/text()', catalog_xml))[1]::text AS name,
(xpath('/product/price/@currency', catalog_xml))[1]::text AS currency,
(xpath('/product/price/text()', catalog_xml))[1]::text::numeric AS price
FROM pg_catalog_xml
WHERE cat_id <= 3
ORDER BY cat_id;
\echo '=== §9 xpath_exists(): 筛选价格 > 50000 的产品 (XML 谓词) ==='
SELECT count(*) FILTER (WHERE xpath_exists('/product[price > 50000]', catalog_xml)) AS high_price_products,
count(*) AS total
FROM pg_catalog_xml;
\echo '=== §10 XML 构造: xmlelement + xmlforest 反向生成文档 ==='
SELECT xmlserialize(document
xmlelement(name product,
xmlforest('Diamond Ring' AS name, 1299.00 AS price, 'USD' AS currency))
AS text) AS built_xml;
\echo '=== §11 半结构化边界: 热路径 vip_level 走关系 B-tree 索引 (生成列, 而非扫描 JSON) ==='
SELECT count(*) FILTER (WHERE vip_level = 5) AS vip5_orders, count(*) AS total FROM pg_order_doc;
\echo '=== §12 国际时区: JSON 订单 created_utc 经各 Office 本地时区折算, 证明跨日界 ==='
SELECT count(DISTINCT (created_utc AT TIME ZONE
CASE country_code
WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'HK' THEN 'Asia/Hong_Kong'
WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo'
WHEN 'DE' THEN 'Europe/Berlin' WHEN 'FR' THEN 'Europe/Paris'
WHEN 'GB' THEN 'Europe/London' WHEN 'SG' THEN 'Asia/Singapore'
WHEN 'AU' THEN 'Australia/Sydney' WHEN 'IN' THEN 'Asia/Kolkata'
ELSE 'UTC' END)::date) AS distinct_local_days
FROM pg_order_doc;
\echo '=== §13 国际多币种: JSON 内 {orig,currency,usd}; 原币合计 ≠ USD 合计 (不可跨币相加) ==='
SELECT round(SUM((order_json->'amount'->>'orig')::numeric),2) AS orig_sum,
round(SUM((order_json->'amount'->>'usd')::numeric),2) AS usd_sum,
count(DISTINCT order_json->'amount'->>'currency') AS n_currencies
FROM pg_order_doc;
\echo '=== §14 万亿级: GIN 索引避免全表扫描 —— 对比 无索引 / 有索引 的扫描块数 ==='
SELECT shared_blks AS noidx_blks
FROM pg_plan_stats('SELECT count(*) FROM pg_order_doc WHERE order_json @> ''{"status":"paid"}''');
-- 临时关闭 seqscan 以稳定让规划器选择 GIN (演示高并发检索路径)
DO $$ BEGIN PERFORM set_config('enable_seqscan','off',true); END $$;
SELECT shared_blks AS idx_blks
FROM pg_plan_stats('SELECT count(*) FROM pg_order_doc WHERE order_json @> ''{"status":"paid"}''');
DO $$ BEGIN PERFORM set_config('enable_seqscan','on',true); END $$;
\echo '=== §15 物化视图预聚合: 从 JSON 抽取指标, 8 行 + 扫描块数 << 全表 ==='
SELECT system_code, orders, round(gmv_usd,2) AS gmv_usd, round(avg_vip,3) AS avg_vip
FROM mv_json_metrics ORDER BY system_code;
-- ============================================================================
-- 6. 自检 V1–V18 (功能正确性 + 计划形态 + 边界约束)
-- ============================================================================
DO $$
DECLARE
v_a bigint; v_c bigint; exact_sum numeric; est1 numeric; est2 numeric;
full_blks bigint; mv_blks bigint; mv_rows bigint; cross_rows bigint;
dist_local int; utc_days int; orig_sum numeric; usd_sum numeric;
noidx_blks bigint; idx_blks bigint;
BEGIN
-- V1 JSON 算子 -> 与 ->>
PERFORM pg_demo_v('V1_json_ops',
(SELECT CASE WHEN ('{"a":1}'::jsonb)->'a' = '1'::jsonb
AND ('{"a":1}'::jsonb)->>'a' = '1' THEN 1 ELSE 0 END),
'-> 取json, ->> 取text');
-- V2 包含与键判断 @> / ? / ?& / ?|
PERFORM pg_demo_v('V2_containment',
(SELECT CASE WHEN ('{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb)
AND ('{"a":1}'::jsonb ? 'a')
AND ('{"a":1,"b":2}'::jsonb ?& ARRAY['a','b'])
AND ('{"a":1,"b":2}'::jsonb ?| ARRAY['x','a']) THEN 1 ELSE 0 END),
'@> 包含, ?/?&/?| 键判断');
-- V3 JSONPath
PERFORM pg_demo_v('V3_jsonpath',
(SELECT CASE WHEN (jsonb_path_query_first('{"price":99.5}'::jsonb,'$.price'))::numeric = 99.5
AND jsonb_path_exists('{"amount":{"usd":1200}}'::jsonb,'$.amount.usd ? (@ > 1000)') THEN 1 ELSE 0 END),
'$.price 取值; $.amount.usd 谓词');
-- V4 业务 JSONPath 检索命中数 > 0
SELECT count(*) INTO v_a FROM pg_order_doc
WHERE order_json @> '{"status":"paid"}'
AND jsonb_path_exists(order_json,'$.amount.usd ? (@ > 1000)');
PERFORM pg_demo_v('V4_jsonpath_hit',
(SELECT CASE WHEN v_a > 0 THEN 1 ELSE 0 END),
'paid & usd>1000 命中数='||v_a);
-- V5 GIN 索引生效 (enable_seqscan=off 稳定选索引)
PERFORM set_config('enable_seqscan','off',true);
PERFORM pg_demo_v('V5_gin_index_plan',
(SELECT CASE WHEN pg_plan_text_has('SELECT count(*) FROM pg_order_doc WHERE order_json @> ''{"status":"paid"}''', 'Bitmap') THEN 1 ELSE 0 END),
'GIN/Bitmap 计划节点');
PERFORM set_config('enable_seqscan','on',true);
-- V6 JSON 聚合 jsonb_agg 数组长度 = 行数
SELECT jsonb_array_length(jsonb_agg(order_json->'order_no')) INTO v_a
FROM pg_order_doc WHERE system_code='CRM';
PERFORM pg_demo_v('V6_json_agg',
(SELECT CASE WHEN v_a = (SELECT count(*) FROM pg_order_doc WHERE system_code='CRM') THEN 1 ELSE 0 END),
'jsonb_agg 行->数组 长度一致');
-- V7 jsonb_object_agg: doc_id=1 的 order_no -> usd 映射键存在
PERFORM pg_demo_v('V7_object_agg',
(SELECT CASE WHEN jsonb_object_agg(order_json->>'order_no', (order_json->'amount'->>'usd')::numeric) ? 'ORD-00000001'
THEN 1 ELSE 0 END
FROM pg_order_doc WHERE doc_id=1),
'object_agg 键存在');
-- V8 jsonb_set / jsonb_insert 生效
PERFORM pg_demo_v('V8_json_mutate',
(SELECT CASE WHEN (jsonb_set(order_json,'{amount,currency}','"USD"') -> 'amount' ->> 'currency') = 'USD'
AND (jsonb_insert(order_json,'{audit}','{"by":"sys"}'::jsonb) ? 'audit')
THEN 1 ELSE 0 END
FROM pg_order_doc WHERE doc_id=1),
'jsonb_set/jsonb_insert 写入');
-- V9 XML xpath 抽取
PERFORM pg_demo_v('V9_xml_xpath',
(SELECT CASE WHEN (xpath('/product/name/text()',
'<product><name>Diamond</name><price currency="USD">99</price></product>'::xml))[1]::text = 'Diamond'
AND (xpath('/product/price/@currency',
'<product><name>Diamond</name><price currency="USD">99</price></product>'::xml))[1]::text = 'USD'
THEN 1 ELSE 0 END),
'xpath 抽取 name 与 price@currency');
-- V10 xpath_exists 谓词
PERFORM pg_demo_v('V10_xpath_exists',
(SELECT CASE WHEN xpath_exists('/product[price > 50]',
'<product><price>99</price></product>'::xml)
AND NOT xpath_exists('/product[price > 500]',
'<product><price>99</price></product>'::xml)
THEN 1 ELSE 0 END),
'XML 数值谓词 xpath_exists');
-- V11 XML 构造
PERFORM pg_demo_v('V11_xml_construct',
(SELECT CASE WHEN position('<name>Diamond</name>'
in xmlserialize(document xmlelement(name product, xmlforest('Diamond' AS name)) as text)) > 0
THEN 1 ELSE 0 END),
'xmlelement+xmlforest 含 <name>Diamond</name>');
-- V12 生成列从 JSON 抽取 (半结构化边界)
PERFORM pg_demo_v('V12_generated_col',
(SELECT CASE WHEN vip_level = ((order_json->'customer'->>'vip_level')::int)
THEN 1 ELSE 0 END FROM pg_order_doc WHERE doc_id=1),
'vip_level 生成列=JSON 内值');
-- V13 国际时区: 本地日 distinct > 1 且 存在跨日界行
SELECT count(DISTINCT (created_utc AT TIME ZONE
CASE country_code
WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'HK' THEN 'Asia/Hong_Kong'
WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo'
WHEN 'DE' THEN 'Europe/Berlin' WHEN 'FR' THEN 'Europe/Paris'
WHEN 'GB' THEN 'Europe/London' WHEN 'SG' THEN 'Asia/Singapore'
WHEN 'AU' THEN 'Australia/Sydney' WHEN 'IN' THEN 'Asia/Kolkata'
ELSE 'UTC' END)::date) INTO dist_local
FROM pg_order_doc;
SELECT count(*) INTO cross_rows FROM pg_order_doc
WHERE (created_utc AT TIME ZONE
CASE country_code
WHEN 'CN' THEN 'Asia/Shanghai' WHEN 'HK' THEN 'Asia/Hong_Kong'
WHEN 'US' THEN 'America/New_York' WHEN 'JP' THEN 'Asia/Tokyo'
WHEN 'DE' THEN 'Europe/Berlin' WHEN 'FR' THEN 'Europe/Paris'
WHEN 'GB' THEN 'Europe/London' WHEN 'SG' THEN 'Asia/Singapore'
WHEN 'AU' THEN 'Australia/Sydney' WHEN 'IN' THEN 'Asia/Kolkata'
ELSE 'UTC' END)::date <> created_utc::date;
PERFORM pg_demo_v('V13_timezone_boundary',
(SELECT CASE WHEN dist_local > 1 AND cross_rows > 0 THEN 1 ELSE 0 END),
'distinct_local_days='||dist_local||' cross_day_rows='||cross_rows);
-- V14 国际多币种: 原币合计 ≠ USD 合计
SELECT round(SUM((order_json->'amount'->>'orig')::numeric),2),
round(SUM((order_json->'amount'->>'usd')::numeric),2)
INTO orig_sum, usd_sum FROM pg_order_doc;
PERFORM pg_demo_v('V14_multicurrency',
(SELECT CASE WHEN orig_sum <> usd_sum THEN 1 ELSE 0 END),
'orig_sum='||orig_sum||' usd_sum='||usd_sum||' (跨币种不可加)');
-- V15 万亿级: GIN 降块数 (无索引 vs 有索引)
SELECT shared_blks INTO noidx_blks FROM pg_plan_stats('SELECT count(*) FROM pg_order_doc WHERE order_json @> ''{"status":"paid"}''');
PERFORM set_config('enable_seqscan','off',true);
SELECT shared_blks INTO idx_blks FROM pg_plan_stats('SELECT count(*) FROM pg_order_doc WHERE order_json @> ''{"status":"paid"}''');
PERFORM set_config('enable_seqscan','on',true);
PERFORM pg_demo_v('V15_gin_blocks',
(SELECT CASE WHEN idx_blks <= noidx_blks THEN 1 ELSE 0 END),
'idx_blks='||idx_blks||' noidx_blks='||noidx_blks);
-- V16 MV 预聚合: 8 行 + 扫描块数 << 全表
SELECT count(*) INTO mv_rows FROM mv_json_metrics;
SELECT shared_blks INTO full_blks FROM pg_plan_stats('SELECT sum((order_json->''amount''->>''usd'')::numeric) FROM pg_order_doc');
SELECT shared_blks INTO mv_blks FROM pg_plan_stats('SELECT sum(gmv_usd) FROM mv_json_metrics');
PERFORM pg_demo_v('V16_mv_approx',
(SELECT CASE WHEN mv_rows=8 AND mv_blks < full_blks THEN 1 ELSE 0 END),
'mv_rows='||mv_rows||' mv_blks='||mv_blks||' full_blks='||full_blks);
-- V17 XML 文档合法性: 全部 catalog_xml IS DOCUMENT
PERFORM pg_demo_v('V17_xml_document',
(SELECT CASE WHEN bool_and(catalog_xml IS DOCUMENT) THEN 1 ELSE 0 END FROM pg_catalog_xml),
'全部 XML 为合法 document');
-- V18 全部通过: 排除自身后, 17 项功能检查必须全为 1
PERFORM pg_demo_v('V18_all_pass',
(SELECT CASE WHEN count(*)=17 AND count(*)=count(*)FILTER(WHERE pass=1) THEN 1 ELSE 0 END
FROM pg_demo_check WHERE name <> 'V18_all_pass'),
'17 functional checks all pass=1');
END $$;
-- ----------------------------------------------------------------------------
-- 7. 自检汇总 (SELECT 输出, 不受 client_min_messages=warning 抑制)
-- ----------------------------------------------------------------------------
SELECT name, pass, detail FROM pg_demo_check ORDER BY name;
SELECT CASE WHEN count(*)=count(*) FILTER (WHERE pass=1) THEN 'ALL PASS' ELSE 'SOME FAILED' END AS result,
count(*) AS total,
count(*) FILTER (WHERE pass=1) AS passed
FROM pg_demo_check;
转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/geovindu/article/details/169510487
