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/)</math.h>

PostgreSQL 原始碼解讀（201）- PG 12 BlackholeAM for tables

相關文章