MatrixDB SQL 查询常见问题
本文档介绍 MatrixDB SQL 查询的常见问题。
1 ERROR: EvalPlanQual can not handle subPlan with Motion node
问题分析
当参数 gp_enable_global_deadlock_detector 设置为 on 后，锁的模式可能下降为 RowExclusiveLock，当执行更新分布键时，UPDATE操作会被拆分为 DELETE + INSERT, 此时并发的更新分布键，DELETE 操作不能执行 EvalPlanQual，INSERT 操作也不会 blocked，可能会生成多余的数据，为了防止这种现象，抛出这类错误。
复现步骤
session 0: create table test_valplanqual (c1 int, c2 int) distributed by(c1);
CREATE
session 0: insert into test_valplanqual values(1,1);
INSERT 1
session 0: select * from test_valplanqual;
c1 | c2
----+----
1 | 1
(1 row)
session 1: begin;
BEGIN
session 2: begin;
BEGIN
session 1: update test_valplanqual set c1 = c1 + 1 where c1 = 1;
UPDATE 1
session 2: update test_valplanqual set c1 = c1 + 1 where c1 = 1; <waiting ...>
session 1: end;
END
session 2<: <... completed>
ERROR: EvalPlanQual can not handle subPlan with Motion node (seg1 127.0.1.1:7003 pid=34629)
session 2: end;
END
session 0: select * from test_valplanqual;
c1 | c2
----+----
2 | 1
(1 row)
session 0: drop table test_valplanqual;
DROP解决方案
避免更新分布键。
2 ERROR: Too many unresolved insertion xids, please do a vacuum
问题分析
当前 mars2 存储引擎控制一个 BLOCK 上存在的 INSERT XID 数量，算法如下:
#define INSERTXIDSPERBLOCK ((BLCKSZ - PAGE_RESERVED_SPACE) / sizeof(TransactionId))
#define PAGE_RESERVED_SPACE(MAXALIGN(sizeof(PageHeaderData)) + MAXALIGN(sizeof(SortHeapPageOpaqueData)))大概约 (block_size - 24 + 24) / 4 个子事务，默认 block_size 为 8K 时，约为 2036，当子事务数超过该值时，会出现该错。
复现步骤
session 0: create table test_xids(id int) using mars2;
CREATE TABLE
session 0: create index ON test_xids using mars2_btree (id);
CREATE INDEX
session 0:
DO $$
DECLARE
i int;
_start timestamptz;
BEGIN
FOR i IN 1..3000 LOOP
_start = clock_timestamp();
INSERT INTO test_xids VALUES (i);
RAISE NOTICE 'value: % escape: % ', i, clock_timestamp() - _start;
END LOOP;
END;
$$language plpgsql;
psql: NOTICE: value: 1 escape: 00:00:00.019476
psql: NOTICE: value: 2 escape: 00:00:00.002501
psql: NOTICE: value: 3 escape: 00:00:00.00218
psql: NOTICE: value: 4 escape: 00:00:00.002098
psql: NOTICE: value: 5 escape: 00:00:00.002938
psql: NOTICE: value: 6 escape: 00:00:00.001891
psql: NOTICE: value: 7 escape: 00:00:00.001794
...
...
psql: NOTICE: value: 2069 escape: 00:00:10.001794
psql: NOTICE: value: 2070 escape: 00:00:10.001495
psql: NOTICE: value: 2071 escape: 00:00:10.001251
psql: NOTICE: ERROR: Too many unresolved insertion xids, please do a vacuum (sortheap_external_sort.c:404) (seg0 172.16.100.197:6000 pid=12992) (sortheap_external_sort.c:404)
CONTEXT: SQL statement "insert into test_xids values(i)"
PL/pgSQL function inline_code_block line 7 at SQL statement解决方案
避免在一个事务中多次执行 INSERT INTO tablename VALUES(...) 操作，最友好的做法是，每一个事务只执行一次 INSERT 操作，并且使用 INSERT INTO tablename VALUES(...),(...),(...) 的形式批量插入据。 对于 MARS2 表，官方更加推荐使用 mxgate 工具 更高效的写入数据。
3 ERROR: modification of distribution columns in OnConflictUpdate is not supported
问题分析
当参数 gp_enable_global_deadlock_detector 设置为 on 后，锁的模式可能下降为 RowExclusiveLock，当执行更新分布键时，INSERT...ON CONFLICT DO UPDATE SET... 语句在执行 UPDATE 操作会被拆分为 DELETE + INSERT, 此时更新分布键，抛出这类错误。
复现步骤
session 0: create table test_upsert(id int, name text, primary key(id)) distributed by(id);
CREATE TABLE
session 0: insert into test_upsert select 1, 'a';
INSERT 0 1
session 0: insert into test_upsert select 1, 'a' on conflict (id) do update set id =2;
psql: ERROR: modification of distribution columns in OnConflictUpdate is not supported
session 0: insert into test_upsert select 1, 'a' on conflict (id) do update set name = 2;
INSERT 0 1
session 0: drop table test_upsert;
DROP解决方案
执行 UPSERT 时，避免更新分布键。
4 UI 客户端收到 log：server closed the connection unexpectedly
UI 客户端访问远程数据库，长查询过程中或长时间 idle 后发查询，客户端有些时候会收到 log:
server closed the connection unexpectedly问题分析
客户端存在查询超时 cancel 设置，或 idle 超时 cancel 连接设置。
解决方案
更改客户端超时设置，取消超时。
5 PARTITION 表简单 Filter 操作的 UNION ALL 查询比 IN 查询慢
问题分析
PARTITION 表的 IN 查询，分区裁剪后只有 1 个 DEFAULT 分区，但 UNION ALL 查询中每个子查询都裁剪到了 DEFAULT 分区，做了多次 DEFAULT 分区的扫描，性能影响明显。
解决方案
对于 PARTITION 表： 尽量避免 DEFAULT 分区、尽量不用 UNION 而用 IN 子句。
6 插入 int 类型数据，单独 SELECT 查询跑很快，放到 plpgsql function 里很慢
问题分析
plpgsql function 内的查询是通过 SPI 运行，SPI Plan 输出结果里是两表 Join，采用了 nestloop，语句 rows = 1，没有 ANALYSE。
解决方案
执行 ANALYZE。
7 PARTITION 分区裁剪更新操作，两个会话（Session）独立更新会导致互锁
问题分析
分布式死锁。
解决方案
打开分布式死锁检测。
gpconfig -c gp_enable_global_deadlock_detector -v on8 自定义 type 使用及单表字段扩展
自定义type使用
- 创建type
CREATE TYPE public.fhpm AS ( avgval double precision, minval double precision, maxval double precision, minval_interval integer, maxval_interval integer ); - 创建表
CREATE TABLE datapool.test ( portindex integer, begintime timestamp without time zone, a_1 fhpm, s_2 integer ); - 插入数据样例
insert into datapool.test values(1,'2022-01-01','(1,1,1,1,1)',1); insert into datapool.test values(2,'2022-01-01','(2,,2,,)',2); - type 类型属性数据查询样例
SELECT * FROM datapool.test; portindex | begintime | a_1 | s_2 -----------+---------------------+-------------+----- 1 | 2022-01-01 00:00:00 | (1,1,1,1,1) | 1 2 | 2022-01-01 00:00:00 | (2,,2,,) | 2 (2 rows)SELECT (a_1).avgval FROM datapool.test; avgval -------- 1 2 (2 rows)SELECT (a_1).minval FROM datapool.test; minval -------- 1 (2 rows)单表字段扩展
psql: ERROR: tables can have at most 1600 columns
使用自定义 type 可以实现单表突破1600字段的限制。有兴趣的可以自己尝试一下,以下是实现样例。
CREATE TYPE public.fhpm1 AS
(
a0001 double precision,
a0002 double precision,
...
a1600 double precision);
CREATE TABLE datapool.test1
(
portindex integer,
begintime timestamp without time zone,
a_1 fhpm1,
s_2 integer,
a_3 fhpm2
);生成1600列 type 脚本
echo 'CREATE TYPE public.fhpm1 AS(' > test.sql
for i in {1..1599};do
echo 'a'$i' double precision,' >> test.sql
done
echo 'a1600 double precision);' >> test.sql9 ERROR,XX000,Right sibling's left-link doesn't match : block 817 links to 45366 instead of expected 70930 in index ""tab_idx""
问题分析
该报错为索引损坏。在表频繁的更新删除，索引膨胀严重的时候也可能出现该报错。出现该报错意味着该索引已无法正常使用。
解决方案
重建索引。
drop index tab_idx；
create index tab_idx on table &tablename (column1,column2..);10 too many clients already
问题分析
会话连接过多，超过了数据库限制的最大连接数。
解决方案
- 情形一：非超管用户连接导致
超管用户在数据库里预留的有连接数用来处理异常情况，可以通过命令show superuser_reserved_connections;来查看，默认预留的是 10 个连接。如果是非超管用户导致的，可以通过超管用户在库里杀掉 idle 会话，或者临时限制部分低优先级作业的用户登录权限。 - 情形二：超管用户导致
超管用户导致的该报错，数据库已经没用额外的预留连接让超管用户登录，如果没用影响业务，作业还在正常运行，则等待作业消耗即可。
如果已经严重影响了作业运行，则需要重启数据库。通过正常的gpstop命令已经无法重启，会报错too many clients already。重启方式如下： 1）使用pg_ctl重启 Master 节点 2）通过gpstart -m命令启动 Master 节点 3）使用gpstop命令停止集群，检查所有实例残余进程 4）使用gpstart命令启动集群
11 更新删除时报错 cannot delete from table "&tablename" because it does not have a replica identity and publishes deletes
问题分析
报错的表被开启了逻辑复制。开启逻辑复制的表如果没有设置主键，只能执行insert操作，update和delete都会报以上错误。该复制逻辑出现在早期的postgresql中，但后续版本该功能一直存在，在 MatrixDB 中建议不要开启该功能。
问题复现
test6=# create table test as select * from pg_tables distributed randomly;
SELECT 94
test6=# create publication rep_source for table test;
CREATE PUBLICATION
test6=# insert into test select * from test;
INSERT 0 94
test6=# update test set tablename='pg_class_old' where tablename='pg_class';
psql: ERROR: cannot update table "test" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
test6=# delete from test where tablename='pg_class';
psql: ERROR: cannot delete from table "test" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.解决方案
设置更新和删除时包含所有列的前映像值。
alter table &schema.tablename REPLICA IDENTITY FULL;或者删除对应的复制发布任务。
select a.*,b.prrelid::regclass tablename from pg_publication a,pg_publication_rel b where a.oid=b.prpubid;
drop publication &任务名;12 ERROR: could not read block 0 in file "base/1588803/269422"
问题分析
文件块损坏，导致对应的文件无法读取
问题复现
test6=# create table test as select * from pg_tables distributed randomly;
SELECT 94
test6=# create index idx_tabname on test1(tablename);
CREATE INDEX
test6=# select relname,relfilenode from pg_class where relname in('test1','idx_tabname');
relname | relfilenode
-------------+-------------
idx_tabname | 269422
test1 | 269421
(2 rows)
根据relfilenode找到对应的文件，任意破坏一个(此处破坏的是索引文件)
test6=# select * from test1 where tablename='a';
psql: ERROR: could not read block 0 in file "base/1588803/269422": read only 8123 of 8192 bytes
解决方案
根据报错的 relfilenode 确认损坏的是表文件还是索引文件：
情形一：索引文件损坏。直接重建即可，使用 reindex 命令。
情形二：有 Mirror，数据表文件损坏，将相同 content 的 Mirror 文件拷贝替换 Primary 文件即可。
情形三：无 Mirror，数据表文件损坏，单独将正常实例的数据拷贝出来，重建表，异常实例的数据会丢失。
13 pg_hba.conf 文件配置错误导致集群无法启动
问题分析
pg_hba.conf 是限制远程访问数据库权限配置文件，该文件配置如果不符合格式要求，会导致集群无法启动。
问题复现
修改 pg_hba.conf 文件，在末尾添加一行信息。
[mxadmin@mxd2 ~]$ cd $MASTER_DATA_DIRECTORY
[mxadmin@mxd2 mxseg-1]$ vi pg_hba.conf
host all all 0.0.0.0 md5 ##新加行，不符合标准格式要求启动数据库报错。
[mxadmin@mxd2 mxseg-1]$ gpstart -a
...
20221230:14:08:56:014273 gpstart:mxd2:mxadmin-[CRITICAL]:-Failed to start Master instance in admin mode
20221230:14:08:56:014273 gpstart:mxd2:mxadmin-[CRITICAL]:-Error occurred: non-zero rc: 1
Command was: 'env GPSESSID=0000000000 GPERA=None $GPHOME/bin/pg_ctl -D /mxdata_20220408112227/master/mxseg-1 -l /mxdata_20220408112227/master/mxseg-1/log/startup.log -w -t 600 -o " -p 5432 -c gp_role=utility " start'
rc=1, stdout='waiting for server to start.... stopped waiting
', stderr='pg_ctl: could not start server
Examine the log output.
'检查数据库日志。
2022-12-29 22:08:56.932011 PST,,,p14282,th804919424,,,,0,,,seg-1,,,,,"LOG","F0000","invalid IP mask ""md5"": Name or service not known",,,,,"line 104 of configuration file ""/mxdata_20220408112227/master/mxseg-1/pg_hba.conf""",,0,,"hba.c",1254,
2022-12-29 22:08:56.932029 PST,,,p14282,th804919424,,,,0,,,seg-1,,,,,"FATAL","F0000","could not load pg_hba.conf",,,,,,,0,,"postmaster.c",1542,
2022-12-29 22:08:56.932033 PST,,,p14282,th804919424,,,,0,,,seg-1,,,,,"LOG","00000","database system is shut down",,,,,,,0,,"miscinit.c",905,解决方案
修正 pg_hba.conf 文件配置：
情形一：如果异常行是非法加入，则删除对应的异常行。正常启动集群即可。
情形二：异常行是加入的时候格式异常，需要确认调整访问权限，修正异常行。然后正常启动集群。
14 pg_hba.conf 文件内容配置的顺序导致的远程访问权限异常
问题分析
pg_hba.conf 是限制远程访问数据库权限配置文件，访问权限配置的顺序读取是从上往上，配置的顺序不对，会导致权限管控失败。
问题复现
创建测试用户。
test6=# create role test with login password 'test';
psql: NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE登录测试。
[mxadmin@mxd2 mxseg-1]$ psql test6 -U test -h 192.168.8.12 -p 5432
Password for user test:
psql (12)
Type "help" for help.
test6=>通过修改 pg_hba.conf 限制 test 用户远程登录。限制配置在文件末尾。
[mxadmin@mxd2 mxseg-1]$ vi pg_hba.conf
#user access rules
host all all 0.0.0.0/0 md5
host all test 0.0.0.0/0 reject //添加权限限制
[mxadmin@mxd2 mxseg-1]$ mxstop -u
//测试登录，登录正常，未成功限制远程
[mxadmin@mxd2 mxseg-1]$ psql test6 -U test -h 192.168.8.12 -p 5432
Password for user test:
psql (12)
Type "help" for help.
test6=> 解决方案
检查当前用户在 pg_hba.conf 里面的限制，将权限限制行添加在合适的位置。
//调整后的 `pg_hab.conf` 文件
[mxadmin@mxd2 mxseg-1]$ vi pg_hba.conf
#user access rules
host all test 0.0.0.0/0 reject //添加权限限制
host all all 0.0.0.0/0 md5[mxadmin@mxd2 mxseg-1]$ mxstop -u
//测试登录，登录失败，成功限制远程
[mxadmin@mxd2 mxseg-1]$ psql test6 -U test -h 192.168.8.12 -p 5432
psql: error: could not connect to server: FATAL: pg_hba.conf rejects connection for host "192.168.8.12", user "test", database "test6", SSL off15 idle in transaction timeout 报错
问题分析
idle in transaction 连接超时，该超时是由参数控制，设置时需谨慎。
问题复现
检查当前参数设置。
test=# show idle_in_transaction_session_timeout ;
idle_in_transaction_session_timeout
-------------------------------------
100s
(1 row)为方便演示，在会话级别设置该参数为 10s。
test=# set idle_in_transaction_session_timeout ='10s';
SET
test=# show idle_in_transaction_session_timeout ;
idle_in_transaction_session_timeout
-------------------------------------
10s
(1 row)开启事务，测试该参数的影响。
test=# begin ;
BEGIN
test=# select count(*) from pg_tables;
count
-------
104
(1 row)等超过 10s 再执行第二次。
test=# select count(*) from pg_tables;
psql: FATAL: terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.检查数据库日志。
2023-02-22 10:47:07.577478 PST,"mxadmin","test",p43086,th-484071296,"[local]",,2023-02-22 10:44:30 PST,0,con25838,cmd8,seg-1,,dx25863,,sx1,"FATAL","25P03","terminating connection due to idle-in-transaction timeout",,,,,,,0,,"postgres.c",4018,解决方案
- 谨慎设置该参数，可能会导致长事务回滚。
- 遇到这种问题，可以修改系统参数或者设置会话级别参数来控制。
系统级别参数修改。
$ gpconfig -c idle_in_transaction_session_timeout -v 10s
$ gpstop -u // 使配置生效会话级别设置参数。
$ set idle_in_transaction_session_timeout='10s';16 insufficient memory reserved for statement 报错
问题分析
会话使用内存超过 statement_mem 设置内存导致。客户数据量不大，但是分区特别多。过多的分区在 count(*) 的时候会导致内存占用较大。
问题复现
创建一个分区表，分区数量尽可能的多。
test=# create table test (id int,read numeric,write numeric,dttime timestamp)
test-# Distributed by (id)
test-# Partition by range(dttime)
test-# (start ('2022-11-01'::date)
test(# end ('2022-12-01'::date)
test(# every ('1 hours'::interval));
CREATE TABLE在表中插入少量数据。
test=# insert into test
test-# select j as id,random()*10 as read,random()*10 as write,i as dttime from generate_series('2022-11-01', '2022-11-30',interval '1 hours') as i,generate_series(1,100) j;
INSERT 0 69800查询表的数据量报错。
test=# select count(*) from test;
psql: ERROR: insufficient memory reserved for statement解决方案
- 临时解决方案
- 在会话级别修改
statement_mem参数set statement_mem ='1000MB'
- 在会话级别修改
- 永久解决方案
- 调整
statement_mem系统值gpconfig -c statement_mem -v 10000MB，调整的时候需要注意max_statement_mem的大小。 - 如果不想调整系统参数，则需要重新规划分区，当前分区与整表数据量对比是不合理的。
- 调整
17 evalplanqual can not hanlde subplan with motion node 报错
问题分析
不同的事务对相同的分布键数据进行更新操作，最开始的事务提交后，其他事务报该错。
问题复现
创建一张表。
test=# create table test1 as
test-# select i id,md5(random()::text) tag_id1
test-# from generate_series(1,10) i;
psql: NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT 10会话 A 开启事务更新 id=1 的记录。
test=# begin;
BEGIN
test=# update test1 set id=11 where id=1;
UPDATE 1会话 B 开启事务更新 id=1 的记录。
test=# begin;
BEGIN
test=# update test1 set id=11 where id=1;提交会话 A 的事务。
test=# begin;
BEGIN
test=# update test1 set id=11 where id=1;
UPDATE 1
test=#
test=# commit;
COMMIT会话 B 报错。
test=# begin;
BEGIN
test=# update test1 set id=11 where id=1;
psql: ERROR: EvalPlanQual can not handle subPlan with Motion node (seg1 192.168.8.12:6001 pid=49302)解决方案
- 避免多个事务同时对相同的分布键自动进行更新。
- 更新数据时尽量使用数据库的自动事务功能，无需手动开启事务。
- 尽量避免在程序中使用长事务。
18 在客户端创建表在 Linux 上查询不到，但是客户端上可以查询到
报错信息
SELECT * FROM ttemp;
psql: ERROR: relation "ttemp" does not exist
LINE 1: SELECT * FROM ttemp;
^问题分析
查看报错。
psql: ERROR: relation "ttemp" does not exist
LINE 1: SELECT * FROM ttemp;
^- 执行
\dn查看 schema 列表，show search_path;查看当前默认的 Schema ，确认一下这个表创建在哪个 Schema 下面，执行查询加上schema.table。
postgres=# \dn
List of schemas
Name | Owner
------------+---------
gp_toolkit | mxadmin
public | mxadmin
(2 rows)
postgres=# SHOW search_path ;
search_path
-----------------
"$user", public
(1 row)发现默认的 Schema 就是 public。
- 执行
\l查看一下数据库列表。
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------+----------+------------+------------+---------------------
matrixmgr | mxadmin | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | mxadmin | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | mxadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/mxadmin +
| | | | | mxadmin=CTc/mxadmin
template1 | mxadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/mxadmin +
| | | | | mxadmin=CTc/mxadmin
(4 rows)- 检查一下客户端和程序的连接信息是否正确。
发现程序连接的是 5432 端口的数据库集群，Linux 上面连接的是 5433 端口的。
postgres=# SHOW port;
port
------
5433
(1 row)解决方案
关闭端口为 5433 的集群。
[mxadmin@mdw ~]$ gpstop -a修改环境变量的端口和 MASTER_DATA_DIRECTORY 信息。
$ vim ~mxadmin/.matrixdb.env
export PGPORT=5432
export MASTER_DATA_DIRECTORY=/mxdata_20220909145815/master/mxseg-1加载新的环境变量。
$ source ~mxadmin/.matrixdb.env19 UUID 分布键插入数据出现数据丢失、数据重复问题
问题描述
执行 INSERT INTO table_uuid SELECT ... FROM 操作，结果会有插入数据条数出现波动的情况。同时 SELECT 结果不出现波动，稳定输出。
给出以下示例：
多次执行 INSERT 操作，发现执行的结果会出现波动，同时 SELECT 结果是稳定的 1 条输出。
INSERT 0 0
Time: 25.774 msINSERT 0 1
Time: 40.934 msINSERT 0 2
Time: 34.338 msINSERT 0 3
Time: 32.562 ms问题分析
此问题的产生原因是下面三个条件的组合：
SET OPTIMIZER TO ON。- 使用
random()这类的随机函数，生成 UUID。 - 插入目标表的分布键为 UUID。
解决方案
更改上面三个条件中的其中一项，即可避免此类问题。
20 数据库对应的操作系统版本从 CentOS7 升级到 CentOS8，查询语句性能下降
问题分析
Redhat8 新增 sssd 服务，会对所有连接访问进行认证，导致连接创建认证效率变低，尤其是 slice 数量多的场景，查询语句效率下降明显。
问题复现
- 在 CentOS7 操作系统上搭建一套集群
- 在 CentOS8 操作系统上搭建一套集群
- 创建多张有关联关系的表
- 进行多表关联查询
- 对比两个系统的数据库的查询效率
解决方案
- 将小表改成复制表，减少 slice
- 关闭对应的操作系统服务
systemctl stop sssd systemctl stop sssd-kcm.socket
21 执行 SQL 语句时显示 insufficient memory reserved for statement 错误
问题分析
在执行 SQL 时查询计划评估的内存超过了 statement_mem 参数的限制。
解决方案
-
调大
statement_mem参数值，避免查询时评估的内存超过限制。 -
查看查询的表是否为分区表，如果是则对分区表进行合并。