怎么使用PostgreSQL12的存储接口

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

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

一、安装体验

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

[root@localhost blackhole_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 -o blackhole_am.o blackhole_am.c -MMD -MP -MF .deps/blackhole_am.Poblackhole_am.c:371:1: warning: 'blackhole_relation_needs_toast_table' defined but not used [-Wunused-function] blackhole_relation_needs_toast_table(Relation rel) ^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 blackhole_am.o -L/appdb/xdb/pg12beta1/lib   -Wl,--as-needed -Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags  -shared -o blackhole_am.so[root@localhost blackhole_am]# make install/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 -m XXX .//blackhole_am.control '/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install -c -m XXX .//blackhole_am--1.0.sql  '/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install -c -m 755  blackhole_am.so '/appdb/xdb/pg12beta1/lib/postgresql/'[root@localhost blackhole_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 -o blackhole_am.o blackhole_am.c -MMD -MP -MF .deps/blackhole_am.Poblackhole_am.c:487:2: error: unknown field 'relation_needs_toast_table' specified in initializer  .relation_needs_toast_table = blackhole_relation_needs_toast_table,  ^blackhole_am.c:487:2: warning: initialization from incompatible pointer type [enabled by default]blackhole_am.c:487:2: warning: (near initialization for 'blackhole_methods.relation_estimate_size') [enabled by default]make: *** [blackhole_am.o] Error 1

测试体验

[pg12@localhost ~]$ psql -d testdbpsql (12beta1)Type "help" for help.testdb=# CREATE EXTENSION blackhole_am;CREATE EXTENSIONtestdb=# \dx+ blackhole_am   Objects in extension "blackhole_am"           Object description            ----------------------------------------- access method blackhole_am function blackhole_am_handler(internal)(2 rows)testdb=# CREATE TABLE t_blackhole (id int) USING blackhole_am;CREATE TABLEtestdb=# INSERT INTO t_blackhole select generate_series(1,100000);INSERT 0 100000

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

testdb=# explain (verbose,analyze) SELECT * FROM t_blackhole;                                                 QUERY PLAN                                                 ------------------------------------------------------------------------------------------------------------ Seq Scan on public.t_blackhole  (cost=0.00..0.00 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)   Output: id Planning Time: 1.166 ms Execution Time: 0.084 ms(4 rows)

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

testdb=# create index idx_t_blackhole_id on t_blackhole(id);psql: server closed the connection unexpectedly    This probably means the server terminated abnormally    before or while processing the request.The connection to the server was lost. Attempting reset: Failed.!> !> \q

二、源码解读

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

/*------------------------------------------------------------------------- * * blackhole_am.c *    blackhole table access method code * * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * * * IDENTIFICATION *    blackhole_am/blackhole_am.c * * * NOTES *    This file introduces the table access method blackhole, which can *    be used as a template for other table access methods, and guarantees *    that any data inserted into it gets sent to the void. * *------------------------------------------------------------------------- */#include "postgres.h"#include #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);/* Base structures for scans */typedef struct BlackholeScanDescData{    TableScanDescData rs_base;  /* AM independent part of the descriptor */    /* Add more fields here as needed by the AM. */} BlackholeScanDescData;typedef struct BlackholeScanDescData *BlackholeScanDesc;static const TableAmRoutine blackhole_methods;/* ------------------------------------------------------------------------ * Slot related callbacks for blackhole AM * ------------------------------------------------------------------------ */static const TupleTableSlotOps *blackhole_slot_callbacks(Relation relation){    /*     * Here you would most likely want to invent your own set of     * slot callbacks for your AM.     */    return &TTSOpsMinimalTuple;}/* ------------------------------------------------------------------------ * Table Scan Callbacks for blackhole AM * ------------------------------------------------------------------------ */static TableScanDescblackhole_scan_begin(Relation relation, Snapshot snapshot,                     int nkeys, ScanKey key,                     ParallelTableScanDesc parallel_scan,                     uint32 flags){    BlackholeScanDesc   scan;    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;                                                                                                                                           }static voidblackhole_scan_end(TableScanDesc sscan){    BlackholeScanDesc scan = (BlackholeScanDesc) sscan;    pfree(scan);}static voidblackhole_scan_rescan(TableScanDesc sscan, ScanKey key, bool set_params,                      bool allow_strat, bool allow_sync, bool allow_pagemode){    /* nothing to do */}static boolblackhole_scan_getnextslot(TableScanDesc sscan, ScanDirection direction,                           TupleTableSlot *slot){    /* nothing to do */    return false;}/* ------------------------------------------------------------------------ * Index Scan Callbacks for blackhole AM * ------------------------------------------------------------------------ */static IndexFetchTableData *blackhole_index_fetch_begin(Relation rel){    return NULL;}static voidblackhole_index_fetch_reset(IndexFetchTableData *scan){    /* nothing to do here */}static voidblackhole_index_fetch_end(IndexFetchTableData *scan){    /* nothing to do here */}static boolblackhole_index_fetch_tuple(struct IndexFetchTableData *scan,                            ItemPointer tid,                            Snapshot snapshot,                            TupleTableSlot *slot,                            bool *call_again, bool *all_dead){    /* there is no data */    return 0;}/* ------------------------------------------------------------------------ * Callbacks for non-modifying operations on individual tuples for * blackhole AM. * ------------------------------------------------------------------------ */static boolblackhole_fetch_row_version(Relation relation,                            ItemPointer tid,                            Snapshot snapshot,                            TupleTableSlot *slot){    /* nothing to do */    return false;}static voidblackhole_get_latest_tid(TableScanDesc sscan,                         ItemPointer tid){    /* nothing to do */}static boolblackhole_tuple_tid_valid(TableScanDesc scan, ItemPointer tid){    return false;}static boolblackhole_tuple_satisfies_snapshot(Relation rel, TupleTableSlot *slot,                                  Snapshot snapshot){    return false;}static TransactionIdblackhole_compute_xid_horizon_for_tuples(Relation rel,                                         ItemPointerData *tids,                                         int nitems){    return InvalidTransactionId;}/* ---------------------------------------------------------------------------- *  Functions for manipulations of physical tuples for blackhole AM. * ---------------------------------------------------------------------------- */static voidblackhole_tuple_insert(Relation relation, TupleTableSlot *slot,                       CommandId cid, int options, BulkInsertState bistate){    /* nothing to do */}static voidblackhole_tuple_insert_speculative(Relation relation, TupleTableSlot *slot,                                   CommandId cid, int options,                                   BulkInsertState bistate,                                   uint32 specToken){    /* nothing to do */}static voidblackhole_tuple_complete_speculative(Relation relation, TupleTableSlot *slot,                                     uint32 spekToken, bool succeeded){    /* nothing to do */}static voidblackhole_multi_insert(Relation relation, TupleTableSlot **slots,                       int ntuples, CommandId cid, int options,                       BulkInsertState bistate){    /* nothing to do */}static TM_Resultblackhole_tuple_delete(Relation relation, ItemPointer tid, CommandId cid,                       Snapshot snapshot, Snapshot crosscheck, bool wait,                       TM_FailureData *tmfd, bool changingPart){    /* nothing to do, so it is always OK */    return TM_Ok;}static TM_Resultblackhole_tuple_update(Relation relation, ItemPointer otid,                       TupleTableSlot *slot, CommandId cid,                       Snapshot snapshot, Snapshot crosscheck,                       bool wait, TM_FailureData *tmfd,                       LockTupleMode *lockmode, bool *update_indexes){    /* nothing to do, so it is always OK */    return TM_Ok;}static TM_Resultblackhole_tuple_lock(Relation relation, ItemPointer tid, Snapshot snapshot,                     TupleTableSlot *slot, CommandId cid, LockTupleMode mode,                     LockWaitPolicy wait_policy, uint8 flags,                     TM_FailureData *tmfd){    /* nothing to do, so it is always OK */    return TM_Ok;}static voidblackhole_finish_bulk_insert(Relation relation, int options){    /* nothing to do */}/* ------------------------------------------------------------------------ * DDL related callbacks for blackhole AM. * ------------------------------------------------------------------------ */static voidblackhole_relation_set_new_filenode(Relation rel,                                    const RelFileNode *newrnode,                                    char persistence,                                    TransactionId *freezeXid,                                    MultiXactId *minmulti){    /* nothing to do */}static voidblackhole_relation_nontransactional_truncate(Relation rel){    /* nothing to do */}static voidblackhole_copy_data(Relation rel, const RelFileNode *newrnode){    /* there is no data */}static voidblackhole_copy_for_cluster(Relation OldTable, Relation NewTable,                           Relation OldIndex, bool use_sort,                           TransactionId OldestXmin,                           TransactionId *xid_cutoff,                           MultiXactId *multi_cutoff,                           double *num_tuples,                           double *tups_vacuumed,                           double *tups_recently_dead){    /* no data, so nothing to do */}static voidblackhole_vacuum(Relation onerel, VacuumParams *params,                 BufferAccessStrategy bstrategy){    /* no data, so nothing to do */}static boolblackhole_scan_analyze_next_block(TableScanDesc scan, BlockNumber blockno,                                  BufferAccessStrategy bstrategy){    /* no data, so no point to analyze next block */    return false;}static boolblackhole_scan_analyze_next_tuple(TableScanDesc scan, TransactionId OldestXmin,                                  double *liverows, double *deadrows,                                  TupleTableSlot *slot){    /* no data, so no point to analyze next tuple */    return false;}static doubleblackhole_index_build_range_scan(Relation tableRelation,                                 Relation indexRelation,                                 IndexInfo *indexInfo,                                 bool allow_sync,                                 bool anyvisible,                                 bool progress,                                 BlockNumber start_blockno,                                 BlockNumber numblocks,                                 IndexBuildCallback callback,                                 void *callback_state,                                 TableScanDesc scan){    /* no data, so no tuples */    return 0;}static voidblackhole_index_validate_scan(Relation tableRelation,                              Relation indexRelation,                              IndexInfo *indexInfo,                              Snapshot snapshot,                              ValidateIndexState *state){    /* nothing to do */}/* ------------------------------------------------------------------------ * Miscellaneous callbacks for the blackhole AM * ------------------------------------------------------------------------ */static uint64blackhole_relation_size(Relation rel, ForkNumber forkNumber){    /* there is nothing */    return 0;}/* * Check to see whether the table needs a TOAST table. */static boolblackhole_relation_needs_toast_table(Relation rel){    /* no data, so no toast table needed */    return false;}/* ------------------------------------------------------------------------ * Planner related callbacks for the blackhole AM * ------------------------------------------------------------------------ */static voidblackhole_estimate_rel_size(Relation rel, int32 *attr_widths,                            BlockNumber *pages, double *tuples,                            double *allvisfrac){    /* no data available */    *attr_widths = 0;    *tuples = 0;    *allvisfrac = 0;    *pages = 0;}/* ------------------------------------------------------------------------ * Executor related callbacks for the blackhole AM * ------------------------------------------------------------------------ */static boolblackhole_scan_bitmap_next_block(TableScanDesc scan,                                 TBMIterateResult *tbmres){    /* no data, so no point to scan next block */    return false;}static boolblackhole_scan_bitmap_next_tuple(TableScanDesc scan,                                 TBMIterateResult *tbmres,                                 TupleTableSlot *slot){    /* no data, so no point to scan next tuple */    return false;}static boolblackhole_scan_sample_next_block(TableScanDesc scan,                                 SampleScanState *scanstate){    /* no data, so no point to scan next block for sampling */    return false;}static boolblackhole_scan_sample_next_tuple(TableScanDesc scan,                                 SampleScanState *scanstate,                                 TupleTableSlot *slot){    /* no data, so no point to scan next tuple for sampling */    return false;}/* ------------------------------------------------------------------------ * Definition of the blackhole table access method. * ------------------------------------------------------------------------ */static const TableAmRoutine blackhole_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,    /* these are common helper functions */    .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 */-- complain if script is sourced in psql, rather than via CREATE EXTENSION\echo Use "CREATE EXTENSION blackhole_am" to load this file. \quitCREATE FUNCTION blackhole_am_handler(internal)RETURNS table_am_handlerAS 'MODULE_PATHNAME'LANGUAGE C;-- Access methodCREATE ACCESS METHOD blackhole_am TYPE TABLE HANDLER blackhole_am_handler;COMMENT ON ACCESS METHOD blackhole_am IS 'template table AM eating all data';

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

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