合众商道网站开发,可以投稿的写作网站,本地app怎么推广,lunix安装wordpress相关 《Postgresql源码#xff08;125#xff09;游标恢复执行的原理分析》 《Postgresql游标使用介绍#xff08;cursor#xff09;》 总结
开源PG中使用tuple store来缓存tuple集#xff0c;默认使用work_mem空间存放#xff0c;超过可以落盘。在PL的returns setof场景… 相关 《Postgresql源码125游标恢复执行的原理分析》 《Postgresql游标使用介绍cursor》 总结
开源PG中使用tuple store来缓存tuple集默认使用work_mem空间存放超过可以落盘。在PL的returns setof场景 和 loop内commit的场景 会使用tuple store暂存元组。tuple store的使用方法 配置dest reveiver为tuple storeCreateDestReceiver / SetTuplestoreDestReceiverParams执行ExecutorRun使用tuple store PL返回的tuplestore拿tupleFunctionNext → tuplestore_gettupleslot游标hold后从tuplestore拿tupleRunFromStore → tuplestore_gettupleslot
TupleStore使用场景一RETURNS SETOF函数
这个场景的惯用法如下
// 1. 创建Dest Receiver
treceiver CreateDestReceiver(DestTuplestore);
// 2 关键函数配置TupleStore
SetTuplestoreDestReceiverParams(treceiver, tStore, .... ...)
// 3 treceiver传入执行器调用执行器结果进入tStore
// 4 清理DestReceiver
// 5 从tStore取出结果使用用例
create table users(id int, name text, active bool);
insert into users values(1, a, true);
insert into users values(2, b, false);
insert into users values(3, c, true);CREATE OR REPLACE FUNCTION get_active_users() RETURNS SETOF users AS $$
BEGINRETURN QUERY SELECT * FROM users WHERE active true;
END;
$$ LANGUAGE plpgsql;select * from get_active_users();执行结果
postgres# select * from get_active_users();id | name | active
------------------1 | a | t3 | c | t
(2 rows)1 exec_stmt_return_query
执行时会走到exec_stmt_return_query中获取执行结果
调用SPI_execute_plan_extended执行后可以看到tstore中有了两条结果。 当前堆栈
(gdb) bt
#0 exec_stmt_return_query (estate0x7ffc569f6950, stmt0x17fe708) at pl_exec.c:3655
#1 0x00007fbaa37bc602 in exec_stmts (estate0x7ffc569f6950, stmts0x17fec18) at pl_exec.c:2079
#2 0x00007fbaa37bc229 in exec_stmt_block (estate0x7ffc569f6950, block0x17fec68) at pl_exec.c:1942
#3 0x00007fbaa37bba2b in exec_toplevel_block (estate0x7ffc569f6950, block0x17fec68) at pl_exec.c:1633
#4 0x00007fbaa37b99be in plpgsql_exec_function (func0x16d72f0, fcinfo0x17fb7d0, simple_eval_estate0x0, simple_eval_resowner0x0, procedure_resowner0x0, atomictrue) at pl_exec.c:622
#5 0x00007fbaa37d4151 in plpgsql_call_handler (fcinfo0x17fb7d0) at pl_handler.c:277
#6 0x000000000075c75d in ExecMakeTableFunctionResult (setexpr0x1763e68, econtext0x1763d38, argContext0x17fb6d0, expectedDesc0x1764138, randomAccessfalse) at execSRF.c:234
#7 0x0000000000777ca7 in FunctionNext (node0x1763b28) at nodeFunctionscan.c:94
#8 0x000000000075df9f in ExecScanFetch (node0x1763b28, accessMtd0x777bf5 FunctionNext, recheckMtd0x777fff FunctionRecheck) at execScan.c:131
#9 0x000000000075e014 in ExecScan (node0x1763b28, accessMtd0x777bf5 FunctionNext, recheckMtd0x777fff FunctionRecheck) at execScan.c:180
#10 0x0000000000778049 in ExecFunctionScan (pstate0x1763b28) at nodeFunctionscan.c:269
#11 0x0000000000759f16 in ExecProcNodeFirst (node0x1763b28) at execProcnode.c:464
#12 0x000000000074dccf in ExecProcNode (node0x1763b28) at ../../../src/include/executor/executor.h:274
#13 0x00000000007507cc in ExecutePlan (estate0x1763900, planstate0x1763b28, use_parallel_modefalse, operationCMD_SELECT, sendTuplestrue, numberTuples0, directionForwardScanDirection, dest0x17df418, execute_oncetrue) at execMain.c:1646
#14 0x000000000074e367 in standard_ExecutorRun (queryDesc0x17dfb90, directionForwardScanDirection, count0, execute_oncetrue) at execMain.c:363
#15 0x000000000074e17b in ExecutorRun (queryDesc0x17dfb90, directionForwardScanDirection, count0, execute_oncetrue) at execMain.c:304
#16 0x00000000009e6e62 in PortalRunSelect (portal0x1787ba0, forwardtrue, count0, dest0x17df418) at pquery.c:924
#17 0x00000000009e6b20 in PortalRun (portal0x1787ba0, count9223372036854775807, isTopLeveltrue, run_oncetrue, dest0x17df418, altdest0x17df418, qc0x7ffc569f7180) at pquery.c:768
#18 0x00000000009e06a2 in exec_simple_query (query_string0x16dd080 select * from get_active_users();) at postgres.c:1274
#19 0x00000000009e4cfb in PostgresMain (dbname0x16d7910 postgres, username0x1715bf8 mingjie) at postgres.c:4680
#20 0x00000000009dd07f in BackendMain (startup_data0x7ffc569f748c , startup_data_len4) at backend_startup.c:101
#21 0x000000000090be72 in postmaster_child_launch (child_typeB_BACKEND, startup_data0x7ffc569f748c , startup_data_len4, client_sock0x7ffc569f74b0) at launch_backend.c:265
#22 0x0000000000911702 in BackendStartup (client_sock0x7ffc569f74b0) at postmaster.c:3593
#23 0x000000000090ebf3 in ServerLoop () at postmaster.c:1674
#24 0x000000000090e5ad in PostmasterMain (argc1, argv0x16d58c0) at postmaster.c:1372
#25 0x00000000007d20ac in main (argc1, argv0x16d58c0) at main.c:1972 exec_stmt_return
继续执行到exec_stmt_return返回值是SET不需要干活。
3 plpgsql_exec_function
在plpgsql_exec_function最后处理刚才保存在tstore里面的元组 注意这里的estate-rsi指向的是fcinfo-resultinfo在这里配置的 这样结果数据就可以通过function调用框架返回给SQL层了。
TupleStore使用场景二游标持久化TupleStore作为Dest Receiver
两条路径会使用到游标持久化的功能
第一种是创SQL层游标时使用with hold语法游标可以跨多个事务存在。第二种是循环体内执行commit这时候循环游标正常会跟着最近一层事务被删掉但循环还需要继续执行所以需要hold给循环游标续命。
后面分析下第二种场景循环游标提交hold。
用例 drop procedure tproc1;
CREATE OR REPLACE PROCEDURE tproc1() AS $$
DECLARErec1 record;
BEGIN
for rec1 in select t.a from generate_series(1,10) t(a)
loopraise notice %, rec1.a;commit;raise notice %, rec1.a;end loop;
END;
$$ LANGUAGE plpgsql;call tproc1();
结果
postgres# call tproc1();
NOTICE: 1
NOTICE: 1
NOTICE: 2
NOTICE: 2
NOTICE: 3
NOTICE: 3
NOTICE: 4
NOTICE: 4
NOTICE: 5
NOTICE: 5
NOTICE: 6
NOTICE: 6
NOTICE: 7
NOTICE: 7
NOTICE: 8
NOTICE: 8
NOTICE: 9
NOTICE: 9
NOTICE: 10
NOTICE: 10
CALL1 HoldPinnedPortals 2 HoldPortal 3 HoldPortal→PortalCreateHoldStore
创建Context切换过去。在新上下文中执行tuplestore的初始化。
void
PortalCreateHoldStore(Portal portal)
{MemoryContext oldcxt;portal-holdContext AllocSetContextCreate(TopPortalContext,PortalHoldContext,ALLOCSET_DEFAULT_SIZES);oldcxt MemoryContextSwitchTo(portal-holdContext);portal-holdStore tuplestore_begin_heap(portal-cursorOptions CURSOR_OPT_SCROLL,true, work_mem);MemoryContextSwitchTo(oldcxt);
}3 HoldPortal→PersistHoldablePortal
void
PersistHoldablePortal(Portal portal)
{...
tuplestore需要再自己的上下文中保存一份desc。 oldcxt MemoryContextSwitchTo(portal-holdContext);portal-tupDesc CreateTupleDescCopy(portal-tupDesc);MemoryContextSwitchTo(oldcxt);标记portal正在干活READY → ACTIVE MarkPortalActive(portal);......PG_TRY();{ScanDirection direction ForwardScanDirection;ActivePortal portal;if (portal-resowner)CurrentResourceOwner portal-resowner;PortalContext portal-portalContext;MemoryContextSwitchTo(PortalContext);PushActiveSnapshot(queryDesc-snapshot);...创建tuple store的dest receiver并开始执行 queryDesc-dest CreateDestReceiver(DestTuplestore);SetTuplestoreDestReceiverParams(queryDesc-dest,portal-holdStore,portal-holdContext,true,NULL,NULL);注意这里ExecutorRun的第三个参数是0表示拿完为止。和游标fetch是有区别的fetch一次这里会传入1只拿一条。
参考这篇《Postgresql源码125游标恢复执行的原理分析》
继续分析 ExecutorRun(queryDesc, direction, 0, false);queryDesc-dest-rDestroy(queryDesc-dest);queryDesc-dest NULL;portal-queryDesc NULL; /* prevent double shutdown */ExecutorFinish(queryDesc);ExecutorEnd(queryDesc);FreeQueryDesc(queryDesc);MemoryContextSwitchTo(portal-holdContext);执行后tuples9之前已经查出来一条现在把剩下的9条都拿到了。 (gdb) p *portal-holdStore $6 {status TSS_INMEM, eflags 4, backward false, interXact true, truncated false, availMem 8371768, allowedMem 8388608, tuples 9, myfile 0x0, context 0x1855da0, resowner 0x1717530, copytup 0xc0051f copytup_heap, writetup 0xc0056a writetup_heap, readtup 0xc00635 readtup_heap, memtuples 0x1821e68, memtupdeleted 0, memtupcount 9, memtupsize 2048, growmemtuples true, readptrs 0x1855fb0, activeptr 0, readptrcount 1, readptrsize 8, writepos_file 0, writepos_offset 0} ......}...PG_END_TRY();MemoryContextSwitchTo(oldcxt);
portal用完了把状态改回来 /* Mark portal not active */portal-status PORTAL_READY;......
}至此游标内容已经全部进入tuple store。
SPI_cursor_fetch
commit后在循环中会继续fetch游标这里一个较大的区别时不在执行portal拿结果了从tuple store拿一行即可 这里使用tuplestore_gettupleslot从tuple里面拿数据
static uint64
RunFromStore(Portal portal, ScanDirection direction, uint64 count,DestReceiver *dest)
{......ok tuplestore_gettupleslot(portal-holdStore, forward, false, slot);......return current_tuple_count;
}
