YMatrix SQL Query FAQ

This document describes common problems with YMatrix SQL queries.

For details on data query, please see:

1 `ERROR: EvalPlanQual can not handle subPlan with Motion node`

Problem Analysis

When the parameter gp_enable_global_deadlock_detector is set to on, the lock mode may drop to RowExclusiveLock. When the update distribution key is performed, the UPDATE operation will be split into DELETE + INSERT. At this time, the concurrent update distribution key is updated. The DELETE operation cannot perform EvalPlanQual, and the INSERT operation will not blocked, which may generate unnecessary data. In order to prevent this phenomenon, such errors are thrown.

Reproduction steps

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

Solution

Avoid updating the distribution keys.

2 `ERROR: Too many unresolved insertion xids, please do a vacuum`

Problem Analysis

The current mars2 storage engine controls the number of INSERT XIDs that exist on a BLOCK, and the algorithm is as follows:

#define INSERTXIDSPERBLOCK ((BLCKSZ - PAGE_RESERVED_SPACE) / sizeof(TransactionId))
#define PAGE_RESERVED_SPACE(MAXALIGN(sizeof(PageHeaderData)) + MAXALIGN(sizeof(SortHeapPageOpaqueData)))

Approximately (block_size - 24 + 24) / 4 sub-transactions. When the default block_size is 8K, it is about 2036. This error will occur when the number of sub-transactions exceeds this value.

Reproduction steps

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

Solution

To avoid multiple executions of INSERT INTO tablename VALUES(...) operations in a transaction, the most friendly approach is to perform INSERT operations only once per transaction and insert data in batches using the form of INSERT INTO tablename VALUES(...),(...),(...). For the mars2 table, the official recommendations using mxgate tool to write data more efficiently.

3 `ERROR: modification of distribution columns in OnConflictUpdate is not supported`

Problem Analysis

When the parameter gp_enable_global_deadlock_detector is set to on, the lock mode may drop to RowExclusiveLock. When performing an update distribution key, the INSERT...ON CONFLICT DO UPDATE SET... statement will be split into DELETE + INSERT when executing the UPDATE operation. At this time, the distribution key is updated, and this type of error is thrown.

Reproduction steps

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

Solution

Avoid updating the distribution keys when performing UPSERT.

4 Graphical client received log: `server closed the connection unexpectedly`

Graphical (UI) client accesses remote databases, sends queries during long querying or after long idles, and sometimes the client will receive logs:

server closed the connection unexpectedly

Problem Analysis

The client has query timeout cancel setting, or idle timeout cancel connection setting.

Solution

Change the client timeout setting and cancel the timeout.

5 The UNION ALL query for simple Filter operations is slower than the IN query

Problem Analysis

For the IN query of the PARTITION table, there is only 1 DEFAULT partition after the partition is cropped, but each subquery in the UNION ALL query is cropped to the DEFAULT partition. After multiple scans of the DEFAULT partition, the performance impact is obvious.

Solution

For the PARTITION table: Try to avoid DEFAULT partitioning, try not to use UNION but use the IN clause.

6 Insert int type data, the query runs very fast, but it is slow to put it in Plpgsql Function

Problem Analysis

The query in Plpgsql Function is run through SPI. The output result of SPI Plan is two tables Join, using nestloop, statement rows = 1, and there is no ANALYSE.

Solution

Perform ANALYZE.

7 PARTITION partition cropping and update operation, two sessions (Session) independently update will cause interlocking

Problem Analysis

Distributed deadlock.

Solution

Turn on distributed deadlock detection.

gpconfig -c gp_enable_global_deadlock_detector -v on

8 Custom type usage and single table field extension

Custom type usage

Create type

CREATE TYPE public.fhpm AS
(
avgval double precision,
minval double precision,
maxval double precision,
minval_interval integer,
maxval_interval integer
);

Create a table

CREATE TABLE datapool.test                
(                                         
 portindex integer,                    
 begintime timestamp without time zone,
 a_1 fhpm,                             
 s_2 integer                           
);

Insert data sample

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);

Sample type attribute data query example

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)

Single table field extension psql: ERROR: tables can have at most 1600 columns

Use a custom type to achieve a single table that breaks the limit of 1600 fields. If you are interested, you can try it yourself. The following are implementation examples.

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
);

Generate 1600 column type script

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""

Problem Analysis

The error is index corrupt. This error may also occur when the table is updated frequently and the index is severely bloated. This error occurs that means the index is no longer used normally.

Solution

Rebuild the index.

drop index tab_idx；
create index tab_idx on table &tablename (column1,column2..);

10 Error when updating and deleting `cannot delete from table "&tablename" because it does not have a replica identity and publishes deletes`

Problem Analysis

The error table is turned on logical copying. If the logically copied table is not set, you can only perform insert operations. Update and delete will report the above errors. This copy logic appears in early postgresql, but this feature has always existed in subsequent versions. It is recommended not to enable this feature in YMatrix.

Problem recurs

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.

Solution

Sets the previous image value that contains all columns when updating and deleting.

alter table &schema.tablename REPLICA IDENTITY FULL;

Or delete the corresponding copy and publish task.

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"`

Problem Analysis

The file block is corrupt, causing the corresponding file to be unreadable

Problem recurs

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)

Find the corresponding file according to the relfilenode and destroy any one (the index file is destroyed here).

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

Solution

According to the error relfilenode, confirm whether the damaged table file or index file is: Situation 1: The index file is corrupted. Just rebuild directly, use the reindex command. Case 2: If there is Mirror, the data table file is corrupted. Just copy the Mirror file of the same content and replace the Primary file. Situation 3: No Mirror, the data table file is corrupted, the data of the normal instance is copied separately, the table is rebuilt, and the data of the absolute instance will be lost.

12 row to column examples

When using YMatrix, you will encounter SQL requirements for row-to-column conversion. The following is a demonstration of this problem. The specific steps are as follows:

Row to column example

Create a test table

create table test (name varchar(20),zbfm varchar(20),value integer);

Insert test data

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);

Query test data

select * from test;
name | zbfm | value 
-----+------+--------
张三 | 年龄 |    60
张三 | 身高 |    95
张三 | 体重 |    31
张三 | 鞋码 |    42
王五 | 年龄 |    97
王五 | 身高 |    75
王五 | 体重 |    66
王五 | 鞋码 |    44
李四 | 年龄 |    50
李四 | 身高 |    83
李四 | 体重 |    84
李四 | 鞋码 |    43
(12 rows)

Row-to-column display data

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 columns to change the line example

When using YMatrix, you will encounter SQL requirements for column-to-line conversion. The following is a demonstration of this problem. The specific steps are as follows:

Column conversion example

Create a test table

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 
);

Insert test data

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);

View the test data

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)

Display the data in columns and rows

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` Remote access permission exception caused by the order of the file content configuration

Problem Analysis

pg_hba.conf is a configuration file that restricts remote access to the database permissions. The order of access permissions is read from top to top. The configuration order is incorrect, which will cause permission control to fail.

Problem recurs

Create a test user.

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=>

Restrict test users to remote login by modifying pg_hba.conf. Limit configuration at the end of the file.

[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
//Test login, login is normal, and the remote is not restricted successfully
[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=>

Solution

Check the current user's restrictions in pg_hba.conf and add the permission restriction line to the appropriate location.

//Adjusted `pg_hab.conf` file
[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
//Test login, login failed, and remote restrictions were successfully restricted
[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` Report an error

Problem Analysis

idle in transaction connection timeout, which is controlled by parameters and should be cautious when setting it.

Problem recurs

Check the current parameter settings.

test=# show idle_in_transaction_session_timeout ;
 idle_in_transaction_session_timeout 
----------------------------------------------------------------------------------------------------------------------------------
 100s
(1 row)

For demonstration convenience, set this parameter to 10s at the session level.

test=# set idle_in_transaction_session_timeout ='10s';
SET
test=# show idle_in_transaction_session_timeout ;
 idle_in_transaction_session_timeout 
----------------------------------------------------------------------------------------------------------------------------------
 10s
(1 row)

Turn on the transaction and test the impact of this parameter.

test=# begin ;
BEGIN
test=# select count(*) from pg_tables;
 count 
-------
   104
(1 row)

Wait for more than 10 seconds before executing the second time.

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.

Check the database log.

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,

Solution

Set this parameter with caution, which may cause long transactions to roll back.
When encountering this problem, you can modify the system parameters or set session-level parameters to control it.

System-level parameter modification.

$ gpconfig -c idle_in_transaction_session_timeout -v 10s
$ gpstop -u // 使配置生效

Set parameters at the session level.

$ set idle_in_transaction_session_timeout='10s';

16 `insufficient memory reserved for statement`

Problem Analysis

The session uses memory that exceeds the statement_mem setting memory. The amount of customer data is not large, but there are very many partitions. Too many partitions will cause a large memory usage when count(*).

Problem recurs

Create a partition table with as many partitions as possible.

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

Insert a small amount of data into the 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

An error occurred in the search table.

test=# select count(*) from test;
psql: ERROR:  insufficient memory reserved for statement

Solution

Temporary Solution
- Modify the statement_mem parameter at the session level set statement_mem ='1000MB'
Permanent solution
- Adjust the system value of statement_mem gpconfig -c statement_mem -v 10000MB. When adjusting, you need to pay attention to the size ofmax_statement_mem`.
- If you do not want to adjust the system parameters, you need to re-plan the partition. The comparison of the current partition with the entire table data volume is unreasonable.

17 `evalplanqual can not hanlde subplan with motion node`

Problem Analysis

Different transactions update the same distributed key data. After the initial transaction is submitted, other transactions report the error.

Problem recurs

Create a table.

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

Session A enables a record of transaction update id=1.

test=# begin;
BEGIN
test=# update test1 set id=11 where id=1;
UPDATE 1

Session B Enables a record of transaction update id=1.

test=# begin;
BEGIN
test=# update test1 set id=11 where id=1;

Submit transactions for session A.

test=# begin;
BEGIN
test=# update test1 set id=11 where id=1;
UPDATE 1
test=# 
test=# commit;
COMMIT

Session B reported an error.

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)

Solution

Avoid multiple transactions automatically updating the same distributed key at the same time.
Try to use the database's automatic transaction function when updating data, without manually starting the transaction.
Try to avoid using long transactions in the program.

18 Creating a table on the client cannot query on Linux, but it can query on the client

Error message

SELECT * FROM ttemp;
psql: ERROR:  relation "ttemp" does not exist
LINE 1: SELECT * FROM ttemp;
                      ^

Problem Analysis

View error.

psql: ERROR:  relation "ttemp" does not exist
LINE 1: SELECT * FROM ttemp;
                      ^

Execute \dn to view the schema list, show search_path; to view the current default Schema, confirm which Schema this table is created, and execute the query plus 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)

I found that the default Schema is `public`.

2. Execute `\l` to view the database list.
```Bash
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)

Check whether the connection information of the client and the program is correct.

It was found that the program was connected to the database cluster with port 5432, and the Linux one was connected to the port 5433.

postgres=# SHOW port;
 port 
------
 5433
(1 row)

Solution

Close the cluster with port 5433.

[mxadmin@mdw ~]$ gpstop -a

Modify the port and MASTER_DATA_DIRECTORY information of the environment variable.

$ vim ~mxadmin/.matrixdb.env

export PGPORT=5432
export MASTER_DATA_DIRECTORY=/mxdata_20220909145815/master/mxseg-1

Load new environment variables.

$ source ~mxadmin/.matrixdb.env

19 Data loss and data duplication problems occur when data is inserted in UUID distribution keys

Problem Description

Perform the INSERT INTO table_uuid SELECT ... FROM operation, and the number of inserted data strips fluctuates. At the same time, the SELECT result does not fluctuate and has stable output.

Given the following example: Execute the INSERT operation multiple times and find that the execution result fluctuates, and the SELECT result is a stable 1 output.

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

Problem Analysis

This problem occurs because of a combination of the following three conditions:

SET OPTIMIZER TO ON.
Use random functions like random() to generate a UUID.
The distribution key inserted into the target table is UUID.

Solution

Change one of the above three conditions to avoid such problems.

20 The operating system version corresponding to the database has been upgraded from CentOS7 to CentOS8, and the query statement performance has been degraded

Problem Analysis

Redhat8 has added the ssd service, which will authenticate all connection accesses, resulting in a lower efficiency in connection creation and authentication, especially in scenarios where there are a large number of slices, and the efficiency of query statements has decreased significantly.

Problem recurs

Build a cluster on the CentOS7 operating system
Build a cluster on the CentOS8 operating system
Create multiple related tables
Perform multi-table association query
Comparison of the query efficiency of databases in two systems

Solution

Change the small table to a copy table to reduce slice
Close the corresponding operating system service
```
systemctl stop sssd
systemctl stop sssd-kcm.socket
```
21 insufficial memory reserved for statement error is displayed when executing SQL statements

Problem Analysis

The memory that querying the scheduled evaluation when executing SQL exceeds the limit of the statement_mem parameter.

Solution

Increase the statement_mem parameter value to avoid the memory evaluated during query exceeding the limit.
Check whether the query table is a partitioned table, and if so, merge the partitioned table.

22 Error when creating `matrixts` extension `ERROR: type "mxkv_text" already exist`

Problem recurs

Create the source library db1, the target library db2, and create the matrixts extension and several tables on db1.

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

Use the tools → Structure Synchronization in the Navicat Premium client software to synchronize the relevant tables, views, functions, etc. of the database db1 to the database db2.

At this time, execute CREATE EXTENSION matrixts in the database db2, and a similar error will occur:

ERROR:  type "<类型名称>" already exists

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

For example:

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)

Problem Analysis

This problem is because when synchronizing DDL through a third-party client, the functions, types and definitions required for the matrixts extension are synchronized over.

When a user creates a matrixts plug-in through CREATE EXTENSION matrixts, the matrixts plug-in cannot be initialized due to the existence of functions, types, etc. of the same name.

Solution

If you have received an error due to improper operation, please delete the conflicting function and type of the same name.

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);

If you have not performed synchronization operations on the client, you can create a new database. After creating the matrixts plug-in under the database, synchronize the definitions of tables, views, etc.

YMatrix SQL Query FAQ

1 ERROR: EvalPlanQual can not handle subPlan with Motion node

2 ERROR: Too many unresolved insertion xids, please do a vacuum

3 ERROR: modification of distribution columns in OnConflictUpdate is not supported

4 Graphical client received log: server closed the connection unexpectedly

5 The UNION ALL query for simple Filter operations is slower than the IN query

6 Insert int type data, the query runs very fast, but it is slow to put it in Plpgsql Function

7 PARTITION partition cropping and update operation, two sessions (Session) independently update will cause interlocking

8 Custom type usage and single table field extension

9 ERROR,XX000,Right sibling's left-link doesn't match : block 817 links to 45366 instead of expected 70930 in index ""tab_idx""

10 Error when updating and deleting cannot delete from table "&tablename" because it does not have a replica identity and publishes deletes

11 ERROR: could not read block 0 in file "base/1588803/269422"

12 row to column examples

13 columns to change the line example

14 pg_hba.conf Remote access permission exception caused by the order of the file content configuration

15 idle in transaction timeout Report an error

16 insufficient memory reserved for statement

17 evalplanqual can not hanlde subplan with motion node

18 Creating a table on the client cannot query on Linux, but it can query on the client

postgres=# SHOW search_path ; search_path

19 Data loss and data duplication problems occur when data is inserted in UUID distribution keys

20 The operating system version corresponding to the database has been upgraded from CentOS7 to CentOS8, and the query statement performance has been degraded

21 insufficial memory reserved for statement error is displayed when executing SQL statements

22 Error when creating matrixts extension ERROR: type "mxkv_text" already exist

1 `ERROR: EvalPlanQual can not handle subPlan with Motion node`

2 `ERROR: Too many unresolved insertion xids, please do a vacuum`

3 `ERROR: modification of distribution columns in OnConflictUpdate is not supported`

4 Graphical client received log: `server closed the connection unexpectedly`

10 Error when updating and deleting `cannot delete from table "&tablename" because it does not have a replica identity and publishes deletes`

11 `ERROR: could not read block 0 in file "base/1588803/269422"`

14 `pg_hba.conf` Remote access permission exception caused by the order of the file content configuration

15 `idle in transaction timeout` Report an error

16 `insufficient memory reserved for statement`

17 `evalplanqual can not hanlde subplan with motion node`

21 `insufficial memory reserved for statement` error is displayed when executing SQL statements

22 Error when creating `matrixts` extension `ERROR: type "mxkv_text" already exist`