本篇内容主要讲解“怎么使用PostgreSQL12的存储接口”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么使用PostgreSQL12的存储接口”吧!

PostgreSQL 12 引入了可插入表存储接口(Pluggable storage for tables)，近日Michael Paquier提交了一份Blackhole Table Access Method的模板方法.
之所以称为黑洞,是因为不管你在数据表上执行什么操作,都会看不到任何的反应,似乎被”黑洞”吸进去了,下面我们先睹为快.

在Github上下载源码,编译安装.

[root@localhostblackhole_am]#makegcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-g3-gdwarf-2-fPIC-I.-I./-I/appdb/xdb/pg12beta1/include/postgresql/server-I/appdb/xdb/pg12beta1/include/postgresql/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-oblackhole_am.oblackhole_am.c-MMD-MP-MF.deps/blackhole_am.Poblackhole_am.c:371:1:warning:‘blackhole_relation_needs_toast_table’definedbutnotused[-Wunused-function]blackhole_relation_needs_toast_table(Relationrel)^gcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-g3-gdwarf-2-fPICblackhole_am.o-L/appdb/xdb/pg12beta1/lib-Wl,--as-needed-Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags-shared-oblackhole_am.so[root@localhostblackhole_am]#makeinstall/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/lib/postgresql'/usr/bin/install-c-mXXX.//blackhole_am.control'/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-mXXX.//blackhole_am--1.0.sql'/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-m755blackhole_am.so'/appdb/xdb/pg12beta1/lib/postgresql/'[root@localhostblackhole_am]#

注:如出现错误,注释相关代码(.relation_needs_toast_table = blackhole_relation_needs_toast_table)即可.

gcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-g3-gdwarf-2-fPIC-I.-I./-I/appdb/xdb/pg12beta1/include/postgresql/server-I/appdb/xdb/pg12beta1/include/postgresql/internal-D_GNU_SOURCE-I/usr/include/libxml2-c-oblackhole_am.oblackhole_am.c-MMD-MP-MF.deps/blackhole_am.Poblackhole_am.c:487:2:error:unknownfield‘relation_needs_toast_table’specifiedininitializer.relation_needs_toast_table=blackhole_relation_needs_toast_table,^blackhole_am.c:487:2:warning:initializationfromincompatiblepointertype[enabledbydefault]blackhole_am.c:487:2:warning:(nearinitializationfor‘blackhole_methods.relation_estimate_size’)[enabledbydefault]make:***[blackhole_am.o]Error1

测试体验

[pg12@localhost~]$psql-dtestdbpsql(12beta1)Type"help"forhelp.testdb=#CREATEEXTENSIONblackhole_am;CREATEEXTENSIONtestdb=#\dx+blackhole_amObjectsinextension"blackhole_am"Objectdescription-----------------------------------------accessmethodblackhole_amfunctionblackhole_am_handler(internal)(2rows)testdb=#CREATETABLEt_blackhole(idint)USINGblackhole_am;CREATETABLEtestdb=#INSERTINTOt_blackholeselectgenerate_series(1,100000);INSERT0100000

提示插入了100000行,执行查询

testdb=#explain(verbose,analyze)SELECT*FROMt_blackhole;QUERYPLAN------------------------------------------------------------------------------------------------------------SeqScanonpublic.t_blackhole(cost=0.00..0.00rows=1width=4)(actualtime=0.003..0.003rows=0loops=1)Output:idPlanningTime:1.166msExecutionTime:0.084ms(4rows)

实际数据并没有插入,都被”黑洞”吸进去了.
尝试创建索引,结果Coredump了.

testdb=#createindexidx_t_blackhole_idont_blackhole(id);psql:serverclosedtheconnectionunexpectedlyThisprobablymeanstheserverterminatedabnormallybeforeorwhileprocessingtherequest.Theconnectiontotheserverwaslost.Attemptingreset:Failed.!>!>\q二、源码解读

blackhole_am与先前我们分析过的做法基本吻合,blackhole_am自定义了一系列的访问方法blackhole_XXX,下面是实现源码:

/*-------------------------------------------------------------------------**blackhole_am.c*blackholetableaccessmethodcode**PortionsCopyright(c)1996-2019,PostgreSQLGlobalDevelopmentGroup*PortionsCopyright(c)1994,RegentsoftheUniversityofCalifornia***IDENTIFICATION*blackhole_am/blackhole_am.c***NOTES*Thisfileintroducesthetableaccessmethodblackhole,whichcan*beusedasatemplateforothertableaccessmethods,andguarantees*thatanydatainsertedintoitgetssenttothevoid.**-------------------------------------------------------------------------*/#include"postgres.h"#include<math.h>#include"miscadmin.h"#include"access/tableam.h"#include"access/heapam.h"#include"access/amapi.h"#include"catalog/index.h"#include"commands/vacuum.h"#include"executor/tuptable.h"PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(blackhole_am_handler);/*Basestructuresforscans*/typedefstructBlackholeScanDescData{TableScanDescDatars_base;/*AMindependentpartofthedescriptor*//*AddmorefieldshereasneededbytheAM.*/}BlackholeScanDescData;typedefstructBlackholeScanDescData*BlackholeScanDesc;staticconstTableAmRoutineblackhole_methods;/*------------------------------------------------------------------------*SlotrelatedcallbacksforblackholeAM*------------------------------------------------------------------------*/staticconstTupleTableSlotOps*blackhole_slot_callbacks(Relationrelation){/**Hereyouwouldmostlikelywanttoinventyourownsetof*slotcallbacksforyourAM.*/return&TTSOpsMinimalTuple;}/*------------------------------------------------------------------------*TableScanCallbacksforblackholeAM*------------------------------------------------------------------------*/staticTableScanDescblackhole_scan_begin(Relationrelation,Snapshotsnapshot,intnkeys,ScanKeykey,ParallelTableScanDescparallel_scan,uint32flags){BlackholeScanDescscan;scan=(BlackholeScanDesc)palloc(sizeof(BlackholeScanDescData));scan->rs_base.rs_rd=relation;scan->rs_base.rs_snapshot=snapshot;scan->rs_base.rs_nkeys=nkeys;scan->rs_base.rs_flags=flags;scan->rs_base.rs_parallel=parallel_scan;return(TableScanDesc)scan;}staticvoidblackhole_scan_end(TableScanDescsscan){BlackholeScanDescscan=(BlackholeScanDesc)sscan;pfree(scan);}staticvoidblackhole_scan_rescan(TableScanDescsscan,ScanKeykey,boolset_params,boolallow_strat,boolallow_sync,boolallow_pagemode){/*nothingtodo*/}staticboolblackhole_scan_getnextslot(TableScanDescsscan,ScanDirectiondirection,TupleTableSlot*slot){/*nothingtodo*/returnfalse;}/*------------------------------------------------------------------------*IndexScanCallbacksforblackholeAM*------------------------------------------------------------------------*/staticIndexFetchTableData*blackhole_index_fetch_begin(Relationrel){returnNULL;}staticvoidblackhole_index_fetch_reset(IndexFetchTableData*scan){/*nothingtodohere*/}staticvoidblackhole_index_fetch_end(IndexFetchTableData*scan){/*nothingtodohere*/}staticboolblackhole_index_fetch_tuple(structIndexFetchTableData*scan,ItemPointertid,Snapshotsnapshot,TupleTableSlot*slot,bool*call_again,bool*all_dead){/*thereisnodata*/return0;}/*------------------------------------------------------------------------*Callbacksfornon-modifyingoperationsonindividualtuplesfor*blackholeAM.*------------------------------------------------------------------------*/staticboolblackhole_fetch_row_version(Relationrelation,ItemPointertid,Snapshotsnapshot,TupleTableSlot*slot){/*nothingtodo*/returnfalse;}staticvoidblackhole_get_latest_tid(TableScanDescsscan,ItemPointertid){/*nothingtodo*/}staticboolblackhole_tuple_tid_valid(TableScanDescscan,ItemPointertid){returnfalse;}staticboolblackhole_tuple_satisfies_snapshot(Relationrel,TupleTableSlot*slot,Snapshotsnapshot){returnfalse;}staticTransactionIdblackhole_compute_xid_horizon_for_tuples(Relationrel,ItemPointerData*tids,intnitems){returnInvalidTransactionId;}/*----------------------------------------------------------------------------*FunctionsformanipulationsofphysicaltuplesforblackholeAM.*----------------------------------------------------------------------------*/staticvoidblackhole_tuple_insert(Relationrelation,TupleTableSlot*slot,CommandIdcid,intoptions,BulkInsertStatebistate){/*nothingtodo*/}staticvoidblackhole_tuple_insert_speculative(Relationrelation,TupleTableSlot*slot,CommandIdcid,intoptions,BulkInsertStatebistate,uint32specToken){/*nothingtodo*/}staticvoidblackhole_tuple_complete_speculative(Relationrelation,TupleTableSlot*slot,uint32spekToken,boolsucceeded){/*nothingtodo*/}staticvoidblackhole_multi_insert(Relationrelation,TupleTableSlot**slots,intntuples,CommandIdcid,intoptions,BulkInsertStatebistate){/*nothingtodo*/}staticTM_Resultblackhole_tuple_delete(Relationrelation,ItemPointertid,CommandIdcid,Snapshotsnapshot,Snapshotcrosscheck,boolwait,TM_FailureData*tmfd,boolchangingPart){/*nothingtodo,soitisalwaysOK*/returnTM_Ok;}staticTM_Resultblackhole_tuple_update(Relationrelation,ItemPointerotid,TupleTableSlot*slot,CommandIdcid,Snapshotsnapshot,Snapshotcrosscheck,boolwait,TM_FailureData*tmfd,LockTupleMode*lockmode,bool*update_indexes){/*nothingtodo,soitisalwaysOK*/returnTM_Ok;}staticTM_Resultblackhole_tuple_lock(Relationrelation,ItemPointertid,Snapshotsnapshot,TupleTableSlot*slot,CommandIdcid,LockTupleModemode,LockWaitPolicywait_policy,uint8flags,TM_FailureData*tmfd){/*nothingtodo,soitisalwaysOK*/returnTM_Ok;}staticvoidblackhole_finish_bulk_insert(Relationrelation,intoptions){/*nothingtodo*/}/*------------------------------------------------------------------------*DDLrelatedcallbacksforblackholeAM.*------------------------------------------------------------------------*/staticvoidblackhole_relation_set_new_filenode(Relationrel,constRelFileNode*newrnode,charpersistence,TransactionId*freezeXid,MultiXactId*minmulti){/*nothingtodo*/}staticvoidblackhole_relation_nontransactional_truncate(Relationrel){/*nothingtodo*/}staticvoidblackhole_copy_data(Relationrel,constRelFileNode*newrnode){/*thereisnodata*/}staticvoidblackhole_copy_for_cluster(RelationOldTable,RelationNewTable,RelationOldIndex,booluse_sort,TransactionIdOldestXmin,TransactionId*xid_cutoff,MultiXactId*multi_cutoff,double*num_tuples,double*tups_vacuumed,double*tups_recently_dead){/*nodata,sonothingtodo*/}staticvoidblackhole_vacuum(Relationonerel,VacuumParams*params,BufferAccessStrategybstrategy){/*nodata,sonothingtodo*/}staticboolblackhole_scan_analyze_next_block(TableScanDescscan,BlockNumberblockno,BufferAccessStrategybstrategy){/*nodata,sonopointtoanalyzenextblock*/returnfalse;}staticboolblackhole_scan_analyze_next_tuple(TableScanDescscan,TransactionIdOldestXmin,double*liverows,double*deadrows,TupleTableSlot*slot){/*nodata,sonopointtoanalyzenexttuple*/returnfalse;}staticdoubleblackhole_index_build_range_scan(RelationtableRelation,RelationindexRelation,IndexInfo*indexInfo,boolallow_sync,boolanyvisible,boolprogress,BlockNumberstart_blockno,BlockNumbernumblocks,IndexBuildCallbackcallback,void*callback_state,TableScanDescscan){/*nodata,sonotuples*/return0;}staticvoidblackhole_index_validate_scan(RelationtableRelation,RelationindexRelation,IndexInfo*indexInfo,Snapshotsnapshot,ValidateIndexState*state){/*nothingtodo*/}/*------------------------------------------------------------------------*MiscellaneouscallbacksfortheblackholeAM*------------------------------------------------------------------------*/staticuint64blackhole_relation_size(Relationrel,ForkNumberforkNumber){/*thereisnothing*/return0;}/**ChecktoseewhetherthetableneedsaTOASTtable.*/staticboolblackhole_relation_needs_toast_table(Relationrel){/*nodata,sonotoasttableneeded*/returnfalse;}/*------------------------------------------------------------------------*PlannerrelatedcallbacksfortheblackholeAM*------------------------------------------------------------------------*/staticvoidblackhole_estimate_rel_size(Relationrel,int32*attr_widths,BlockNumber*pages,double*tuples,double*allvisfrac){/*nodataavailable*/*attr_widths=0;*tuples=0;*allvisfrac=0;*pages=0;}/*------------------------------------------------------------------------*ExecutorrelatedcallbacksfortheblackholeAM*------------------------------------------------------------------------*/staticboolblackhole_scan_bitmap_next_block(TableScanDescscan,TBMIterateResult*tbmres){/*nodata,sonopointtoscannextblock*/returnfalse;}staticboolblackhole_scan_bitmap_next_tuple(TableScanDescscan,TBMIterateResult*tbmres,TupleTableSlot*slot){/*nodata,sonopointtoscannexttuple*/returnfalse;}staticboolblackhole_scan_sample_next_block(TableScanDescscan,SampleScanState*scanstate){/*nodata,sonopointtoscannextblockforsampling*/returnfalse;}staticboolblackhole_scan_sample_next_tuple(TableScanDescscan,SampleScanState*scanstate,TupleTableSlot*slot){/*nodata,sonopointtoscannexttupleforsampling*/returnfalse;}/*------------------------------------------------------------------------*Definitionoftheblackholetableaccessmethod.*------------------------------------------------------------------------*/staticconstTableAmRoutineblackhole_methods={.type=T_TableAmRoutine,.slot_callbacks=blackhole_slot_callbacks,.scan_begin=blackhole_scan_begin,.scan_end=blackhole_scan_end,.scan_rescan=blackhole_scan_rescan,.scan_getnextslot=blackhole_scan_getnextslot,/*thesearecommonhelperfunctions*/.parallelscan_estimate=table_block_parallelscan_estimate,.parallelscan_initialize=table_block_parallelscan_initialize,.parallelscan_reinitialize=table_block_parallelscan_reinitialize,.index_fetch_begin=blackhole_index_fetch_begin,.index_fetch_reset=blackhole_index_fetch_reset,.index_fetch_end=blackhole_index_fetch_end,.index_fetch_tuple=blackhole_index_fetch_tuple,.tuple_insert=blackhole_tuple_insert,.tuple_insert_speculative=blackhole_tuple_insert_speculative,.tuple_complete_speculative=blackhole_tuple_complete_speculative,.multi_insert=blackhole_multi_insert,.tuple_delete=blackhole_tuple_delete,.tuple_update=blackhole_tuple_update,.tuple_lock=blackhole_tuple_lock,.finish_bulk_insert=blackhole_finish_bulk_insert,.tuple_fetch_row_version=blackhole_fetch_row_version,.tuple_get_latest_tid=blackhole_get_latest_tid,.tuple_tid_valid=blackhole_tuple_tid_valid,.tuple_satisfies_snapshot=blackhole_tuple_satisfies_snapshot,.compute_xid_horizon_for_tuples=blackhole_compute_xid_horizon_for_tuples,.relation_set_new_filenode=blackhole_relation_set_new_filenode,.relation_nontransactional_truncate=blackhole_relation_nontransactional_truncate,.relation_copy_data=blackhole_copy_data,.relation_copy_for_cluster=blackhole_copy_for_cluster,.relation_vacuum=blackhole_vacuum,.scan_analyze_next_block=blackhole_scan_analyze_next_block,.scan_analyze_next_tuple=blackhole_scan_analyze_next_tuple,.index_build_range_scan=blackhole_index_build_range_scan,.index_validate_scan=blackhole_index_validate_scan,.relation_size=blackhole_relation_size,.relation_needs_toast_table=blackhole_relation_needs_toast_table,.relation_estimate_size=blackhole_estimate_rel_size,.scan_bitmap_next_block=blackhole_scan_bitmap_next_block,.scan_bitmap_next_tuple=blackhole_scan_bitmap_next_tuple,.scan_sample_next_block=blackhole_scan_sample_next_block,.scan_sample_next_tuple=blackhole_scan_sample_next_tuple};Datumblackhole_am_handler(PG_FUNCTION_ARGS){PG_RETURN_POINTER(&blackhole_methods);}

通过SQL创建相关对象,包括FUNCTION blackhole_am_handler和ACCESS METHOD blackhole_am.

/*blackhole_am/blackhole_am--1.0.sql*/--complainifscriptissourcedinpsql,ratherthanviaCREATEEXTENSION\echoUse"CREATEEXTENSIONblackhole_am"toloadthisfile.\quitCREATEFUNCTIONblackhole_am_handler(internal)RETURNStable_am_handlerAS'MODULE_PATHNAME'LANGUAGEC;--AccessmethodCREATEACCESSMETHODblackhole_amTYPETABLEHANDLERblackhole_am_handler;COMMENTONACCESSMETHODblackhole_amIS'templatetableAMeatingalldata';

PG 12的Pluggable storage for tables为数据表的访问打开了另外一扇窗,使用者完全可以根据自身需求自行实现自己的存取方式.

到此，相信大家对“怎么使用PostgreSQL12的存储接口”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！