单节点表DML操作

前置条件

• 当前表是否为单节点表，可通过如下步骤确认是否正确创建单节点表。

  -- 创建的单节点表t_ng1。
  gaussdb=# CREATE TABLE t_ng1(c1 int, c2 int) DISTRIBUTE BY REPLICATION TO GROUP sys_single_group1;
  gaussdb=# \d+ t_ng1
                          Table "public.t_ng1"
   Column |  Type   | Modifiers | Storage | Stats target | Description
  --------+---------+-----------+---------+--------------+-------------
   c1     | integer |           | plain   |              |
   c2     | integer |           | plain   |              |
  Has OIDs: no
  Distribute By: REPLICATION
  Location Nodes: datanode1
  Options: orientation=row, is_single_node=true, logical_repl_node=-1, compression=no, storage_type=USTORE, segment=off
  
  gaussdb=# SELECT reloptions FROM pg_class WHERE relname = 't_ng1';
                                                  reloptions
  -----------------------------------------------------------------------------------------------------------
   {orientation=row,is_single_node=true,logical_repl_node=-1,compression=no,storage_type=USTORE,segment=off}
  (1 row)

• 当前单节点表所在节点组Node Group是否为单节点组sys_single_group，可通过如下步骤确认单节点表所属的节点组Node Group信息。

  --查询pgxc_class系统表，确认单节点表所属节点组信息，其中group name必须是sys_single_group[%d]。
  gaussdb=# SELECT pgroup, nodeoids FROM pgxc_class WHERE pcrelid = (SELECT oid FROM pg_class WHERE relname = 't_ng1');
        pgroup       | nodeoids
  -------------------+----------
   sys_single_group1 | 16385
  (1 row)
  gaussdb=# DROP TABLE t_ng1;

规格约束

• 规格：
  1. 单节点表支持INSERT、UPDATE、DELETE、UPSERT、MERGE、SELECT以及SELECT FOR UPDATE | SHARED（FOR UPDATE | SHARED场景和原Ustore/Astore约束保持一致）常用DML语法语句。
  2. 单节点表支持和非单节点表（如：分布表、分区表）交互DML操作，分布表包括：复制表、Range分布表、List分布表、Hash分布表、hash bucket表以及单节点表；分区表包括：二级分区表以及一级分区表。
  3. 单节点表支持SMP、存储过程内使用单节点表、支持TRIGGER。
  4. 单节点表分布属性保持和复制表分布属性一致，单节点表的功能规格整体保持和复制表支持功能规格一致。
  5. 通过DATABASE LINK对远端单节点表进行访问后（如：DML操作），会在系统表pg_foreign_table的ftoptions字段中标记is_single_datanode_table=true的信息（仅单节点表存在该标识）。单节点表转换为其他表类型后，再次通过DML操作才会更新pg_foreign_table系统表中的ftoptions字段。
• 约束：
  1. MERGE INTO USING子句中，目标表和源表均为单节点表时，需要为相同单节点组Node Group下的单节点表。
  2. 单节点表支持INSERT ALL，但INSERT ALL的所有目标表必须为相同单节点组Node Group下的单节点表。
  3. 支持通过DATABASE LINK对远端单节点表进行查询或DML操作。但是包含DATABASE LINK单节点表的SQL语句内，不支持包含DATABASE LINK非单节点表。
  4. 当使用“/*+ stream */ Hint”指定连接顺序时，若涉及分布式表与单节点表的混合连接，优化器可能忽略该Hint，主要由于优化器自动评估执行成本，若发现比Hint更优的方案，将优先采用替代计划。
  5. 当级联收集单节点表的分区表的统计信息，且PARTITION_MODE为ALL时，其行为将转换为ALL COMPLETE模式。
  6. 单节点表仅支持基于单节点组Node Group功能使用。
  7. 单节点表和非单节点表交互，不支持进行SMP操作。
  8. 单节点表和不同DN的单节点表交互，不支持进行SMP操作。

使用示例

-- sys_single_group1、sys_single_group2为单节点组Node Group。
-- 创建单节点表。
gaussdb=# CREATE TABLE t_sig1(c1 int, c2 int, c3 int) TO GROUP sys_single_group1;
gaussdb=# CREATE TABLE t_sig2(c1 int primary key, c2 int, c3 int) TO GROUP sys_single_group2;
-- 单节点表导入数据。
gaussdb=# INSERT INTO t_sig1 VALUES(generate_series(1, 20), generate_series(21, 40), generate_series(1, 20));

场景：单节点表典型DML

-- 单节点表SELECT。
gaussdb=# SELECT t_sig1.* FROM t_sig1 WHERE t_sig1.c1 > 5 AND (t_sig1.*)::t_sig1 IS NOT NULL ORDER BY 1 LIMIT 5;
 c1 | c2 | c3
----+----+----
  6 | 26 |  6
  7 | 27 |  7
  8 | 28 |  8
  9 | 29 |  9
 10 | 30 | 10
(5 rows)
-- 单节点表SELECT FOR UPDATE | SHAR。
gaussdb=# SELECT t_sig1.* FROM t_sig1 WHERE t_sig1.c1 > 15 ORDER BY 1, 2 FOR UPDATE;
 c1 | c2 | c3
----+----+----
 16 | 36 | 16
 17 | 37 | 17
 18 | 38 | 18
 19 | 39 | 19
 20 | 40 | 20
(5 rows)
gaussdb=# SELECT t_sig1.* FROM t_sig1 WHERE t_sig1.c1 > 15 ORDER BY 1, 2 FOR SHARE;
 c1 | c2 | c3
----+----+----
 16 | 36 | 16
 17 | 37 | 17
 18 | 38 | 18
 19 | 39 | 19
 20 | 40 | 20
(5 rows)
-- 单节点表INSERT SELECT。
gaussdb=# INSERT INTO t_sig2 SELECT * FROM t_sig1;
-- 单节点表UPSERT。
gaussdb=# INSERT INTO t_sig1 VALUES (1, 21) ON duplicate key UPDATE c2 = c2 + 100;
-- 单节点表UPDATE。
gaussdb=# UPDATE t_sig1 SET c2 = (SELECT t_sig1.c1 FROM t_sig1 WHERE t_sig1.c2 = 20) WHERE c1 = 100;
-- 单节点表DELETE。
gaussdb=# DELETE FROM t_sig1 WHERE t_sig1.c1 = 100;
-- 单节点表MERGE。
gaussdb=# CREATE TABLE t_sig11(c1 int, c2 int, c3 int) TO GROUP sys_single_group1;
gaussdb=# INSERT INTO t_sig11 VALUES(100, 100, 100), (101, 101, 101);
gaussdb=# MERGE INTO t_sig1 AS mt1 USING t_sig11 AS mt2 ON mt1.c1 = mt2.c1 WHEN MATCHED THEN UPDATE SET mt1.c2 = 100 WHEN NOT MATCHED THEN INSERT (c1, c2, c3) VALUES (100, 200, 300);
-- 删除单节点表。
gaussdb=# DROP TABLE t_sig1;
gaussdb=# DROP TABLE t_sig2;
gaussdb=# DROP TABLE t_sig11;

场景：单节点表和分布表交互

-- 创建表。
gaussdb=# CREATE TABLE t_rep(c1 int, c2 int) DISTRIBUTE BY replication; -- 复制表
gaussdb=# CREATE TABLE t_dis(c1 int, c2 int) DISTRIBUTE BY hash(c1); -- hash分布表
gaussdb=# CREATE TABLE t_sig1(c1 int, c2 int) DISTRIBUTE BY replication TO GROUP sys_single_group1; -- 单节点表
gaussdb=# CREATE TABLE t_sig2(c1 int, c2 int) DISTRIBUTE BY replication TO GROUP sys_single_group2; -- 单节点表
gaussdb=# CREATE TABLE t_sig11(c1 int, c2 int) DISTRIBUTE BY replication TO GROUP sys_single_group1;-- 单节点表
gaussdb=# INSERT INTO t_rep SELECT v,v FROM generate_series(1,10) AS v;
gaussdb=# INSERT INTO t_sig1 VALUES (generate_series(11, 20), generate_series(11, 20));
gaussdb=# INSERT INTO t_dis VALUES (generate_series(11, 20), generate_series(11, 20));
gaussdb=# INSERT INTO t_sig2 VALUES (generate_series(11, 20), generate_series(11, 20));
-- UNION集合场景。
gaussdb=# SELECT * FROM t_sig1 UNION all SELECT * FROM t_sig2 ORDER BY 1;
 c1 | c2
----+----
 11 | 11
 11 | 11
 12 | 12
 12 | 12
 13 | 13
 13 | 13
 14 | 14
 14 | 14
 15 | 15
 15 | 15
 16 | 16
 16 | 16
 17 | 17
 17 | 17
 18 | 18
 18 | 18
 19 | 19
 19 | 19
 20 | 20
 20 | 20
(20 rows)
gaussdb=# SELECT * FROM t_sig1 UNION all SELECT * FROM t_rep ORDER BY 1 LIMIT 5;
 c1 | c2
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |  5
(5 rows)
gaussdb=# SELECT * FROM t_sig1 UNION ALL SELECT * FROM t_dis ORDER BY 1 LIMIT 5 OFFSET 2;
 c1 | c2
----+----
 12 | 12
 12 | 12
 13 | 13
 13 | 13
 14 | 14
(5 rows)
-- JOIN场景。
gaussdb=# SELECT * FROM t_sig1 LEFT JOIN t_dis ON t_sig1.c1 = t_dis.c1 ORDER BY 1,2 LIMIT 5;
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 11 | 11 | 11
 12 | 12 | 12 | 12
 13 | 13 | 13 | 13
 14 | 14 | 14 | 14
 15 | 15 | 15 | 15
(5 rows)
gaussdb=# SELECT * FROM t_sig1 LEFT JOIN t_rep ON t_sig1.c1 = t_rep.c1 + 10 ORDER BY 1,2 LIMIT 5;
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 11 |  1 |  1
 12 | 12 |  2 |  2
 13 | 13 |  3 |  3
 14 | 14 |  4 |  4
 15 | 15 |  5 |  5
(5 rows)
-- 相同单节点组Node Group上的单节点表下推场景。
-- SELECT 查询下推。
gaussdb=# EXPLAIN (costs off) SELECT * FROM t_sig1 WHERE c1 IN (SELECT c1 FROM t_sig11 WHERE c2 > 10 LIMIT 2);
                 QUERY PLAN
--------------------------------------------
 Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
   Node/s: datanode1
(2 rows)
-- INSERT SELECT下推。
gaussdb=# EXPLAIN (costs off) INSERT INTO t_sig1 SELECT * FROM t_sig11 WHERE c1 > 10;
                 QUERY PLAN
--------------------------------------------
 Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
   Node/s: (sys_single_group1) datanode1
(2 rows)
-- UPDATE下推。
gaussdb=# EXPLAIN (costs off) UPDATE t_sig1 SET (c1, c2) = (SELECT c1 + 10, c2 - c1 FROM t_sig11);
                 QUERY PLAN
--------------------------------------------
 Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
   Node/s: (sys_single_group1) datanode1
(2 rows)
-- DELETE下推。
gaussdb=# EXPLAIN (costs off) DELETE FROM t_sig1 WHERE c1 = (SELECT c1 FROM t_sig11 WHERE c2 > 10);
                 QUERY PLAN
--------------------------------------------
 Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
   Node/s: (sys_single_group1) datanode1
(2 rows)
-- JOIN下推。
gaussdb=# EXPLAIN (costs off) SELECT * FROM t_sig1, t_sig11 WHERE t_sig1.c1 = t_sig11.c2;
                 QUERY PLAN
--------------------------------------------
 Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
   Node/s: (sys_single_group1) datanode1
(2 rows)
-- UNION ALL下推。
gaussdb=# EXPLAIN (costs off) SELECT * FROM t_sig1 UNION ALL SELECT * FROM t_sig11 LIMIT 4;
                 QUERY PLAN
--------------------------------------------
 Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
   Node/s: (GenGroup) datanode1
(2 rows)

-- 单节点表SMP场景。
--注意：需要在DEBUG版本下设置query_dop=1002强制生效SMP。
gaussdb=# SET query_dop = 1002;
gaussdb=# SET enable_fast_query_shipping = off;
-- 范围查询。
gaussdb=# EXPLAIN (costs off) SELECT * FROM t_sig1;
                   QUERY PLAN
-------------------------------------------------
 Streaming (type: GATHER)
   Node/s: (GenGroup) datanode1
   ->  Streaming(type: LOCAL GATHER dop: 1/2)
         Spawn ON: (sys_single_group1) datanode1
         ->  Seq Scan ON t_sig1
(5 rows)
-- 聚集操作。
gaussdb=# EXPLAIN (costs off) SELECT count(*) FROM t_sig1;
                      QUERY PLAN
-------------------------------------------------------
 Streaming (type: GATHER)
   Node/s: (GenGroup) datanode1
   ->  Aggregate
         ->  Streaming(type: LOCAL GATHER dop: 1/2)
               Spawn ON: (sys_single_group1) datanode1
               ->  Aggregate
                     ->  Seq Scan ON t_sig1
(7 rows)
-- 点查。
gaussdb=# EXPLAIN (costs off) SELECT avg(c1) as avg, c1 FROM t_sig1 WHERE c1 > 15 GROUP BY c1 HAVING c1 < 19 ORDER BY 1, 2;
                              QUERY PLAN
-----------------------------------------------------------------------
 Streaming (type: GATHER)
   Node/s: (GenGroup) datanode1
   ->  Sort
         Sort Key: (pg_catalog.avg((avg(c1)))), c1
         ->  Streaming(type: LOCAL GATHER dop: 1/2)
               Spawn ON: (sys_single_group1) datanode1
               ->  HashAggregate
                     GROUP BY Key: c1
                     ->  Streaming(type: SPLIT REDISTRIBUTE dop: 2/2)
                           Spawn ON: (sys_single_group1) datanode1
                           ->  HashAggregate
                                 GROUP BY Key: c1
                                 ->  Seq Scan ON t_sig1
                                       Filter: ((c1 > 5) AND (c1 < 9))
(14 rows)
-- 删除表。
gaussdb=# DROP TABLE t_rep;
gaussdb=# DROP TABLE t_dis;
gaussdb=# DROP TABLE t_sig1;
gaussdb=# DROP TABLE t_sig2;
gaussdb=# DROP TABLE t_sig11;