本篇内容主要讲解“怎么使用PostgreSQL12的存储接口”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么使用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 data';
PG 12的Pluggable storage for tables为数据表的访问打开了另外一扇窗,使用者完全可以根据自身需求自行实现自己的存取方式.
到此，相信大家对“怎么使用PostgreSQL12的存储接口”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
