本文为您介绍编写和优化Hologres SQL语句时可能遇到的常见问题及解决方法。
报错:Creating publication with table that without binlog is not supported now
报错:internal error: Connect timeout, err: std_exception: Connection refused
报错:ALTER TABLE CHANGE OWNER is not supported in SLPM (Schema-Level Permission Mode)
报错:remaining connection slots are reserved for non-replication superuser connections
报错:ERROR: commit ddl phase1 failed: DDLWrite is not allowed on replica
报错:ERROR: Query:[xxx] Get result failed: canceling statement due to user request
报错:Total memory used by all existing queries exceeded memory limitation
报错:ERROR: Feature not supported: INSERT on conflict contains un-unique column
报错:current transaction is aborted, commands ignored until end of transaction block
报错:ERROR: function jsonb_set(json, text[], jsonb, boolean) does not exist
报错:ERROR: new row for relation violates partition constraint
报错:SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction for table
写入或查询含有多表Join时报错: BinaryArray cannot contain more than 2147483646 bytes
导入或者查询数据时报错:Cannot reserve capacity larger than 2^31 - 1 for binary
报错:Creating publication with table that without binlog is not supported now
问题原因:对没有开启Binlog的表创建了Publication。
解决方法:Publication用于订阅Binlog,只允许对开启Binlog的表创建Publication。
报错:bigint out of range
问题原因:bigint类型的数据长度超过了限制。
解决方法:处理脏数据,使其在bigint类型的长度范围内。
报错:too many shards in this instance
问题原因:当前实例设置的Shard数量超过了实例Shard总数的限制。
解决方法:梳理当前实例的Shard数,不建议设置超过总数的Shard数,详情请参见实例规格概述。
报错:Feature not supported: insert into parent table
问题原因:不支持将数据直接写入分区父表。
解决方法:需要将数据写入指定的分区子表。
报错:timestamp out of range
问题原因:TIMESTAMP类型的字段数据超过了精度限制。
解决方法:处理脏数据,使其在TIMESTAMP类型的数据精度范围内。
报错:internal error: Connect timeout, err: std_exception: Connection refused
问题原因:通常是由于某种原因导致了实例宕机,从而出现报错。
解决方法:提交工单排查Hologres实例宕机原因。
报错:value too long for type character varying(256)
问题原因:字段长度超过了VARCHAR类型的长度限制。
解决方法:处理数据或将VARCHAR的长度设置得更长。
报错:internal error: Write is not allowed in readonly mode
问题原因:不支持在从实例中执行INSERT、UPDATE或DELETE等操作。
解决方法:从实例中只允许执行SELECT操作,其余操作请在主实例中执行。
报错:SELECT INTO is not supported now
问题原因:Hologres不支持使用SELECT INTO语法。
解决方法:您可使用INSERT INTO SELECT方式插入数据,详情请参见INSERT。
报错:ALTER TABLE CHANGE OWNER is not supported in SLPM (Schema-Level Permission Mode)
问题原因:不支持在SLPM模型下使用ALTER TABLE方式改变表owner。
解决方法:SLPM权限模式的表Owner都是用户组,可以将用户从用户组中移除。
报错:ERROR: HGERR_msge division by zero
问题原因:不支持除数为0。
解决方法:
使用CASE WHEN方法绕过。
创建MySQL兼容性插件,开启允许除以0的开关,详情请参见迁移MySQL至Hologres。
--创建MySQL兼容性插件,需要Superuser执行,一个数据库只需要执行一次即可 create extension if not exists mysql_compatible; --设置除以0开关(开启后允许DQL除零容忍) set mysql_compatible.enable = on;
报错:remaining connection slots are reserved for non-replication superuser connections
问题原因:当前连接数已满,导致非Superuser账号无法连接。
解决方法:使用Superuser账号释放空闲连接,详情请参见释放连接。
报错:Build desc failed: Column type: information_schema.sql_identifier can not translate into hologres type
问题原因:部分系统表的字段类型比较复杂,当前Hologres不支持。
解决方法:不建议将复杂的系统表数据与Hologres表关联查询。
报错:ERROR: permission denied for table pg_subscription
问题原因:非Superuser执行
select hg_dump_script
语句需要具备一定的权限才能执行。解决方法:
可以使用Superuser用户执行
select hg_dump_script('表名')
获取DDL.可以使用Superuser用户授予当前用户pg_subscription的查看权限,授权示例如下。
grant select on pg_subscription to "xxxx";
报错:CREATE TABLE is not supported for current instance
问题原因:不支持在共享集群中创建内部表。
解决方法:如果需要创建内部表,需使用通用型实例。共享集群仅支持创建外部表,外部表创建语法请参见CREATE FOREIGN TABLE。
报错:DISTINCT is not implemented for window functions
问题原因:Hologres目前不支持在窗口函数中使用DISTINCT关键字。
解决方法:窗口函数中去掉DISTINCT。
报错:ERROR: commit ddl phase1 failed: DDLWrite is not allowed on replica
问题原因:不支持在从实例中修改IP白名单。
解决方法:需要在主实例中修改IP白名单。
报错:Feature not supported: INSERT ON CONFLICT RETURNING
问题原因:Hologres暂不支持
insert on conflict returning
的语法。解决方法:去掉returning,详情请参见INSERT ON CONFLICT(UPSERT)。
报错:ERROR: Query:[xxx] Get result failed: canceling statement due to user request
问题原因:Query设置了超时时间,运行时间过长导致超时。
解决方法:优化Query,缩短其运行时间,或者重新设置更加合理的超时时间。详情请参见Query管理。
报错:Total memory used by all existing queries exceeded memory limitation
问题原因:Query由于内存超出,导致出现OOM。
解决方法:详情请参见OOM常见问题排查指南。
报错:ERROR: INSERT in ddl transaction is not supported now
问题原因:不支持在事务中执行INSERT操作。如:
begin; insert xxx commit; ERROR: INSERT in ddl transaction is not supported now
解决方法:在事务外执行INSERT操作。
报错:ERROR: INSERT has more expressions than target columns
问题原因:INSERT写入的列与目标表的列数不一致。
解决方法:INSERT写入的列需要与目标表列数保持一致,一一对应。
报错:ERROR: Feature not supported: INSERT on conflict contains un-unique column
问题原因:INSERT ON CONFLICT中的CONFLICT条件使用了非主键字段。
解决方法:INSERT ON CONFLICT中的CONFLICT条件只能使用主键字段,详情请参见INSERT ON CONFLICT(UPSERT)。
报错:query is cancelled Cannot find index full ID:xxx (table id: x, index id: x) in storages or it is deleting
问题原因:Query执行过程中,涉及到的表存在TRUNCATE或DROP行为,导致表的table id发生变化,Query被取消了。
解决方法:使用Query洞察排查当前Query是否在同一时间执行了TRUNCATE或DROP操作,详情请参见Query洞察。
报错:ALTER TABLE ALTER COLUMN SET TYPE is not supported now
问题原因:当前不支持通过ALTER TABLE方式修改表中的列类型。
解决方法:重新建表并修改列类型。
报错:current transaction is aborted, commands ignored until end of transaction block
问题原因:通常是由于上一个Transaction的命令未执行完,又开始执行下一个命令导致报错。如:
begin; create xxxx begin;
解决方法:执行
rollback;
命令结束当前事务并回滚所有未提交的更改。
报错:Feature not supported: UPDATE with shard keys
问题原因:Hologres不支持对主键或Distribution Key执行UPDATE操作。
解决方法:修改SQL重新执行。
报错:query length xxxxx exceeded the maximum 102400
问题原因:Query的长度超过了102400字节的限制。
解决方法:改写Query,使其长度保持在规定范围内。
报错:The specified partition does not exist
问题原因:通常发生在数据写入或更新时,对应的表分区不存在。
解决方法:提前创建对应的分区。
报错:time before 1925 not supported
问题原因:时间相关的函数默认支持的范围为1925~2282年,超过该范围则会报错。
解决方法:如果是to_char、to_date或to_timestamp等函数,可以在SQL前通过设置GUC参数
hg_experimental_functions_use_pg_implementation
以支持更大范围的时间。详情请参见时间和日期转换函数。
报错:ERROR:function xxx does not exist
问题原因:使用函数时未创建对应的extension,或函数语法不满足规定。
解决方法:按照Hologres的规定正确使用函数。
报错:Update row with Key (id)=(xxxx) multiple times
问题原因:执行UPDATE操作时,主键有重复数据。具体如下:
数据来源确实有主键重复数据。
表设置了生命周期TTL,TTL到期了但数据还未被删除。
解决方法:
对于数据来源确实有主键重复数据:可在执行UPDATE操作前处理重复数据。
对于设置了TTL的表:可使用hg_remove_duplicated_pk函数清理过期数据,详情见INSERT ON CONFLICT(UPSERT)。
报错:Modify record by primary key is not on this table
问题原因:通常是通过Flink写入数据时选择了更新模式,但目标表没有主键,导致数据无法更新。
解决方法:为目标表设置合适的主键,详情请参见主键Primary Key。
报错:null value in column "xxx" violates not-null constraint
问题原因:向表中被定义为NOT NULL的列插入了NULL值。
解决方法:去掉NULL值后再插入数据。
报错:ERROR: Currently materialized view does not support aggregate on expressions, only support single column
问题原因:单表物化视图不支持表达式,从而导致报错。
解决方法:单表物化视图不使用表达式,详情请参见实时物化视图(Beta)。
报错:invalid input syntax for type numeric: \"\"
问题原因:NUMERIC类型的字段有脏数据,不符合NUMERIC的数据规范。
解决方法:处理脏数据。
报错:ERROR: function jsonb_set(json, text[], jsonb, boolean) does not exist
问题原因:JSONB_SET函数传入的参数类型不匹配导致报错。
解决方法:JSONB_SET函数第一个参数需要传入JSONB类型,详情请参见JSON和JSONB类型。
报错:ERROR: new row for relation violates partition constraint
问题原因:分区表写入的值与定义的值不一致。例如分区定义的值为20240110,但是写入了其他分区值,导致了分区冲突,示例如下:
CREATE TABLE public.tbl_20240110 PARTITION OF public.tbl FOR VALUES IN('20240110'); INSERT INTO public.tbl_20240110 SELECT * FROM odps_tbl where sale_date >'20240110'; error: new row for relation "tbl_20240110" violates partition constraint
解决方法:向分区表内写入符合分区约束的分区值。
报错:Group by key is type of imprecise not supported
问题原因:Group by的字段类型是非精确类型,导致报错。
解决方法:Group by中避免非精确数据类型,如float等,建议使用精确的数据类型。
报错:ERROR: xxx for fe ,should not be evaluated
问题原因:通常是使用某个函数查询系统表导致的报错。该函数运行在Hologres的引擎,但是系统表的计算不会经过Hologres引擎。所以在执行时,将函数直接计算而不经过Hologres引擎,导致了报错。
解决方法:不建议使用该函数操作Hologres系统表。
报错:SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction for table
问题原因:建表时CREATE TABLE和call属性不在一个事务(transaction)中,导致报错。
解决方法:建表时CREATE TABLE需要和call属性放在同一个事务中,示例如下:
begin; CREATE TABLE tbl ( xxxx ); call set_table_property('tbl', 'orientation', 'xx'); call set_table_property('tbl', 'distribution_key', 'xxx'); commit;
如何处理数字开头的字段?
交互式分析Hologres兼容PostgreSQL,使用语法同PostgreSQL,不支持数字开头的字段。
如果您使用Hologres时遇到数字开头的字段,查询时需要为该字段增加双引号,示例如下。
select bizdate,"1_day_active_users","7_day_active_users" from t_active_users;
执行DROP TABLE语句报错:cannot drop table
问题原因
表存在相关依赖,例如有视图使用了此表,导致删除表失败。
解决方法
使用如下命令删除表的同时删除相关依赖。
DROP TABLE <table_name> CASCADE;
table_name为要删除表的名称。
写入或查询命令中含有多表Join时报错: BinaryArray cannot contain more than 2147483646 bytes
问题原因
Hologres单个字段大小的上限是2 GB,在统计信息未及时更新的情况下,多表Join时生成了不合理的执行计划,从而出现超过2 GB的报错。
解决方法
对Join的表执行如下命令用于更新表的统计信息。
analyze <tablename>;
如果更新表的统计信息后仍然报错,说明数据中有较大字段,在SQL前使用如下命令添加GUC参数解决。
set hg_experimental_query_batch_size = 1024;
建表时报错:CREATE TABLE is not supported for current instance
问题原因
当前实例是共享集群(实例Endpoint以
hgmc
开头,如hgmc-cn-xxwwwkkk
,可以前往Hologres管理控台查看实例Endpoint),共享集群不支持创建内部表,只能创建外部表。解决方法
按照创建外部表语法创建外部表,详情请参见CREATE FOREIGN TABLE。
使用Hologres独享示例,可支持创建内部表。
导入或者查询数据时报错:Cannot reserve capacity larger than 2^31 - 1 for binary
问题原因及解决方法
原因一:Hologres单个字段大小的上限为2 GB,在统计信息未及时更新的情况下,不合理的执行计划,从而出现超过2 GB的报错。
解决方法:对表执行
analyze
命令,用于更新表的统计信息。analyze <tablename>;
原因二:表字段数据量超过2 GB,导致报错。
解决方法:在SQL前面添加以下GUC参数以降低每次Query读取的批次大小。
set hg_experimental_query_batch_size = 1024;
执行SQL时报错:internal error: Sequence is used out
问题原因:表中有serial类型,serial类型值超过了取值范围。
解决方法:
方法1:如果是serial类型,建议重新建表,并将类型改为bigserial。
方法2:serial类型在写入时会获取表锁,建议避免使用serial类型。
报错:ERROR: ORCA failed to produce a plan : Lookup of object [failed to get table cluster index ] in cache failed
问题原因:建表时列名中含有空格。
解决方法:删除该表重新创建。