2025PG版本兼容性案例

一些兼容性案例及PG18新特性前瞻2025我的新特性分享(反吐槽：PG没有挤牙膏)PG15新特性精编版

/doc/132281并行distinct、fdw并行提交、全备lz4和zstd压缩、basic_archive归档、视图基表权限检测、参数可授权、json日志、startup日志、统计信息内存化、逻辑复制行列过滤、MERGEINTO(带and)、NULLsDistinct、FKactionsetnull(b,c)、\dconfig、迁移一致的DB、刷新OS排序、walinspectPG16新特性汇编

/doc/143240并行HashFull/RightJoin、string_agg并行聚合、大事务并行回放、RingBuffer、reservedconn、createroleselfgrant(家族世袭)、pg_stat_io、last_idx_scan、forcegenericplan、libpq负载均衡、replicafull标识(非唯一索引)、数字的多进制转换及下划线自定义分组、pg_input_is_valid、逻辑备份(lz4和zstd压缩)、copyfromdefault、fdw异步中止及抽样统计信息PG17RC1新特性体验

/doc/143246in查询加速、brin并行创建、reindexdb并行、基于ART自适应vacuum加速、io_combine_limit多块读pg_maintain、可关闭的altersystem、event_triggers及登录事件、seralize选项、%TYPE类型增量备份、dump/restore对象文件过滤、pg_createsubscriber、内置的localeproviderPG18展望：虚拟列逻辑复制、UUID唯一且有序、ACL函数、时态对象PG15的兼容性变化public模式create权限被回收(保持向下兼容可修改template1)pg_start_backup重命名为pg_backup_startpg_stop_backup重命名为pg_backup_stop废弃排他性备份模式(可能会导致重启失败)免密连接优先从HOME环境变量($HOME/.pgpass)读取密码文件废弃stats_temp_directory参数(受统计信息内存化影响)自定义变量前缀不允许与扩展插件名相同PG16的兼容性变化postgres进程统一：彻底抹除postmaster信息备库提升为主库废弃promote_trigger_file文件触发方式预防流复制冲突的vacuum_defer_cleanup_age参数被废弃服务端只读变量lc_collate和lc_ctype参数被删除：新版本可DB级设置ICU提示优化器考虑并行：force_parallel_mode重命名为debug_parallel_querylogical_replication_mode重命名为debug_logical_replication_streaming归档参数archive_library与archive_command互斥：不能同时使用非ASCII字符转换规则变更：application_name/cluster_name显示问号变HEX码PG17的兼容性变化pg_stat_bgwriter视图分裂出pg_stat_checkpointerpg_stat_progress_vacuum视图新增索引处理进度字段pg_stat_statements插件I/O读写时间拆分出shared和local关闭事务快照太旧的功能(废弃old_snapshot_threshold参数)关闭db级的用户功能(废弃db_user_namespace参数)删除trace_recovery_messages参数pg_stat_slru视图重命名SLRU名称删除了adminpack插件SQL绑定变量：JDBC驱动有硬限制Triedtosendanout-of-rangeintegerasa2-bytevalue:32768PreparedStatementcanhaveatmost65,535parameters.JDBC42.4.0之前：JDBC42.4.0开始：IN传参IN($1,$2,$3,$4,$5)ANY数组传参fooLIKEANY('{"%bar","%baz"}')fooILIKEANY('{"%bar","%baz"}')id<>ANY('{1,2,3}')INSERTINTOtest(id,ts,val)VALUES($1,$2,$3),($4,$5,$6),...,($2998,$2999,$3000);INSERTINTOtest(id,ts,val)

SELECT*

FROMunnest(

$1::timestamptz[],

$2::text[],

$3::float8[])InsertUnnest连接符||和IS

NOT

NULL的优先级SELECTcasewhent.a||''isnotnullthen1else2endresFROM(SELECT'a'::varcharASa)t;ERROR:argumentofCASE/WHENmustbetypeboolean,nottypetextPostgreSQL9.5之前，语句执行的逻辑是：t.a||(''isnotnull)PostgreSQL9.5开始，语句执行的逻辑是：(t.a||'')isnotnull9.5版本开始该语句可以正常执行，9.5之前会报错如下：10和11的两个兼容性案例extra_float_digits=3CREATETABLEt1(avarchar);

ps.setObject(1,newDouble(48));ps.setObject(1,newDouble(48.1));ps.setObject(1,newDouble(48.9));连接建立不规范会引起LOG膨胀可使用nc对比测试11和12nc-zv235432Ncat:Version7.50(/ncat)Ncat:Connectedto23:5432.Ncat:0bytessent,0bytesreceivedin0.01seconds.不遵守标准消息格式的客户端会大量记录如下日志incompletestartuppacket解决方法升级到PostgreSQL12日志文件循环记录使用工具定期过滤处理PostgreSQL12releasenoteunknown与any家族之宿敌化解Yourinstallationcontainsuser-definedobjectsthatrefertointernalpolymorphicfunctionswithargumentsoftype"anyarray"or"anyelement".Theseuser-definedobjectsmustbedroppedbeforeupgradingandrestoredafterwards,changingthemtorefertothenewcorrespondingfunctionswithargumentsoftype"anycompatiblearray"and"anycompatible'.PostgreSQL14多态函数参数类型变化-anyelement-->anycompatible-anyarray-->为anycompatiblearrayarray_appendarray_prependarray_catarray_positionarray_positionsarray_removearray_replacewidth_bucketleadlagCREATEFUNCTIONfoo(anyelement,anyelement)RETURNSboolLANGUAGESQLAS'select$1=$2';SELECTfoo('a','b');ERROR:couldnotdeterminepolymorphictypebecauseinputhastypeunknownOR缺失空格PostgreSQL15开始会报错ERROR:trailingjunkafternumericliteralatornear"1010R"CREATETABLEt(idint);SELECT*FROMtWHERE(id=101ORid=102);分区表DDL引起pg_dump等待会话1开启事务，查询分区表test_listbegin;select*fromtest_listlimit1;truncatetest_list;pg_dump-p5431-ta-s会话2执行truncate分区表test_list会话3导出a表的结构PostgreSQL15之前，普通表dump导出时会受分区表DDL操作而被阻塞，需要加超时参数分区表detach与外键管理CREATETABLElogs(idintegernotnull,log_datedatenotnull,messagetext)PARTITIONBYRANGE(log_date);

ALTERTABLEONLYlogsADDprimarykey(id,log_date);CREATETABLElogs_2024_01PARTITIONOFlogsFORVALUESFROM('2024-01-01')TO('2024-02-01');CREATETABLElogs_2024_02PARTITIONOFlogsFORVALUESFROM('2024-02-01')TO('2024-03-01');CREATETABLElogs_child(idinteger,log_datedate,messagetext,logs_parent_idinteger)PARTITIONBYRANGE(log_date);

ALTERTABLEONLYlogs_childADDprimarykey(id,log_date);ALTERTABLElogs_childaddconstraintlogs_child_fkforeignkey(logs_parent_id,log_date)referenceslogs(id,log_date)ONDELETECASCADE;

CREATETABLElogs_child_2024_01PARTITIONOFlogs_childFORVALUESFROM('2024-01-01')TO('2024-02-01');CREATETABLElogs_child_2024_02PARTITIONOFlogs_childFORVALUESFROM('2024-02-01')TO('2024-03-01');

INSERTINTOlogs(id,log_date,message)VALUES(1,'2024-01-15','Logmessage1');INSERTINTOlogs(id,log_date,message)VALUES(11,'2024-01-15','Logmessage1');INSERTINTOlogs(id,log_date,message)VALUES(2,'2024-02-15','Logmessage2');INSERTINTOlogs(id,log_date,message)VALUES(22,'2024-02-15','Logmessage2');INSERTINTOlogs_childvalues(1,'2024-01-15','Logmessage1',1);INSERTINTOlogs_childvalues(2,'2024-01-15','Logmessage1',1);INSERTINTOlogs_childvalues(5,'2024-02-15','Logmessage2',22);INSERTINTOlogs_childvalues(6,'2024-02-15','Logmessage2',2);ALTERTABLElogs_childDETACHPARTITIONlogs_child_2024_02;ALTERTABLElogs_child_2024_02DROPCONSTRAINTlogs_child_fk;ALTERTABLElogsDETACHPARTITIONlogs_2024_02;15.7、16.2归档命令：shell语法的%需双写PostgreSQL16配置归档策略：每天自动生成一个目录存放归档日志文件标准写法：双写百分号通过游标获取数据的兼容性psql-Ant--variable='FETCH_COUNT=100'-c"WITHdataAS(SELECTgenerate_series(1,20_000_000)asTotal)selectrepeat('a',100)||data.Total||repeat('b',800)astotal_patfromdata;"KilledLOG:"couldnotsenddatatoclient:Brokenpipe"PostgreSQL17PostgreSQL16发布注记与帮助链接应对策略点击发布注记后面的§符号，即可打开git链接如下：包括开发者的特性讨论及完整提交过程。9月26号PG17正式发布时，ReleaseNotes里增加了251处git链接标记。PG全球开发组一直使用git来跟踪项目特性的提交，从17开始每条发布注记后面都有相应的单条或多条git提交链接。注记的编写并不是一件容易的事，即要兼顾不同提交者的声音，又要对所有的变化保持简明扼要及张弛有度。对于数据库特性深度爱好者，注记内容的详实度级别是不够的，而这些新增的链接对他们如获至宝。通过链接他们不仅可以看清特性的实现源码还能观察到一些注释信息，更多注记里没有的细节都能映入眼帘。小版本参数兼容性CVE-2024-7348补丁参数：在16.4,15.8,14.13,13.16,12.20引入功能参数：在14.5,13.8,12.12,11.17,10.22引入推荐文章：<<MacBook使用虚拟机搭建kingbase数据库>>数据库创建的兼容性建议使用11.1912.1413.1014.715.2及以上小版本

正常执行语句DROPDATABASEifexistsmydb;CREATEDATABASEmydb;

cannotbeexecutedwithinapipelineSELECT1;DROPDATABASEifexistsmydb;SELECT1;CREATEDATABASEmydb;展望18：Oracle兼容性CREATEORREPLACEPROCEDUREprocedure1(INOUTp1TEXT)AS$$BEGINRAISENOTICE'ProcedureParameter:%',p1;END;$$LANGUAGEplpgsql;CALLprocedure1(p1:='CREATEPROCEDUREfunctionalitysupportedinPostgreSQL11!');CALLprocedure1(p1=>'CREATEPROCEDUREfunctionalitysupportedinPostgreSQL11!');游标参数支持=>语法CREATEORREPLACEPROCEDUREprocedure2(param1TEXT)AS$$DECLAREmy_cursorCURSOR(c_p1varchar)ISSELECT*FROMpg_classWHERErelname=param1;my_recRECORD;BEGINOPENmy_cursor(c_p1=>param1);FETCHmy_cursorintomy_rec;RAISENOTICE'filenode:%',my_rec.relfilenode;END;$$LANGUAGEplpgsql;CALLprocedure2(param1=>'tab');展望18：有序的UUID数据表的主键有两种基本的方法：使用sequence生成主键或使用UUID（universallyuniqueidentifier）生成主键。PostgreSQL18参考RFC9652标准将引入UUIDv7版本的特性，UUIDv7融合了时间和随机属性，同时提供唯一性和有序性。参照RFC9652标准新增uuidv7函数保证单个后端进程内单调有序递增扩展uuid_extract_timestamp函数支持UUIDv7版本对gen_random_uuid函数增加别名函数uuidv4，维护UUID版本名称的一致性功能特性postgres=#selectuuidv7();uuidv7------------------------------019453ff-d612-7dea-8eed-c3f6fba09a96(1row)postgres=#selectuuid_extract_version('019453ff-d612-7dea-8eed-c3f6fba09a96');uuid_extract_version-----------------7(1row)postgres=#selectuuid_extract_timestamp('019453ff-d612-7dea-8eed-c3f6fba09a96');uuid_extract_timestamp----------------------2025-01-1114:14:22.994+08(1row)展望18：DISTINCT关键字内部排序优化通过引入enable_distinct_reordering参数进行控制，默认为on。CREATETABLEdistinct_tbl(xint,yint);INSERTINTOdistinct_tblSELECTi%10,i%10FROMgenerate_series(1,1000)ASi;CREATEINDEXdistinct_tbl_x_y_idxONdistinct_tbl(x,y);ANALYZEdistinct_tbl;

SETenable_hashaggTOOFF;EXPLAIN(COSTSOFF)SELECTDISTINCTy,xFROMdistinct_tbl;QUERYPLAN------------------------------------------------------------------Unique->IndexOnlyScanusingdistinct_tbl_x_y_idxondistinct_tbl(2rows)EXPLAIN(COSTSOFF)SELECTDISTINCTy,xFROM(SELECT*FROMdistinct_tblORDERBYx)s;QUERYPLAN------------------------------------------------------------------------------Unique->IncrementalSortSortKey:s.x,s.yPresortedKey:s.x->SubqueryScanons->IndexOnlyScanusingdistinct_tbl_x_y_idxondistinct_tbl(6rows)展望18：使用唯一索引消除冗余GROUPBY列createtemptablet3(aint,bint,cint,primarykey(a,b)deferrable);createuniqueindext3_c_uidxont3(c);ALTERTABLEt3ALTERCOLUMNcSETNOTNULL;EXPLAIN(COSTSOFF)SELECTb,cFROMt3GROUPBYb,c;QUERYPLAN----------------------HashAggregate

GroupKey:c->SeqScanont3(3rows)EXPLAIN(COSTSOFF)SELECTa,b,cFROMt3GROUPBYa,b,c;QUERYPLAN----------------------HashAggregate

GroupKey:c->SeqScanont3(3rows)唯一索引列且非空可消除冗余列多个唯一索引时选用列数最少的索引以尽可能消除更多冗余列EXPLAIN(COSTSOFF)SELECTa,b,cFROMt3GROUPBYc,a,b;QUERYPLAN----------------------HashAggregate

GroupKey:c->SeqScanont3(3rows)展望18：权限查询统一接口函数pg_get_acl函数展望18：生成列支持不存储CREATETABLEtest(kmnumeric,nmnumericGENERATEDALWAYSAS(km*1.852)

STORED,nm2numericGENERATEDALWAYSAS(km*1.852)VIRTUAL);查询生成列存储属性selectattname,attgeneratedfrompg_attributewhereattrelid='test'::regclassandattname='nm2';attname|attgenerated---------+--------------nm|v(1row)修改表达式ALTERTABLEtestALTERCOLUMNnm2SETexpressionAS(km*2);不支持修改为普通列ALTERTABLEtestALTERCOLUMNnm2DROPexpression

;ERROR:ALTERTABLE/DROPEXPRESSIONisnotsupportedforvirtualgeneratedcolumns展望18：逻辑复制支持存储生成列createdatabaselogical_src;createdatabaselogical_dest;\clogical_srccreatetabletab1(kmnumeric,nmnumericGENERATEDALWAYSAS(km*1.852)STORED,nm2numericGENERATEDALWAYSAS(km*1.852));selectpg_create_logical_replication_slot('logical_slot1','pgoutput');CREATEPUBLICATIONpub1FORTABLEtab1;CREATEPUBLICATIONpub2FORTABLEtab1(km,nm);CREATEPUBLICATIONpub3FORTABLEtab1WITH(publish_generated_columns=stored);\clogical_destcreatetabletab1(kmnumeric,nmnumericGENERATEDALWAYSAS(km*1.852)STORED,nm2numericGENERATEDALWAYSAS(km*1.852)

);createsubscriptionsub1connection'hostaddr=port=1800user=postgresdbname=logical_src'publicationpub1with(create_slot='false',slot_name='logical_slot1');展望18：基于时态的主外键约束(范围类型)背景SQL标准针对periods类型(表示时间范围)提出了temporalforeignkey特性AddPERIODclausetoforeignkeyconstraintdefinitionsThisissupportedforrangeandmultirangetypes.Temporalforeignkeyscheckforrangecontainmentinsteadofequality.配套语法主表：PRIMARYKEY(id,valid_atWITHOUTOVERLAPS)：相当于排他约束外表：FOREIGNKEY(id,PERIODvalid_at)REFERENCEStoo(id,PERIODvalid_at)限制ReferenceactionsON{UPDATE,DELETE}{CASCADE,SETNULL,SETDEFAULT}arenotsupportedyet.展望18：基于时态的主外键示例createextensionbtree_gist;CREATETABLEaddresses(idint8generatedBYDEFAULTASIDENTITY,valid_rangetstzrangeNOTNULLDEFAULTtstzrange(now(),'infinity','[)')