AntDB 数据库分布式功能-FQS增强8

标量子查询

# 测试用表和数据请参加附录
# 带动态参数的标量子查询
antdb=# prepare s_scalar_subquery(int) as SELECT * FROM (SELECT province FROM fqs_window_test WHERE id = $1) a;
PREPARE
antdb=# EXPLAIN (verbose) execute s_scalar_subquery(2);
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: a.province
   Node expr: $1
   Remote query: SELECT province FROM (SELECT fqs_window_test.province FROM public.fqs_window_test fqs_window_test WHERE (fqs_window_test.id = $1)) a
(4 rows)


# 带静态常量的标量子查询
antdb=# EXPLAIN (verbose) SELECT * FROM (SELECT province FROM fqs_window_test WHERE id = 2) a;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: a.province
   Node/s: DN1
   Remote query: SELECT province FROM (SELECT fqs_window_test.province FROM public.fqs_window_test fqs_window_test WHERE (fqs_window_test.id = 2)) a
(4 rows)

包含 sqlvalueFunc

# 测试用表和数据请参加附录
# 包含 sqlvalueFunc（例如:CURRENT_TIMESTAMP）的函数支持 FQS，分片键是动态绑定变量的
antdb=# prepare s_sqlvalueFunc(int) as INSERT INTO fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, 1, 1, 1, CURRENT_TIMESTAMP);
PREPARE
antdb=# EXPLAIN (verbose) execute s_sqlvalueFunc(2);
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: ($1), 1, 1, 1, ((CURRENT_TIMESTAMP)::timestamp without time zone)
   Node expr: $1
   Remote query: INSERT INTO public.fqs_pgbench_history AS fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, 1, 1, 1, CURRENT_TIMESTAMP)
(4 rows)


# 包含sqlvalueFunc（例如:CURRENT_TIMESTAMP）的函数支持FQS，分片键是静态常量的
antdb=# EXPLAIN (verbose) INSERT INTO fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES (2, 1, 1, 1, CURRENT_TIMESTAMP);
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: 2, 1, 1, 1, ((CURRENT_TIMESTAMP)::timestamp without time zone)
   Node expr: 2
   Remote query: INSERT INTO public.fqs_pgbench_history AS fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES (2, 1, 1, 1, CURRENT_TIMESTAMP)
(4 rows)