本篇内容介绍了“怎么使用PostgreSQ中的ExecMaterial函数”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
一、数据结构
SubPlanState
子计划运行期状态
typedef struct SubPlanState
{
NodeTag type;
SubPlan *subplan;
struct PlanState *planstate;
struct PlanState *parent;
ExprState *testexpr;
List *args;
HeapTuple curTuple;
Datum curArray;
TupleDesc descRight;
ProjectionInfo *projLeft;
ProjectionInfo *projRight;
TupleHashTable hashtable;
TupleHashTable hashnulls;
bool havehashrows;
bool havenullrows;
MemoryContext hashtablecxt;
MemoryContext hashtempcxt;
ExprContext *innerecontext;
AttrNumber *keyColIdx;
Oid *tab_eq_funcoids;
Oid *tab_collations;
FmgrInfo *tab_hash_funcs;
FmgrInfo *tab_eq_funcs;
FmgrInfo *lhs_hash_funcs;
FmgrInfo *cur_eq_funcs;
ExprState *cur_eq_comp;
} SubPlanState;SubPlan
子查询计划
typedef struct SubPlan
{
Expr xpr;//表达式
//从SubLink中拷贝而来
SubLinkType subLinkType;
//组合操作符,转换为可执行的表达式
Node *testexpr;
List *paramIds;
//Plan tree标识
int plan_id;
//EXPLAIN和debug目的的SubPlan标识
char *plan_name;
//用于确定subplan输出类型的额外信息
Oid firstColType;
int32 firstColTypmod;
Oid firstColCollation;
//执行阶段的相关信息
bool useHashTable;
bool unknownEqFalse;
bool parallel_safe;
//用于给子查询传入和传出参数的信息
//setParam和parParam是整型链表(param IDs)
List *setParam;
List *parParam;
List *args;
//估算执行成本
Cost startup_cost;
Cost per_call_cost;
} SubPlan;SubLinkType
SubLink类型
typedef enum SubLinkType
{
EXISTS_SUBLINK,
ALL_SUBLINK,
ANY_SUBLINK,
ROWCOMPARE_SUBLINK,
EXPR_SUBLINK,
MULTIEXPR_SUBLINK,
ARRAY_SUBLINK,
CTE_SUBLINK
} SubLinkType;SubLink
SubLink结构体
typedef struct SubLink
{
Expr xpr;
SubLinkType subLinkType;
int subLinkId;
Node *testexpr;
List *operName;
Node *subselect;
int location;
} SubLink;MaterialState
Material状态
typedef struct MaterialState
{
ScanState ss;
int eflags;
bool eof_underlying;
Tuplestorestate *tuplestorestate;
} MaterialState;二、源码解读
static TupleTableSlot *
ExecMaterial(PlanState *pstate)
{
MaterialState *node = castNode(MaterialState, pstate);//物化节点
EState *estate;//运行期状态
ScanDirection dir;//扫描方向
bool forward;//是否往前扫描
Tuplestorestate *tuplestorestate;//Tuplestorestate结构体指针
bool eof_tuplestore;//是否完成?
TupleTableSlot *slot;//存储元组的slot
CHECK_FOR_INTERRUPTS();
estate = node->ss.ps.state;
dir = estate->es_direction;//方向
forward = ScanDirectionIsForward(dir);//是否往前扫描
tuplestorestate = node->tuplestorestate;
if (tuplestorestate == NULL && node->eflags != 0)
{
tuplestorestate = tuplestore_begin_heap(true, false, work_mem);
tuplestore_set_eflags(tuplestorestate, node->eflags);
if (node->eflags & EXEC_FLAG_MARK)
{
int ptrno PG_USED_FOR_ASSERTS_ONLY;
ptrno = tuplestore_alloc_read_pointer(tuplestorestate,
node->eflags);
Assert(ptrno == 1);
}
node->tuplestorestate = tuplestorestate;
}
eof_tuplestore = (tuplestorestate == NULL) ||
tuplestore_ateof(tuplestorestate);
if (!forward && eof_tuplestore)
{
if (!node->eof_underlying)
{
if (!tuplestore_advance(tuplestorestate, forward))
return NULL;
}
eof_tuplestore = false;
}
slot = node->ss.ps.ps_ResultTupleSlot;
if (!eof_tuplestore)
{
if (tuplestore_gettupleslot(tuplestorestate, forward, false, slot))
return slot;
if (forward)
eof_tuplestore = true;
}
if (eof_tuplestore && !node->eof_underlying)
{
PlanState *outerNode;
TupleTableSlot *outerslot;
outerNode = outerPlanState(node);
outerslot = ExecProcNode(outerNode);
if (TupIsNull(outerslot))
{
node->eof_underlying = true;
return NULL;
}
if (tuplestorestate)
tuplestore_puttupleslot(tuplestorestate, outerslot);
ExecCopySlot(slot, outerslot);
return slot;
}
return ExecClearTuple(slot);
}三、跟踪分析
执行SQL：
[pg12@localhost ~]$ psql -d testdb
Timing is on.
Expanded display is used automatically.
psql (12.0)
Type "help" for help.
[local]:5432 pg12@testdb=#
[local]:5432 pg12@testdb=# select * from tbl;
id | value
----+-------
1 | 2
(1 row)
Time: 2.678 ms
[local]:5432 pg12@testdb=# select count(*) from t_big_null;
count
----------
10000001
(1 row)
Time: 679.972 ms
[local]:5432 pg12@testdb=# analyze tbl;
ANALYZE
Time: 64.442 ms
[local]:5432 pg12@testdb=# analyze t_big_null;
ANALYZE
Time: 434.702 ms
[local]:5432 pg12@testdb=#
[local]:5432 pg12@testdb=# select pg_backend_pid();
pg_backend_pid
----------------
18758
(1 row)
Time: 1.990 ms
[local]:5432 pg12@testdb=# select * from tbl a where a.id not in (select b.id from t_big_null b);启动gdb跟踪
(gdb) b ExecMaterial
Breakpoint 1 at 0x720edb: file nodeMaterial.c, line 41.
(gdb) c
Continuing.
Breakpoint 1, ExecMaterial (pstate=0x1230128) at nodeMaterial.c:41
41 MaterialState *node = castNode(MaterialState, pstate);
(gdb)输入参数
(gdb) p *pstate
$4 = {type = T_MaterialState, plan = 0x1211858, state = 0x122fe88,
ExecProcNode = 0x720ecf <ExecMaterial>, ExecProcNodeReal = 0x720ecf <ExecMaterial>,
instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0, qual = 0x0,
lefttree = 0x1230240, righttree = 0x0, initPlan = 0x0, subPlan = 0x0, chgParam = 0x0,
ps_ResultTupleDesc = 0x1230660, ps_ResultTupleSlot = 0x1230778, ps_ExprContext = 0x0,
ps_ProjInfo = 0x0, scandesc = 0x1230548, scanops = 0xc3e720 <TTSOpsMinimalTuple>,
outerops = 0x0, innerops = 0x0, resultops = 0xc3e720 <TTSOpsMinimalTuple>,
scanopsfixed = true, outeropsfixed = false, inneropsfixed = false,
resultopsfixed = true, scanopsset = true, outeropsset = false, inneropsset = false,
resultopsset = true}
(gdb)MaterialState结构体指针数据
(gdb) p *node
$1 = {ss = {ps = {type = T_MaterialState, plan = 0x1211858, state = 0x122fe88,
ExecProcNode = 0x720ecf <ExecMaterial>, ExecProcNodeReal = 0x720ecf <ExecMaterial>,
instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0, qual = 0x0,
lefttree = 0x1230240, righttree = 0x0, initPlan = 0x0, subPlan = 0x0,
chgParam = 0x0, ps_ResultTupleDesc = 0x1230660, ps_ResultTupleSlot = 0x1230778,
ps_ExprContext = 0x0, ps_ProjInfo = 0x0, scandesc = 0x1230548,
scanops = 0xc3e720 <TTSOpsMinimalTuple>, outerops = 0x0, innerops = 0x0,
resultops = 0xc3e720 <TTSOpsMinimalTuple>, scanopsfixed = true,
outeropsfixed = false, inneropsfixed = false, resultopsfixed = true,
scanopsset = true, outeropsset = false, inneropsset = false, resultopsset = true},
ss_currentRelation = 0x0, ss_currentScanDesc = 0x0, ss_ScanTupleSlot = 0x1230838},
eflags = 2, eof_underlying = false, tuplestorestate = 0x0}
(gdb) p *node->ss->ps->plan
$2 = {type = T_Material, startup_cost = 0, total_cost = 233310.685, plan_rows = 9999979,
plan_width = 4, parallel_aware = false, parallel_safe = true, plan_node_id = 1,
targetlist = 0x1257600, qual = 0x0, lefttree = 0x1210f58, righttree = 0x0,
initPlan = 0x0, extParam = 0x0, allParam = 0x0}
(gdb)运行期信息和方向
(gdb) p *estate
$5 = {type = T_EState, es_direction = ForwardScanDirection, es_snapshot = 0x1164d50,
es_crosscheck_snapshot = 0x0, es_range_table = 0x1257328,
es_range_table_array = 0x12300d8, es_range_table_size = 2, es_relations = 0x1230100,
es_rowmarks = 0x0, es_plannedstmt = 0x1257748,
es_sourceText = 0x113cd88 "select * from tbl a where a.id not in (select b.id from t_big_null b);", es_junkFilter = 0x0, es_output_cid = 0, es_result_relations = 0x0,
es_num_result_relations = 0, es_result_relation_info = 0x0,
es_root_result_relations = 0x0, es_num_root_result_relations = 0,
es_partition_directory = 0x0, es_tuple_routing_result_relations = 0x0,
es_trig_target_relations = 0x0, es_param_list_info = 0x0,
es_param_exec_vals = 0x12300a0, es_queryEnv = 0x0, es_query_cxt = 0x122fd70,
es_tupleTable = 0x1230510, es_processed = 0, es_top_eflags = 16, es_instrument = 0,
es_finished = false, es_exprcontexts = 0x1230418, es_subplanstates = 0x1230920,
es_auxmodifytables = 0x0, es_per_tuple_exprcontext = 0x0, es_epq_active = 0x0,
es_use_parallel_mode = false, es_query_dsa = 0x0, es_jit_flags = 25, es_jit = 0x0,
es_jit_worker_instr = 0x0}
(gdb) p dir
$6 = ForwardScanDirection
(gdb)初始化tuplestore
(gdb) n
64 tuplestorestate = tuplestore_begin_heap(true, false, work_mem);
(gdb) n
65 tuplestore_set_eflags(tuplestorestate, node->eflags);
(gdb) p *tuplestorestate
$7 = {status = TSS_INMEM, eflags = 6, backward = false, interXact = false,
truncated = false, availMem = 4177896, allowedMem = 4194304, tuples = 0, myfile = 0x0,
context = 0x122fd70, resowner = 0x116e308, copytup = 0xaba7bd <copytup_heap>,
writetup = 0xaba811 <writetup_heap>, readtup = 0xaba9d9 <readtup_heap>,
memtuples = 0x1219e60, memtupdeleted = 0, memtupcount = 0, memtupsize = 2048,
growmemtuples = true, readptrs = 0x123ca50, activeptr = 0, readptrcount = 1,
readptrsize = 8, writepos_file = 0, writepos_offset = 0}
(gdb) n
66 if (node->eflags & EXEC_FLAG_MARK)
(gdb)
78 node->tuplestorestate = tuplestorestate;
(gdb)
85 eof_tuplestore = (tuplestorestate == NULL) ||
(gdb) p *tuplestorestate
$8 = {status = TSS_INMEM, eflags = 2, backward = false, interXact = false,
truncated = false, availMem = 4177896, allowedMem = 4194304, tuples = 0, myfile = 0x0,
context = 0x122fd70, resowner = 0x116e308, copytup = 0xaba7bd <copytup_heap>,
writetup = 0xaba811 <writetup_heap>, readtup = 0xaba9d9 <readtup_heap>,
memtuples = 0x1219e60, memtupdeleted = 0, memtupcount = 0, memtupsize = 2048,
growmemtuples = true, readptrs = 0x123ca50, activeptr = 0, readptrcount = 1,
readptrsize = 8, writepos_file = 0, writepos_offset = 0}
(gdb)既不是往后扫描也没有到达EOF
(gdb) n
86 tuplestore_ateof(tuplestorestate);
(gdb)
85 eof_tuplestore = (tuplestorestate == NULL) ||
(gdb)
88 if (!forward && eof_tuplestore)
(gdb) p eof_tuplestore
$9 = false
(gdb) p forward
$10 = true
(gdb)如能从tuplestore中提取另外一个tuple，则返回此slot
(gdb) n
107 slot = node->ss.ps.ps_ResultTupleSlot;
(gdb)
108 if (!eof_tuplestore)
(gdb) p *slot
$11 = {type = T_TupleTableSlot, tts_flags = 18, tts_nvalid = 0,
tts_ops = 0xc3e720 <TTSOpsMinimalTuple>, tts_tupleDescriptor = 0x1230660,
tts_values = 0x12307e8, tts_isnull = 0x12307f0, tts_mcxt = 0x122fd70, tts_tid = {
ip_blkid = {bi_hi = 65535, bi_lo = 65535}, ip_posid = 0}, tts_tableOid = 0}
(gdb) p slot->tts_values[0]
$12 = 0
(gdb) n
110 if (tuplestore_gettupleslot(tuplestorestate, forward, false, slot))
(gdb)如扫描方向为往前，则设置eof_tuplestore为T，填充tuple到tuplestore中
112 if (forward)
(gdb)
113 eof_tuplestore = true;
(gdb)如需要（处于tuplestore末尾），尝试从subplan（扫描t_big_null）中提取另外一行
124 if (eof_tuplestore && !node->eof_underlying)
(gdb) p node->eof_underlying
$13 = false
(gdb) n
133 outerNode = outerPlanState(node);
(gdb) n
134 outerslot = ExecProcNode(outerNode);
(gdb)
135 if (TupIsNull(outerslot))
(gdb) p *outerNode
$14 = {type = T_SeqScanState, plan = 0x1210f58, state = 0x122fe88,
ExecProcNode = 0x72b904 <ExecSeqScan>, ExecProcNodeReal = 0x72b904 <ExecSeqScan>,
instrument = 0x0, worker_instrument = 0x0, worker_jit_instrument = 0x0, qual = 0x0,
lefttree = 0x0, righttree = 0x0, initPlan = 0x0, subPlan = 0x0, chgParam = 0x0,
ps_ResultTupleDesc = 0x1230548, ps_ResultTupleSlot = 0x0, ps_ExprContext = 0x1230358,
ps_ProjInfo = 0x0, scandesc = 0x7f6ec53386a8,
scanops = 0xc3e780 <TTSOpsBufferHeapTuple>, outerops = 0x0, innerops = 0x0,
resultops = 0xc3e780 <TTSOpsBufferHeapTuple>, scanopsfixed = true,
outeropsfixed = false, inneropsfixed = false, resultopsfixed = true, scanopsset = true,
outeropsset = false, inneropsset = false, resultopsset = true}
(gdb) p *outerslot
$15 = {type = T_TupleTableSlot, tts_flags = 16, tts_nvalid = 0,
tts_ops = 0xc3e780 <TTSOpsBufferHeapTuple>, tts_tupleDescriptor = 0x7f6ec53386a8,
tts_values = 0x12304c0, tts_isnull = 0x12304c8, tts_mcxt = 0x122fd70, tts_tid = {
ip_blkid = {bi_hi = 0, bi_lo = 44240}, ip_posid = 1}, tts_tableOid = 49155}
(gdb) p outerslot->tts_values[0]
$16 = 0
(gdb) p outerslot->tts_values[1]
$17 = 0
(gdb)
###
[local]:5432 pg12@testdb=# select oid,relname from pg_class where oid = 49155;
oid | relname
-------+------------
49155 | t_big_null
(1 row)
###拷贝到tuplestore中，返回slot
(gdb) n
146 if (tuplestorestate)
(gdb)
147 tuplestore_puttupleslot(tuplestorestate, outerslot);
(gdb)
149 ExecCopySlot(slot, outerslot);
(gdb)
150 return slot;
(gdb) p *slot
$18 = {type = T_TupleTableSlot, tts_flags = 20, tts_nvalid = 0,
tts_ops = 0xc3e720 <TTSOpsMinimalTuple>, tts_tupleDescriptor = 0x1230660,
tts_values = 0x12307e8, tts_isnull = 0x12307f0, tts_mcxt = 0x122fd70, tts_tid = {
ip_blkid = {bi_hi = 65535, bi_lo = 65535}, ip_posid = 0}, tts_tableOid = 0}
(gdb) p *slot->tts_values
$19 = 0
(gdb)继续执行
...
(gdb) p slot->tts_values[0]
$24 = 9998243
(gdb) c
Continuing.
Breakpoint 1, ExecMaterial (pstate=0x1230128) at nodeMaterial.c:41
41 MaterialState *node = castNode(MaterialState, pstate);
(gdb) n
49 CHECK_FOR_INTERRUPTS();
(gdb)
54 estate = node->ss.ps.state;
(gdb)
55 dir = estate->es_direction;
(gdb)
56 forward = ScanDirectionIsForward(dir);
(gdb)
57 tuplestorestate = node->tuplestorestate;
(gdb)
62 if (tuplestorestate == NULL && node->eflags != 0)
(gdb)
85 eof_tuplestore = (tuplestorestate == NULL) ||
(gdb)
86 tuplestore_ateof(tuplestorestate);
(gdb)
85 eof_tuplestore = (tuplestorestate == NULL) ||
(gdb)
88 if (!forward && eof_tuplestore)
(gdb)
107 slot = node->ss.ps.ps_ResultTupleSlot;
(gdb)
108 if (!eof_tuplestore)
(gdb)
124 if (eof_tuplestore && !node->eof_underlying)
(gdb)
133 outerNode = outerPlanState(node);
(gdb)
134 outerslot = ExecProcNode(outerNode);
(gdb)
135 if (TupIsNull(outerslot))
(gdb)
146 if (tuplestorestate)
(gdb)
147 tuplestore_puttupleslot(tuplestorestate, outerslot);
(gdb)
149 ExecCopySlot(slot, outerslot);
(gdb)
150 return slot;
(gdb) p slot->tts_values[0]
$25 = 9998244
(gdb)
...“怎么使用PostgreSQ中的ExecMaterial函数”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
