greenplum（postgreSQL底层）在Navicat可视化界面修改数据失败或数据库数据等值查询失败问题成因以及解决办法

本文链接：https://blog.csdn.net/m0_64774905/article/details/137682595

1.问题描述

在使用postgresql的时候遇到一个问题，我在navicat界面修改数据，在修改数据后刷新表，数据在刷新表之后又回滚回原来的值。

如图所示，该值有个空格，我在这里将空格删除掉，保存后刷新页面，空格又回来了：

此时使用SQL用等值查询会失败，只能用模糊查询：字段 like '%xx%'：

如下图

2.问题成因

由于该表的创建并不是在Greenplum数据库直接创建，而是在navicat使用数据传输功能从mysql传输到Greenplum数据库，或者创建数据表的时候没有指定分布键，但是由于Greenplum是分布式系统在创建表时需要指定分布键，而从mysql传到Greenplum数据库的表并没有指定分布键，导致数据表实际采取的分布键策略我们无法得知数据表实际采取的分布键策略（有可能采用了随机分布）。

Greenplum是分布式系统，创建表时需要指定分布键（创建表需要CREATEDBA权限），目的在于将数据平均分布到各个segment。选择分布键非常重要，选择错了会导致数据不唯一，更严重的是会造成SQL性能急剧下降。

Greenplum有两种分布策略：

1、hash分布。

Greenplum默认使用hash分布策略。该策略可选一个或者多个列作为分布键（distribution key，简称DK）。分布键做hash算法来确认数据存放到对应的segment上。相同分布键值会hash到相同的segment上。表上最好有唯一键或者主键，这样能保证数据均衡分不到各个segment上。语法，distributed by。

如果没有主键或者唯一键，默认选择第一列作为分布键。增加主键

2、随机（randomly）分布。

数据会被随机分不到segment上，相同记录可能会存放在不同的segment上。随机分布可以保证数据平均，但是Greenplum没有跨节点的唯一键约束数据，所以无法保证数据唯一。基于唯一性和性能考虑，推荐使用hash分布，性能部分会另开一篇文档详细介绍。语法，distributed randomly。

3.复制分布（这种分布策略是GPDB 6的新增特性）

Greenplum数据分布和分区策略

要使用这一策略，需要在创建表使用 “DISTRIBUTED REPLICATED” 子句。

Greenplum数据库将每行数据分配到每个segment上。这种分布策略下，表数据将均匀分布，因为每个segment都存储着同样的数据行。当您需要在segment上执行用户自定义的函数且这些函数需要访问表中的所有行时，就需要用到复制分布策略。或者当有大表与小表join，把足够小的表指定为replicated也可能提升性能。

3.解决办法

解决办法需要两个步骤：

1.将表指定正确的分布键，通常是主键；

SQL语句：

ALTER TABLE 表名 SET DISTRIBUTED BY (分布键);

2.当hash分布表的分布键修改时，表数据会自动重新分布。但修改分布策略为随机分布时不会引起重新分布，所以我们需要将表的分布情况手动重新分布一下

SQL语句：

ALTER TABLE 表名 SET WITH (REORGANIZE=TRUE);

注：如果需要快速修改则需要拼接字符串，拼接批量修改语句，将下列语句模式名以及针对表名的筛选条件即可获得该模式下多表的运行语句，其中kcu.column_name是表的主键，若表无主键则无法生成对应表的SQL语句。

SELECT concat('ALTER TABLE ',tc.table_name,' SET DISTRIBUTED BY (',kcu.column_name,');ALTER TABLE ',tc.table_name,' SET WITH (REORGANIZE=TRUE);')

  FROM information_schema.table_constraints AS tc

  JOIN information_schema.key_column_usage  AS kcu

    ON tc.constraint_name = kcu.constraint_name

 WHERE tc.constraint_type = 'PRIMARY KEY'

   AND tc.table_name      in  (

SELECT DISTINCT

"table_name"

FROM

information_schema.tables t1,

pg_class t2

WHERE

table_schema = '模式名'

AND t1."table_name" = t2.relname

    -- and 针对表名的其他筛选条件（不加其他筛选条件就是获取模式中所有有主键的表的语句）

    );