2025PG版本兼容性案例

一些兼容性案例及PG18新特性前瞻2025我的新特性分享(反吐槽：PG沒(méi)有擠牙膏)PG15新特性精編版

/doc/132281并行distinct、fdw并行提交、全備lz4和zstd壓縮、basic_archive歸檔、視圖基表權(quán)限檢測(cè)、參數(shù)可授權(quán)、json日志、startup日志、統(tǒng)計(jì)信息內(nèi)存化、邏輯復(fù)制行列過(guò)濾、MERGEINTO(帶and)、NULLsDistinct、FKactionsetnull(b,c)、\dconfig、遷移一致的DB、刷新OS排序、walinspectPG16新特性匯編

/doc/143240并行HashFull/RightJoin、string_agg并行聚合、大事務(wù)并行回放、RingBuffer、reservedconn、createroleselfgrant(家族世襲)、pg_stat_io、last_idx_scan、forcegenericplan、libpq負(fù)載均衡、replicafull標(biāo)識(shí)(非唯一索引)、數(shù)字的多進(jìn)制轉(zhuǎn)換及下劃線自定義分組、pg_input_is_valid、邏輯備份(lz4和zstd壓縮)、copyfromdefault、fdw異步中止及抽樣統(tǒng)計(jì)信息PG17RC1新特性體驗(yàn)

/doc/143246in查詢(xún)加速、brin并行創(chuàng)建、reindexdb并行、基于ART自適應(yīng)vacuum加速、io_combine_limit多塊讀pg_maintain、可關(guān)閉的altersystem、event_triggers及登錄事件、seralize選項(xiàng)、%TYPE類(lèi)型增量備份、dump/restore對(duì)象文件過(guò)濾、pg_createsubscriber、內(nèi)置的localeproviderPG18展望：虛擬列邏輯復(fù)制、UUID唯一且有序、ACL函數(shù)、時(shí)態(tài)對(duì)象PG15的兼容性變化public模式create權(quán)限被回收(保持向下兼容可修改template1)pg_start_backup重命名為pg_backup_startpg_stop_backup重命名為pg_backup_stop廢棄排他性備份模式(可能會(huì)導(dǎo)致重啟失敗)免密連接優(yōu)先從HOME環(huán)境變量($HOME/.pgpass)讀取密碼文件廢棄stats_temp_directory參數(shù)(受統(tǒng)計(jì)信息內(nèi)存化影響)自定義變量前綴不允許與擴(kuò)展插件名相同PG16的兼容性變化postgres進(jìn)程統(tǒng)一：徹底抹除postmaster信息備庫(kù)提升為主庫(kù)廢棄promote_trigger_file文件觸發(fā)方式預(yù)防流復(fù)制沖突的vacuum_defer_cleanup_age參數(shù)被廢棄服務(wù)端只讀變量lc_collate和lc_ctype參數(shù)被刪除：新版本可DB級(jí)設(shè)置ICU提示優(yōu)化器考慮并行：force_parallel_mode重命名為debug_parallel_querylogical_replication_mode重命名為debug_logical_replication_streaming歸檔參數(shù)archive_library與archive_command互斥：不能同時(shí)使用非ASCII字符轉(zhuǎn)換規(guī)則變更：application_name/cluster_name顯示問(wèn)號(hào)變HEX碼PG17的兼容性變化pg_stat_bgwriter視圖分裂出pg_stat_checkpointerpg_stat_progress_vacuum視圖新增索引處理進(jìn)度字段pg_stat_statements插件I/O讀寫(xiě)時(shí)間拆分出shared和local關(guān)閉事務(wù)快照太舊的功能(廢棄old_snapshot_threshold參數(shù))關(guān)閉db級(jí)的用戶(hù)功能(廢棄db_user_namespace參數(shù))刪除trace_recovery_messages參數(shù)pg_stat_slru視圖重命名SLRU名稱(chēng)刪除了adminpack插件SQL綁定變量：JDBC驅(qū)動(dòng)有硬限制Triedtosendanout-of-rangeintegerasa2-bytevalue:32768PreparedStatementcanhaveatmost65,535parameters.JDBC42.4.0之前：JDBC42.4.0開(kāi)始：IN傳參IN($1,$2,$3,$4,$5)ANY數(shù)組傳參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的優(yōu)先級(jí)SELECTcasewhent.a||''isnotnullthen1else2endresFROM(SELECT'a'::varcharASa)t;ERROR:argumentofCASE/WHENmustbetypeboolean,nottypetextPostgreSQL9.5之前，語(yǔ)句執(zhí)行的邏輯是：t.a||(''isnotnull)PostgreSQL9.5開(kāi)始，語(yǔ)句執(zhí)行的邏輯是：(t.a||'')isnotnull9.5版本開(kāi)始該語(yǔ)句可以正常執(zhí)行，9.5之前會(huì)報(bào)錯(cuò)如下：10和11的兩個(gè)兼容性案例extra_float_digits=3CREATETABLEt1(avarchar);

ps.setObject(1,newDouble(48));ps.setObject(1,newDouble(48.1));ps.setObject(1,newDouble(48.9));連接建立不規(guī)范會(huì)引起LOG膨脹可使用nc對(duì)比測(cè)試11和12nc-zv235432Ncat:Version7.50(/ncat)Ncat:Connectedto23:5432.Ncat:0bytessent,0bytesreceivedin0.01seconds.不遵守標(biāo)準(zhǔn)消息格式的客戶(hù)端會(huì)大量記錄如下日志incompletestartuppacket解決方法升級(jí)到PostgreSQL12日志文件循環(huán)記錄使用工具定期過(guò)濾處理PostgreSQL12releasenoteunknown與any家族之宿敵化解Yourinstallationcontainsuser-definedobjectsthatrefertointernalpolymorphicfunctionswithargumentsoftype"anyarray"or"anyelement".Theseuser-definedobjectsmustbedroppedbeforeupgradingandrestoredafterwards,changingthemtorefertothenewcorrespondingfunctionswithargumentsoftype"anycompatiblearray"and"anycompatible'.PostgreSQL14多態(tài)函數(shù)參數(shù)類(lèi)型變化-anyelement-->anycompatible-anyarray-->為anycompatiblearrayarray_appendarray_prependarray_catarray_positionarray_positionsarray_removearray_replacewidth_bucketleadlagCREATEFUNCTIONfoo(anyelement,anyelement)RETURNSboolLANGUAGESQLAS'select$1=$2';SELECTfoo('a','b');ERROR:couldnotdeterminepolymorphictypebecauseinputhastypeunknownOR缺失空格PostgreSQL15開(kāi)始會(huì)報(bào)錯(cuò)ERROR:trailingjunkafternumericliteralatornear"1010R"CREATETABLEt(idint);SELECT*FROMtWHERE(id=101ORid=102);分區(qū)表DDL引起pg_dump等待會(huì)話1開(kāi)啟事務(wù)，查詢(xún)分區(qū)表test_listbegin;select*fromtest_listlimit1;truncatetest_list;pg_dump-p5431-ta-s會(huì)話2執(zhí)行truncate分區(qū)表test_list會(huì)話3導(dǎo)出a表的結(jié)構(gòu)PostgreSQL15之前，普通表dump導(dǎo)出時(shí)會(huì)受分區(qū)表DDL操作而被阻塞，需要加超時(shí)參數(shù)分區(qū)表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語(yǔ)法的%需雙寫(xiě)PostgreSQL16配置歸檔策略：每天自動(dòng)生成一個(gè)目錄存放歸檔日志文件標(biāo)準(zhǔn)寫(xiě)法：雙寫(xiě)百分號(hào)通過(guò)游標(biāo)獲取數(shù)據(jù)的兼容性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發(fā)布注記與幫助鏈接應(yīng)對(duì)策略點(diǎn)擊發(fā)布注記后面的§符號(hào)，即可打開(kāi)git鏈接如下：包括開(kāi)發(fā)者的特性討論及完整提交過(guò)程。9月26號(hào)PG17正式發(fā)布時(shí)，ReleaseNotes里增加了251處git鏈接標(biāo)記。PG全球開(kāi)發(fā)組一直使用git來(lái)跟蹤項(xiàng)目特性的提交，從17開(kāi)始每條發(fā)布注記后面都有相應(yīng)的單條或多條git提交鏈接。注記的編寫(xiě)并不是一件容易的事，即要兼顧不同提交者的聲音，又要對(duì)所有的變化保持簡(jiǎn)明扼要及張弛有度。對(duì)于數(shù)據(jù)庫(kù)特性深度愛(ài)好者，注記內(nèi)容的詳實(shí)度級(jí)別是不夠的，而這些新增的鏈接對(duì)他們?nèi)绔@至寶。通過(guò)鏈接他們不僅可以看清特性的實(shí)現(xiàn)源碼還能觀察到一些注釋信息，更多注記里沒(méi)有的細(xì)節(jié)都能映入眼簾。小版本參數(shù)兼容性CVE-2024-7348補(bǔ)丁參數(shù)：在16.4,15.8,14.13,13.16,12.20引入功能參數(shù)：在14.5,13.8,12.12,11.17,10.22引入推薦文章：<<MacBook使用虛擬機(jī)搭建kingbase數(shù)據(jù)庫(kù)>>數(shù)據(jù)庫(kù)創(chuàng)建的兼容性建議使用11.1912.1413.1014.715.2及以上小版本

正常執(zhí)行語(yǔ)句DROPDATABASEifexistsmydb;CREATEDATABASEmydb;

cannotbeexecutedwithinapipelineSELECT1;DROPDATABASEifexistsmydb;SELECT1;CREATEDATABASEmydb;展望18：Oracle兼容性CREATEORREPLACEPROCEDUREprocedure1(INOUTp1TEXT)AS$$BEGINRAISENOTICE'ProcedureParameter:%',p1;END;$$LANGUAGEplpgsql;CALLprocedure1(p1:='CREATEPROCEDUREfunctionalitysupportedinPostgreSQL11!');CALLprocedure1(p1=>'CREATEPROCEDUREfunctionalitysupportedinPostgreSQL11!');游標(biāo)參數(shù)支持=>語(yǔ)法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數(shù)據(jù)表的主鍵有兩種基本的方法：使用sequence生成主鍵或使用UUID（universallyuniqueidentifier）生成主鍵。PostgreSQL18參考RFC9652標(biāo)準(zhǔn)將引入U(xiǎn)UIDv7版本的特性，UUIDv7融合了時(shí)間和隨機(jī)屬性，同時(shí)提供唯一性和有序性。參照RFC9652標(biāo)準(zhǔn)新增uuidv7函數(shù)保證單個(gè)后端進(jìn)程內(nèi)單調(diào)有序遞增擴(kuò)展uuid_extract_timestamp函數(shù)支持UUIDv7版本對(duì)gen_random_uuid函數(shù)增加別名函數(shù)uuidv4，維護(hù)UUID版本名稱(chēng)的一致性功能特性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關(guān)鍵字內(nèi)部排序優(yōu)化通過(guò)引入enable_distinct_reordering參數(shù)進(jìn)行控制，默認(rèn)為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)唯一索引列且非空可消除冗余列多個(gè)唯一索引時(shí)選用列數(shù)最少的索引以盡可能消除更多冗余列EXPLAIN(COSTSOFF)SELECTa,b,cFROMt3GROUPBYc,a,b;QUERYPLAN----------------------HashAggregate

GroupKey:c->SeqScanont3(3rows)展望18：權(quán)限查詢(xún)統(tǒng)一接口函數(shù)pg_get_acl函數(shù)展望18：生成列支持不存儲(chǔ)CREATETABLEtest(kmnumeric,nmnumericGENERATEDALWAYSAS(km*1.852)

STORED,nm2numericGENERATEDALWAYSAS(km*1.852)VIRTUAL);查詢(xún)生成列存儲(chǔ)屬性selectattname,attgeneratedfrompg_attributewhereattrelid='test'::regclassandattname='nm2';attname|attgenerated---------+--------------nm|v(1row)修改表達(dá)式ALTERTABLEtestALTERCOLUMNnm2SETexpressionAS(km*2);不支持修改為普通列ALTERTABLEtestALTERCOLUMNnm2DROPexpression

;ERROR:ALTERTABLE/DROPEXPRESSIONisnotsupportedforvirtualgeneratedcolumns展望18：邏輯復(fù)制支持存儲(chǔ)生成列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：基于時(shí)態(tài)的主外鍵約束(范圍類(lèi)型)背景SQL標(biāo)準(zhǔn)針對(duì)periods類(lèi)型(表示時(shí)間范圍)提出了temporalforeignkey特性AddPERIODclausetoforeignkeyconstraintdefinitionsThisissupportedforrangeandmultirangetypes.Temporalforeignkeyscheckforrangecontainmentinsteadofequality.配套語(yǔ)法主表：PRIMARYKEY(id,valid_atWITHOUTOVERLAPS)：相當(dāng)于排他約束外表：FOREIGNKEY(id,PERIODvalid_at)REFERENCEStoo(id,PERIODvalid_at)限制ReferenceactionsON{UPDATE,DELETE}{CASCADE,SETNULL,SETDEFAULT}arenotsupportedyet.展望18：基于時(shí)態(tài)的主外鍵示例createextensionbtree_gist;CREATETABLEaddresses(idint8generatedBYDEFAULTASIDENTITY,valid_rangetstzrangeNOTNULLDEFAULTtstzrange(now(),'infinity','[)')