GaussDB(DWS)结果集不稳定常见场景和解决办法
本文主要介绍几种在用户使用中场景的结果集不稳定的问题,此类问题大部分都是由于用户使用不当或用法不合理导致,通过合理的业务使用可以避免此类问题。本文介绍几种常见的场景和解决办法供使用者参考。
1. 窗口函数中使用排序后取部分结果
1.1 典型场景
postgres=# select * from t3 order by 1,2,3;
a | b | c
---+---+---
1 | 2 | 1
1 | 2 | 2
1 | 2 | 3
(3 rows)
postgres=#
postgres=#
postgres=# select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1;
c | rn
---+----
1 | 1
(1 row)
postgres=# select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1;
c | rn
---+----
3 | 1
(1 row)
1.2 问题原因
如上所示,执行同一条语句:select c,rn from (select c,row_number() over(order by a,b) as rn from t3) where rn = 1;两次查询结果不同。问题原因是因为在窗口函数的排序列a,b上存在重复值1,2,而且重复值在c列上的值不同,就会造成每次按照a,b列排序结果取第一条时,取的数据是随机的,造成结果集不稳定。
1.3 解决方法
这种场景需要将取值列c列上也加到排序中,使排序结果取到的第一条数据固定。
postgres=# select c,rn from (select c,row_number() over(order by a,b,c) as rn from t3) where rn = 1;
c | rn
---+----
1 | 1
(1 row)
postgres=#
postgres=# select c,rn from (select c,row_number() over(order by a,b,c) as rn from t3) where rn = 1;
c | rn
---+----
1 | 1
(1 row)
1.4 扩展场景:
上述场景中,diag_code列不在row_number()的排序列中,当排序列有重复值时,而重复值里diag_code的值不一样,就会造成随机取一条,diag_code用于join,就会导致结果集不稳定。
2. 子视图/子查询中使用排序
2.1 典型场景
create table test(a serial ,b int);
insert into test(b) values(1);
insert into test(b) values select b from test;
…
insert into test(b) values select b from test;
create view v as select * from test order by a;
--问题SQL:
select * from v limit 1;
a | b
---+---
3 | 1
(1 row)
select * from (select *from test order by a) limit 10;
a | b
----+---
14 | 1
(1 row)
select *from test order by a limit 10;
a | b
---+---
1 | 1
(1 row)
2.2 问题原因
对于视图和子查询中order by是无效的,要想保证结果的有序,必须在最外层的查询中加oder by。
2.3 解决方法
不要在视图和子查询中使用order by,如果需要保证结果有序,需要在最外层查询使用order by。
3. 子查询limit
3.1 典型场景
select * from (select a from test limit 1 ) order by 1;
a
---
5
(1 row)
select * from (select a from test limit 1 ) order by 1;
a
---
1
(1 row)
3.2 问题原因
子查询中的limit会导致结果的随机,从而导致最终结果的随机。
3.3 解决方法
要保证最终结果集的稳定,避免在子查询中使用limit。
4. 使用string_agg
4.1 典型场景
postgres=# select * from employee;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+---------+------+---------------------+-------+------+--------
7654 | MARTIN | SALEMAN | 7698 | 2022-11-08 00:00:00 | 12000 | 1400 | 30
7566 | JONES | MANAGER | 7839 | 2022-11-08 00:00:00 | 32000 | 0 | 20
7499 | ALLEN | SALEMAN | 7698 | 2022-11-08 00:00:00 | 16000 | 300 | 30
(3 rows)
postgres=#
postgres=# select count(*) from
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t1 ,
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t2
postgres-# where t1.string_agg = t2.string_agg;
count
-------
2
(1 row)
postgres=#
postgres=# select count(*) from
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t1 ,
postgres-# (select deptno, string_agg(ename, ',') from employee group by deptno) t2
postgres-# where t1.string_agg = t2.string_agg;
count
-------
1
(1 row)
postgres=#
4.2 问题原因
String_agg函数的作用是将组内的数据合并成一行,但是如果用户用法是string_agg(ename, ',')这种情况下,结果集就是不稳定的,因为没有指定组合的顺序。例如,上述语句中,对于 select deptno, string_agg(ename, ',') from employee group by deptno;输出结果既可以是:
30 | ALLEN,MARTIN
也可能是:
30 |MARTIN,ALLEN
上述两个结果都是合理的,因此上述关联场景下,有可能出现t1这个subquery中的结果和t2这个subquery中的结果对于deptno=30的时候的输出结果是不一样的。
4.3 解决方法
String_agg中增加order by排序,保证按顺序拼接:
select count(*) from
(select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t1 ,
(select deptno, string_agg(ename, ',' order by ename desc) from employee group by deptno) t2
where t1.string_agg = t2.string_agg;
5. 数据库兼容模式
5.1 典型场景
database1(TD兼容模式):
td=# select '' is null;
isnull
--------
f
(1 row)
database2(ORA兼容模式):
postgres=# select '' is null;
isnull
--------
t
(1 row)
5.2 问题原因
本例中,是由于不同数据库兼容模式下空串与null的比较不同导致的。
目前,DWS支持三种数据库兼容模式:Oracle、TD、MySql,不同兼容模式下语法和行为存在差异,兼容性差异说明可参考:https://support.huaweicloud.com/devg-813-dws/dws_04_0042.html,
用户常见的TD和Oracle兼容性行为差异还可以参考:https://bbs.huaweicloud.com/blogs/176361。
不同兼容模式下的database表现出不同的兼容性行为属于正常现象。可以通过查看select datname, datcompatibility from pg_database;确认数据库兼容性设置是否相同。
5.3 解决方法
这种场景下只能将两个环境的database的兼容性模式设置为一致的才能解决。Database的datcompatibility属性不支持alter,只能通过新建数据库的方法,在创建数据库时指定相同的DBCOMPATIBILITY属性解决。
5.4 扩展场景
oracle兼容模式下,空串''等价为null,而null除了is判断外,用其他比较操作的返回结果均为null,因此会有以下行为:
postgres=# select * from t1 limit 1;
a | b | c
---+---+---
3 | 1 | 1
(1 row)
postgres=# select * from t1 where b = '' limit 1;
a | b | c
---+---+---
(0 rows)
postgres=# select * from t1 where b != '' limit 1;
a | b | c
---+---+---
(0 rows)
6. 数据库兼容性行为配置项behavior_compat_options设置不同
6.1 典型场景
database1:
select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-28 00:00:00
(1 row)
database2:
select add_months('2018-02-28',3) from dual;
add_months
---------------------
2018-05-31 00:00:00
(1 row)
6.2 问题原因
数据库兼容性行为配置项behavior_compat_options不同也会导致部分行为不同,该参数选项可参考产品文档:https://support.huaweicloud.com/devg-813-dws/dws_04_0945.html中的相关选项描述。
6.3 解决方法
需要将该参数设置为一致解决。该参数类型为USERSET类型,可session级别设置或集群级修改。
7. 自定义函数属性设置不合理
7.1 典型场景
create function get_count() returns int
SHIPPABLE
as $$
declare
result int;
begin
result = (select count(*) from test); --test表是hash表
return result;
end;
$$
language plpgsql;
调用该函数发现以下现象:
postgres=# select get_count();
get_count
-----------
2106
(1 row)
postgres=# select get_count() from t_src;
get_count
-----------
1032
(1 row)
7.2 问题原因
这是因为由于这个函数指定了SHIPPABLE的函数属性,因此生成计划时该函数会下推到DN上执行,该函数下推到DN后,由于函数定义中的test表是hash表,因此每个DN上只有该表的一部分数据,所以select count(*) from test返回的结果不是test表全量数据的结果,而是每个DN上部分数据的结果,因此导致加上from表后函数返回预期发生变化。参考:https://bbs.huaweicloud.com/blogs/250351
7.3 解决方法
以下两种方法任选其一即可(推荐第一种方法):
(1)将函数改为不下推:alter function get_count() not shippable;
(2)将函数中用到的表改为复制表,这样每个DN上都是一份该表的全量数据,即使下推到DN执行,也能保证结果集符合预期。
8. 使用unlog表
8.1 典型场景
用户使用unlog表后,在集群重启后,关联查询结果集异常,查看unlog表缺少部分数据。
8.2 问题原因
如果设置max_query_retry_times为0,且在建表时指定UNLOGGED关键字,则创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中,这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动截断,会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。因此当集群发生异常重启(进程重启、节点故障、集群重启)时,会导致部分内存中的数据未及时落盘,造成部分数据丢失,从而导致结果集异常。
8.3 解决方法
unlog表在集群异常情况下的安全性无法保证,一般不能作为业务表使用,更多的场景是作为临时表使用。当出现集群故障后,为了保证数据正常,需要重建unlog表或将数据重新入库。
9. 8.2.0版本及以后,窗口函数结果集无序
9.1 典型场景
业务使用row_number作为窗口函数,升级到8.2.0以后,结果集无序。如下图所示,两次查询得到的结果集顺序不一样:
postgres=# select * from (select a,b, row_number() over (order by b) from t) row_num;
a | b | row_number
----+---+------------
2 | 8 | 8
2 | 8 | 9
6 | 8 | 10
36 | 8 | 11
8 | 4 | 1
38 | 4 | 2
1 | 4 | 3
1 | 4 | 4
2 | 5 | 5
9 | 5 | 6
39 | 5 | 7
(11 rows)
postgres=# select * from (select a,b, row_number() over (order by b) from t) row_num;
a | b | row_number
----+---+------------
39 | 5 | 5
2 | 5 | 6
9 | 5 | 7
8 | 4 | 1
38 | 4 | 2
1 | 4 | 3
1 | 4 | 4
6 | 8 | 8
2 | 8 | 9
2 | 8 | 10
36 | 8 | 11
(11 rows)
9.2 问题原因
这是因为在8.2.0版本在窗口函数(windowagg)支持基于value partition的执行计划,以提高窗口函数的性能。而此行为导致的一个影响就是输出结果集变成无序的,与8.1.3等版本行为发生变化。
9.3 解决方法
需要在最外层手动添加order by解决,或者修改enable_value_partitioin为off关闭value partition的重分布计划。
- 点赞
- 收藏
- 关注作者
评论(0)