金仓数据库问题汇总

1. cache lookup failed for type 536871955com.kingbase8.util.KSQLException: 错误: cache lookup failed for type 536871955; uncategorized SQLException; SQL state [XX000]; error code [0]; 错误: cache lookup failed for type 536871955; nested exception is com.kingbase8.util.KSQLException: 错误: cache lookup failed for type 536871955过程导出 pg 数据直接导入金仓的数据库没有问题因为金仓有 pg 的兼容模式然后程序启动出现 cache lookup failed for type 536871955问题解决datasource.urljdbc:kingbase8://IP:54321/数据库名称?preferQueryModesimple加上 preferQueryModesimple2. 保存多序列问题这个就是数据库迁移的时候金仓有添加了一遍自增键值这个删除即可。DO$$DECLAREr RECORD;v_countINTEGER:0;BEGINRAISE NOTICE 开始解除 Identity 字段多余 AUTO Sequence ;FORrINSELECTtn.nspnameAStable_schema,t.relnameAStable_name,a.attnameAScolumn_name,sn.nspnameASsequence_schema,s.relnameASsequence_nameFROMpg_depend dJOINpg_class sONs.oidd.objidANDs.relkindSJOINpg_namespace snONsn.oids.relnamespaceJOINpg_class tONt.oidd.refobjidJOINpg_namespace tnONtn.oidt.relnamespaceJOINpg_attribute aONa.attrelidt.oidANDa.attnumd.refobjsubidWHEREtn.nspnamepublic-- 必须是 Identity 字段ANDa.attidentity-- 只处理 AUTO dependencyANDd.deptypea-- 必须明确存在且只存在一个 INTERNAL SequenceAND(SELECTCOUNT(*)FROMpg_depend diJOINpg_class siONsi.oiddi.objidANDsi.relkindSWHEREdi.refobjidd.refobjidANDdi.refobjsubidd.refobjsubidANDdi.deptypei)1-- 当前字段必须确实存在多个拥有序列AND(SELECTCOUNT(*)FROMpg_depend dxJOINpg_class sxONsx.oiddx.objidANDsx.relkindSWHEREdx.refobjidd.refobjidANDdx.refobjsubidd.refobjsubidANDdx.deptypeIN(a,i))1ORDERBYtn.nspname,t.relname,a.attname,s.relnameLOOPRAISE NOTICE解除%.% 字段 % - %.%,r.table_schema,r.table_name,r.column_name,r.sequence_schema,r.sequence_name;EXECUTEformat(ALTER SEQUENCE %I.%I OWNED BY NONE,r.sequence_schema,r.sequence_name);v_count :v_count1;ENDLOOP;RAISE NOTICE;RAISE NOTICE处理完成共解除 % 个多余 Sequence 关联,v_count;RAISE NOTICE;END$$;3. 自增表主键修复DO$$DECLAREr RECORD;v_data_valueBIGINT;v_last_valueBIGINT;v_is_calledBOOLEAN;v_fix_countINTEGER:0;v_skip_countINTEGER:0;BEGINRAISE NOTICE;RAISE NOTICE开始检查并修复 public 模式所有自增 Sequence;RAISE NOTICE;FORrINWITHauto_sequencesAS(/* * 1. IDENTITY 字段 * 直接获取 deptype i 的内部 Sequence * */SELECTtn.nspnameAStable_schema,t.relnameAStable_name,a.attnameAScolumn_name,sn.nspnameASsequence_schema,s.relnameASsequence_name,s.oidASsequence_oid,ps.seqincrementASsequence_incrementFROMpg_class tJOINpg_namespace tnONtn.oidt.relnamespaceJOINpg_attribute aONa.attrelidt.oidANDa.attnum0ANDNOTa.attisdroppedJOINpg_depend dONd.refobjidt.oidANDd.refobjsubida.attnumANDd.deptypeiJOINpg_class sONs.oidd.objidANDs.relkindSJOINpg_namespace snONsn.oids.relnamespaceJOINpg_sequence psONps.seqrelids.oidWHEREtn.nspnamepublicANDt.relkindIN(r,p)ANDa.attidentityUNION/* * 2. SERIAL / DEFAULT nextval(...) 字段 * * 不依赖 OWNED BY 判断 * 直接找 DEFAULT 真正引用的 Sequence。 * */SELECTtn.nspnameAStable_schema,t.relnameAStable_name,a.attnameAScolumn_name,sn.nspnameASsequence_schema,s.relnameASsequence_name,s.oidASsequence_oid,ps.seqincrementASsequence_incrementFROMpg_class tJOINpg_namespace tnONtn.oidt.relnamespaceJOINpg_attribute aONa.attrelidt.oidANDa.attnum0ANDNOTa.attisdroppedJOINpg_attrdef adONad.adrelidt.oidANDad.adnuma.attnumJOINpg_depend ddONdd.classidpg_attrdef::regclassANDdd.objidad.oidJOINpg_class sONs.oiddd.refobjidANDs.relkindSJOINpg_namespace snONsn.oids.relnamespaceJOINpg_sequence psONps.seqrelids.oidWHEREtn.nspnamepublicANDt.relkindIN(r,p)ANDpg_get_expr(ad.adbin,ad.adrelid)LIKE%nextval(%)SELECTDISTINCTtable_schema,table_name,column_name,sequence_schema,sequence_name,sequence_oid,sequence_incrementFROMauto_sequencesORDERBYtable_schema,table_name,column_nameLOOP/* * 正向自增 * 使用 MAX(id) * * 反向序列 * 使用 MIN(id) * */IFr.sequence_increment0THENEXECUTEformat(SELECT MAX(%I)::BIGINT FROM %I.%I,r.column_name,r.table_schema,r.table_name)INTOv_data_value;ELSEEXECUTEformat(SELECT MIN(%I)::BIGINT FROM %I.%I,r.column_name,r.table_schema,r.table_name)INTOv_data_value;ENDIF;/* 空表不用处理 */IFv_data_valueISNULLTHENRAISE NOTICE跳过空表%.% [%] - %.%,r.table_schema,r.table_name,r.column_name,r.sequence_schema,r.sequence_name;v_skip_count :v_skip_count1;CONTINUE;ENDIF;/* 获取当前 Sequence 状态 */EXECUTEformat(SELECT last_value, is_called FROM %I.%I,r.sequence_schema,r.sequence_name)INTOv_last_value,v_is_called;/* * 正向 Sequence * */IFr.sequence_increment0THEN/* * Sequence 已经大于表最大 ID * 不往回调避免不必要的 ID 重用。 */IFv_last_valuev_data_valueTHENRAISE NOTICE无需修改%.% [%] seq% MAX% last%,r.table_schema,r.table_name,r.column_name,r.sequence_name,v_data_value,v_last_value;CONTINUE;ENDIF;/* * 设置到当前 MAX(id)并设置 is_calledtrue。 * * 下一次 nextval * MAX(id) increment */PERFORM setval(r.sequence_oid::regclass,v_data_value,true);/* * 反向 Sequence * */ELSEIFv_last_valuev_data_valueTHENRAISE NOTICE无需修改%.% [%] seq% MIN% last%,r.table_schema,r.table_name,r.column_name,r.sequence_name,v_data_value,v_last_value;CONTINUE;ENDIF;PERFORM setval(r.sequence_oid::regclass,v_data_value,true);ENDIF;v_fix_count :v_fix_count1;RAISE NOTICE【已修复】%.% [%] Sequence%.% 数据边界% 原last%,r.table_schema,r.table_name,r.column_name,r.sequence_schema,r.sequence_name,v_data_value,v_last_value;ENDLOOP;RAISE NOTICE;RAISE NOTICE全部处理完成;RAISE NOTICE实际修复 Sequence 数量%,v_fix_count;RAISE NOTICE空表跳过数量%,v_skip_count;RAISE NOTICE;END$$;4 数据库连接数过多我使用的是V9,授权证书限制连接数数10改配置文件重启都没有用只能删减一下SELECTsys_catalog.sys_terminate_backend(pid)FROMsys_catalog.sys_stat_activityWHEREdatnameintesimANDusenameintesimANDstateidleANDstate_changeNOW()-INTERVAL30 minutesANDpidsys_catalog.sys_backend_pid();如果sys_catalog.sys_stat_activity仍不存在但pg_view有值说明当前实例使用 PostgreSQL 兼容系统目录改用SELECTpg_catalog.pg_terminate_backend(pid)FROMpg_catalog.pg_stat_activityWHEREdatnameintesimANDusenameintesimANDstateidleANDstate_changeNOW()-INTERVAL30 minutesANDpidpg_catalog.pg_backend_pid();