YMatrix SQL 查询常见问题

本文档介绍 YMatrix 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 图形化客户端收到 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 类型数据,单独查询跑很快,但放到 Plpgsql Function 里很慢


问题分析

Plpgsql Function 内的查询是通过 SPI 运行,SPI Plan 输出结果里是两表 Join,采用了 nestloop,语句 rows = 1,没有 ANALYSE。

解决方案

执行 ANALYZE。


7 PARTITION 分区裁剪更新操作,两个会话(Session)独立更新会导致互锁


问题分析

分布式死锁。

解决方案

打开分布式死锁检测。

gpconfig -c gp_enable_global_deadlock_detector -v on


8 自定义 type 使用及单表字段扩展


自定义type使用

  1. 创建type
    CREATE TYPE public.fhpm AS
    (
    avgval double precision,
    minval double precision,
    maxval double precision,
    minval_interval integer,
    maxval_interval integer
    );
  2. 创建表
    CREATE TABLE datapool.test                
    (                                         
     portindex integer,                    
     begintime timestamp without time zone,
     a_1 fhpm,                             
     s_2 integer                           
    );               
  3. 插入数据样例
    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);   
  4. 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.sql


9 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 更新删除时报错 cannot delete from table "&tablename" because it does not have a replica identity and publishes deletes


问题分析

报错的表被开启了逻辑复制。开启逻辑复制的表如果没有设置主键,只能执行insert操作,update和delete都会报以上错误。该复制逻辑出现在早期的postgresql中,但后续版本该功能一直存在,在 YMatrix 中建议不要开启该功能。

问题复现

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 &任务名;


11 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,数据表文件损坏,单独将正常实例的数据拷贝出来,重建表,异常实例的数据会丢失。


12 行转列示例


在使用 YMatrix 的时候会遇到行转列的 SQL 需求,下面针对这个问题进行演示。具体步骤如下:

行转列示例

  1. 创建测试表
    create table test (name varchar(20),zbfm varchar(20),value integer);
  2. 插入测试数据
    insert into test values('张三','年龄',60);
    insert into test values('张三','身高',95);
    insert into test values('张三','体重',31);
    insert into test values('张三','鞋码',42);
    insert into test values('李四','年龄',50);
    insert into test values('李四','身高',83);
    insert into test values('李四','体重',84);
    insert into test values('李四','鞋码',43);
    insert into test values('王五','年龄',97);
    insert into test values('王五','身高',75);
    insert into test values('王五','体重',66);
    insert into test values('王五','鞋码',44);
  3. 查询测试数据
    select * from test;
    name | zbfm | value 
    ------+------+-------
    张三 | 年龄 |    60
    张三 | 身高 |    95
    张三 | 体重 |    31
    张三 | 鞋码 |    42
    王五 | 年龄 |    97
    王五 | 身高 |    75
    王五 | 体重 |    66
    王五 | 鞋码 |    44
    李四 | 年龄 |    50
    李四 | 身高 |    83
    李四 | 体重 |    84
    李四 | 鞋码 |    43
    (12 rows)
  4. 将数据进行行转列展示
    select name,
    max(case when zbfm='年龄' then value else 0 end) as 年龄,
    max(case when zbfm='身高' then value else 0 end) as 身高,
    max(case when zbfm='体重' then value else 0 end) as 体重,
    max(case when zbfm='鞋码' then value else 0 end) as 鞋码
    from test 
    group by name
    order by 年龄 desc;
    name | 年龄 | 身高 | 体重 | 鞋码 
    ------+------+------+------+------
    王五 |   97 |   75 |   66 |   44
    张三 |   60 |   95 |   31 |   42
    李四 |   50 |   83 |   84 |   43
    (3 rows)


13 列转行示例


在使用 YMatrix 的时候会遇到列转行的 SQL 需求,下面针对这个问题进行演示。具体步骤如下:

列转行示例

  1. 创建测试表
drop table if exists t_unpivot;
create table t_unpivot(
    currenttimestamp bigint  ,
    deviceid text  ,
    devicetemplatecode varchar  ,
    statisticstype text ,
    co2 float ,
    currentpowersum float ,
    currenttemperature float ,
    curtemp float ,
    duration float ,
    dust_level float 
);
  1. 插入测试数据
    insert into t_unpivot values(1646376466611,'1.2.156.156.11.20.227161606742980522','138700003','statistics_avg',12.4,23.4,null,null,null,55.5);
    insert into t_unpivot values(1646376466612,'1.2.156.156.11.20.607141619709364801','122400004','statistics_avg',null,34.3,56.3,null,null,null);
    insert into t_unpivot values(1646376466613,'1.2.156.156.11.20.929741642180181067','178800001','statistics_avg',null,null,null,43.2,null,null);
    insert into t_unpivot values(1646376466614,'1.2.156.156.11.20.327231588865913990','123200004','statistics_avg',null,null,null,null,23.2,null);
    insert into t_unpivot values(1646376466615,'1.2.156.156.11.20.155831629756361011','154900008','statistics_avg',null,null,21.2,null,null,null);
  2. 查看测试数据
    select * from t_unpivot;
    currenttimestamp |               deviceid               | devicetemplatecode | statisticstype | co2  | currentpowersum | currenttemperature | curtemp | duration | dust_level 
    ------------------+--------------------------------------+--------------------+----------------+------+-----------------+--------------------+---------+----------+------------
     1646376466612 | 1.2.156.156.11.20.607141619709364801 | 122400004          | statistics_avg |      |            34.3 |               56.3 |         |          |           
     1646376466611 | 1.2.156.156.11.20.227161606742980522 | 138700003          | statistics_avg | 12.4 |            23.4 |                    |         |          |       55.5
     1646376466613 | 1.2.156.156.11.20.929741642180181067 | 178800001          | statistics_avg |      |                 |                    |    43.2 |          |           
     1646376466615 | 1.2.156.156.11.20.155831629756361011 | 154900008          | statistics_avg |      |                 |               21.2 |         |          |           
     1646376466614 | 1.2.156.156.11.20.327231588865913990 | 123200004          | statistics_avg |      |                 |                    |         |     23.2 |           
    (5 rows)
  3. 将数据进行列转行展示
    select currenttimestamp, 
        deviceid, 
        devicetemplatecode,
        statisticstype,
        (b.rec).key as key, 
        (b.rec).value as value 
    from
    (select currenttimestamp, 
         deviceid, 
         devicetemplatecode,
         statisticstype,
         jsonb_each_text(row_to_json(t.*)::jsonb-'currenttimestamp'-'deviceid'-'devicetemplatecode'-'statisticstype') as rec  
    from t_unpivot t
    ) b
    where (b.rec).value is not null;
    currenttimestamp |               deviceid               | devicetemplatecode | statisticstype |        key         | value 
    ------------------+--------------------------------------+--------------------+----------------+--------------------+-------
     1646376466612 | 1.2.156.156.11.20.607141619709364801 | 122400004          | statistics_avg | currentpowersum    | 34.3
     1646376466612 | 1.2.156.156.11.20.607141619709364801 | 122400004          | statistics_avg | currenttemperature | 56.3
     1646376466611 | 1.2.156.156.11.20.227161606742980522 | 138700003          | statistics_avg | co2                | 12.4
     1646376466611 | 1.2.156.156.11.20.227161606742980522 | 138700003          | statistics_avg | dust_level         | 55.5
     1646376466611 | 1.2.156.156.11.20.227161606742980522 | 138700003          | statistics_avg | currentpowersum    | 23.4
     1646376466613 | 1.2.156.156.11.20.929741642180181067 | 178800001          | statistics_avg | curtemp            | 43.2
     1646376466615 | 1.2.156.156.11.20.155831629756361011 | 154900008          | statistics_avg | currenttemperature | 21.2
     1646376466614 | 1.2.156.156.11.20.327231588865913990 | 123200004          | statistics_avg | duration           | 23.2
    (8 rows)


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 off


15 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,

解决方案

  1. 谨慎设置该参数,可能会导致长事务回滚。
  2. 遇到这种问题,可以修改系统参数或者设置会话级别参数来控制。

系统级别参数修改。

$ 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

解决方案

  1. 临时解决方案
    • 在会话级别修改 statement_mem 参数 set statement_mem ='1000MB'
  2. 永久解决方案
    • 调整 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)

解决方案

  1. 避免多个事务同时对相同的分布键自动进行更新。
  2. 更新数据时尽量使用数据库的自动事务功能,无需手动开启事务。
  3. 尽量避免在程序中使用长事务。


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;
                      ^
  1. 执行 \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

  1. 执行 \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)
  1. 检查一下客户端和程序的连接信息是否正确。

发现程序连接的是 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.env


19 UUID 分布键插入数据出现数据丢失、数据重复问题


问题描述

执行 INSERT INTO table_uuid SELECT ... FROM 操作,结果会有插入数据条数出现波动的情况。同时 SELECT 结果不出现波动,稳定输出。

给出以下示例:
多次执行 INSERT 操作,发现执行的结果会出现波动,同时 SELECT 结果是稳定的 1 条输出。

INSERT 0 0
Time: 25.774 ms
INSERT 0 1
Time: 40.934 ms
INSERT 0 2
Time: 34.338 ms
INSERT 0 3
Time: 32.562 ms

问题分析

此问题的产生原因是下面三个条件的组合:

  1. SET OPTIMIZER TO ON
  2. 使用 random() 这类的随机函数,生成 UUID。
  3. 插入目标表的分布键为 UUID。

解决方案

更改上面三个条件中的其中一项,即可避免此类问题。


20 数据库对应的操作系统版本从 CentOS7 升级到 CentOS8,查询语句性能下降


问题分析

Redhat8 新增 sssd 服务,会对所有连接访问进行认证,导致连接创建认证效率变低,尤其是 slice 数量多的场景,查询语句效率下降明显。

问题复现

  1. 在 CentOS7 操作系统上搭建一套集群
  2. 在 CentOS8 操作系统上搭建一套集群
  3. 创建多张有关联关系的表
  4. 进行多表关联查询
  5. 对比两个系统的数据库的查询效率

解决方案

  1. 将小表改成复制表,减少 slice
  2. 关闭对应的操作系统服务
    systemctl stop sssd
    systemctl stop sssd-kcm.socket


21 执行 SQL 语句时显示 insufficient memory reserved for statement 错误


问题分析

在执行 SQL 时查询计划评估的内存超过了 statement_mem 参数的限制。

解决方案

  1. 调大 statement_mem 参数值,避免查询时评估的内存超过限制。

  2. 查看查询的表是否为分区表,如果是则对分区表进行合并。


22 创建 matrixts 扩展时报错 ERROR: type "mxkv_text" already exist


问题复现

创建源库 db1、目标库 db2,并在 db1 上创建 matrixts 扩展及若干表。

CREATE DATABASE db1;
CREATE DATABASE db2;
\c db1
CREATE EXTENSION matrixts;
CREATE TABLE t1(c INT);
CREATE TABLE t2(c INT);

使用 Navicat Premium 客户端软件中的工具 → 结构同步,将数据库 db1 的相关表、视图、函数等同步至数据库 db2 中。

此时在数据库 db2 执行 CREATE EXTENSION matrixts ,会出现类似报错:

ERROR:  type "<类型名称>" already exists
ERROR:  function "<函数名称>" already exists with same argument types

例如:

db2=# CREATE EXTENSION matrixts;
psql: NOTICE:  Releasing segworker groups to finish aborting the transaction.
psql: ERROR:  type "mxkv_text" already exists
ERROR:  could not open gp_segment_configutation dump file:gpsegconfig_dump:No such file or directory (cdbutil.c:151)
db2=# CREATE EXTENSION matrixts;
psql: ERROR:  function "set_policy" already exists with same argument types
ERROR:  could not open gp_segment_configutation dump file:gpsegconfig_dump:No such file or directory (cdbutil.c:151)

问题分析

该种问题是由于在通过第三方客户端同步 DDL 的时候,将 matrixts 扩展所需的函数、类型等定义同步了过来。

当用户通过 CREATE EXTENSION matrixts 创建 matrixts 插件时,由于存在同名函数、类型等定义,所以会导致 matrixts 插件无法初始化。

解决方案

  1. 如果你已因操作不当收到报错,请删除冲突的同名函数及类型。
   DROP TYPE mxkv_float4 cascade;
   DROP TYPE mxkv_float8 cascade;
   DROP TYPE mxkv_int4 cascade;
   DROP TYPE mxkv_text cascade;
   DROP FUNCTION set_policy;
   DROP FUNCTION drop_policy(rel regclass);
   DROP FUNCTION drop_policy(rel regclass, policy_name text);
   DROP FUNCTION set_policy_action(rel regclass, action_name text, args text);
   DROP FUNCTION set_policy_action(rel regclass, action_name text, in_disabled boolean);
   DROP FUNCTION disable_policy_action(rel regclass, action_name text);
   DROP FUNCTION enable_policy_action(rel regclass, action_name text);
  1. 如果你还未在客户端进行同步操作,则可创建新的数据库,在该数据库下创建 matrixts 插件后,再进行表、视图等定义的同步。