共计 16838 个字符，预计需要花费 43 分钟才能阅读完成。
本篇内容主要讲解“怎么使用 PostgreSQL12 的存储接口”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“怎么使用 PostgreSQL12 的存储接口”吧!
PostgreSQL 12 引入了可插入表存储接口 (Pluggable storage for tables)，近日 Michael Paquier 提交了一份 Blackhole Table Access Method 的模板方法.
之所以称为黑洞, 是因为不管你在数据表上执行什么操作, 都会看不到任何的反应, 似乎被”黑洞”吸进去了, 下面我们先睹为快.
一、安装体验
在
Github
上下载源码, 编译安装.
[root@localhost blackhole_am]# make
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.Po
blackhole_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.Po
blackhole_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 testdb
psql (12beta1)
Type help for help.
testdb=# CREATE EXTENSION blackhole_am;
CREATE EXTENSION
testdb=# \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 TABLE
testdb=# 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 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);
/* 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 TableScanDesc
blackhole_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 void
blackhole_scan_end(TableScanDesc sscan)
BlackholeScanDesc scan = (BlackholeScanDesc) sscan;
pfree(scan);
static void
blackhole_scan_rescan(TableScanDesc sscan, ScanKey key, bool set_params,
bool allow_strat, bool allow_sync, bool allow_pagemode)
/* nothing to do */
static bool
blackhole_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 void
blackhole_index_fetch_reset(IndexFetchTableData *scan)
/* nothing to do here */
static void
blackhole_index_fetch_end(IndexFetchTableData *scan)
/* nothing to do here */
static bool
blackhole_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 bool
blackhole_fetch_row_version(Relation relation,
ItemPointer tid,
Snapshot snapshot,
TupleTableSlot *slot)
/* nothing to do */
return false;
static void
blackhole_get_latest_tid(TableScanDesc sscan,
ItemPointer tid)
/* nothing to do */
static bool
blackhole_tuple_tid_valid(TableScanDesc scan, ItemPointer tid)
return false;
static bool
blackhole_tuple_satisfies_snapshot(Relation rel, TupleTableSlot *slot,
Snapshot snapshot)
return false;
static TransactionId
blackhole_compute_xid_horizon_for_tuples(Relation rel,
ItemPointerData *tids,
int nitems)
return InvalidTransactionId;
/* ----------------------------------------------------------------------------
* Functions for manipulations of physical tuples for blackhole AM.
* ----------------------------------------------------------------------------
*/
static void
blackhole_tuple_insert(Relation relation, TupleTableSlot *slot,
CommandId cid, int options, BulkInsertState bistate)
/* nothing to do */
static void
blackhole_tuple_insert_speculative(Relation relation, TupleTableSlot *slot,
CommandId cid, int options,
BulkInsertState bistate,
uint32 specToken)
/* nothing to do */
static void
blackhole_tuple_complete_speculative(Relation relation, TupleTableSlot *slot,
uint32 spekToken, bool succeeded)
/* nothing to do */
static void
blackhole_multi_insert(Relation relation, TupleTableSlot **slots,
int ntuples, CommandId cid, int options,
BulkInsertState bistate)
/* nothing to do */
static TM_Result
blackhole_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_Result
blackhole_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_Result
blackhole_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 void
blackhole_finish_bulk_insert(Relation relation, int options)
/* nothing to do */
/* ------------------------------------------------------------------------
* DDL related callbacks for blackhole AM.
* ------------------------------------------------------------------------
*/
static void
blackhole_relation_set_new_filenode(Relation rel,
const RelFileNode *newrnode,
char persistence,
TransactionId *freezeXid,
MultiXactId *minmulti)
/* nothing to do */
static void
blackhole_relation_nontransactional_truncate(Relation rel)
/* nothing to do */
static void
blackhole_copy_data(Relation rel, const RelFileNode *newrnode)
/* there is no data */
static void
blackhole_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 void
blackhole_vacuum(Relation onerel, VacuumParams *params,
BufferAccessStrategy bstrategy)
/* no data, so nothing to do */
static bool
blackhole_scan_analyze_next_block(TableScanDesc scan, BlockNumber blockno,
BufferAccessStrategy bstrategy)
/* no data, so no point to analyze next block */
return false;
static bool
blackhole_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 double
blackhole_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 void
blackhole_index_validate_scan(Relation tableRelation,
Relation indexRelation,
IndexInfo *indexInfo,
Snapshot snapshot,
ValidateIndexState *state)
/* nothing to do */
/* ------------------------------------------------------------------------
* Miscellaneous callbacks for the blackhole AM
* ------------------------------------------------------------------------
*/
static uint64
blackhole_relation_size(Relation rel, ForkNumber forkNumber)
/* there is nothing */
return 0;
* Check to see whether the table needs a TOAST table.
*/
static bool
blackhole_relation_needs_toast_table(Relation rel)
/* no data, so no toast table needed */
return false;
/* ------------------------------------------------------------------------
* Planner related callbacks for the blackhole AM
* ------------------------------------------------------------------------
*/
static void
blackhole_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 bool
blackhole_scan_bitmap_next_block(TableScanDesc scan,
TBMIterateResult *tbmres)
/* no data, so no point to scan next block */
return false;
static bool
blackhole_scan_bitmap_next_tuple(TableScanDesc scan,
TBMIterateResult *tbmres,
TupleTableSlot *slot)
/* no data, so no point to scan next tuple */
return false;
static bool
blackhole_scan_sample_next_block(TableScanDesc scan,
SampleScanState *scanstate)
/* no data, so no point to scan next block for sampling */
return false;
static bool
blackhole_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
Datum
blackhole_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. \quit
CREATE FUNCTION blackhole_am_handler(internal)
RETURNS table_am_handler
AS MODULE_PATHNAME
LANGUAGE C;
-- Access method
CREATE ACCESS METHOD blackhole_am TYPE TABLE HANDLER blackhole_am_handler;
COMMENT ON ACCESS METHOD blackhole_am IS template table AM eating all dataPG 12 的 Pluggable storage for tables 为数据表的访问打开了另外一扇窗, 使用者完全可以根据自身需求自行实现自己的存取方式.
到此，相信大家对“怎么使用 PostgreSQL12 的存储接口”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
正文完
发表至： 数据库
2023-07-24
