PostgreSQL 12 引入了可插入表存储接口(Pluggable storage for tables)，近日Michael Paquier提交了一份Blackhole Table Access Method的模板方法. 之所以称为黑洞,是因为不管你在数据表上执行什么操作,都会看不到任何的反应,似乎被"黑洞"吸进去了,下面我们先睹为快. ### 一、安装体验 在[Github](https://github.com/michaelpq/pg_plugins/tree/master/blackhole_am)上下载源码,编译安装. ``` [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与先前我们分析过的[做法](http://blog.itpub.net/6906/viewspace-2646264/)基本吻合,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为数据表的访问打开了另外一扇窗,使用者完全可以根据自身需求自行实现自己的存取方式. ### 三、参考资料 [Postgres 12 highlight - Table Access Methods and blackholes](https://paquier.xyz/postgresql-2/postgres-12-table-am-blackhole/)