Обсуждение: Question about the holdable cursor
when I fetch from holdable cursor, I found the fact is more complex than I expected.
suppose we fetched 20 rows.
1). It will fill a PortalStore, the dest is not the client, it is the DestTupleStore, called ExecutePlan once and receiveSlot will be call 20 times.
2). the portal for client then RunFromStore and send the result to client. the receiveSlot will be call 20 times again.
3). at last, when we HoldPortal, called ExecutePlan once again and receiveSlot will be call 20 times
```
0 in ExecutePlan of execMain.c:1696
1 in standard_ExecutorRun of execMain.c:366
2 in ExecutorRun of execMain.c:309
3 in PersistHoldablePortal of portalcmds.c:392
4 in HoldPortal of portalmem.c:639
5 in PreCommit_Portals of portalmem.c:733
6 in CommitTransaction of xact.c:2007
7 in CommitTransactionCommand of xact.c:2801
8 in finish_xact_command of postgres.c:2529
9 in exec_simple_query of postgres.c:1176
10 in exec_docdb_simple_query of postgres.c:5069
11 in _exec_query_with_intercept_exception of op_executor.c:38
12 in exec_op_query of op_executor.c:102
13 in exec_op_find of op_executor.c:204
14 in run_op_find_common of op_find_common.c:42
15 in _cmd_run_find of cmd_find.c:31
16 in run_commands of commands.c:610
17 in DocdbMain of postgres.c:4792
18 in DocdbBackendRun of postmaster.c:4715
19 in DocdbBackendStartup of postmaster.c:4196
20 in ServerLoop of postmaster.c:1760
21 in PostmasterMain of postmaster.c:1406
22 in main of main.c:228
```
why the 3rd time is necessary and will the performance be bad due to this design?
Thanks for your help!
Andy Fan <zhihui.fan1213@gmail.com> writes:
> when I fetch from holdable cursor, I found the fact is more complex than I
> expected.
> ...
> why the 3rd time is necessary and will the performance be bad due to this
> design?
If you read the whole cursor output, then close the transaction and
persist the cursor, yes we'll read it twice, and yes it's bad for that
case. The design is intended to perform well in these other cases:
1. The HOLD option isn't really being used, ie you just read and
close the cursor within the original transaction. This is important
because applications are frequently sloppy about marking cursors as
WITH HOLD.
2. You declare the cursor and persist it before reading anything from it.
(This is really the typical use-case for held cursors, IMV.)
FWIW, I don't see any intermediate tuplestore in there when
dealing with a PORTAL_ONE_SELECT query, which is the only
case that's possible with a cursor no?
regards, tom lane
On Thu, Apr 18, 2019 at 10:09 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andy Fan <zhihui.fan1213@gmail.com> writes:
> when I fetch from holdable cursor, I found the fact is more complex than I
> expected.
> ...
> why the 3rd time is necessary and will the performance be bad due to this
> design?
If you read the whole cursor output, then close the transaction and
persist the cursor, yes we'll read it twice, and yes it's bad for that
case. The design is intended to perform well in these other cases:
Thanks you Tom for the reply!! Looks this situation is really hard to produce but I just got there:( Please help me to confirm my understanding:
1. we can have 2 methods to reproduce it:
Method 1:
a). begin; // begin the transaction explicitly
b). declare c1 cursor WITH HOLD for select * from t; // declare the cursor with HOLD option.
c). fetch n c1; // this will run ExecutePlan the first time.
d). commit // commit the transaction explicitly, which caused the 2nd ExecutePlan. Write "ALL the records" into tuplestore.
Method 2:
a). declare c1 cursor WITH HOLD for select * from t; fetch n c1; // send 1 query with 2 statements, with implicitly transaction begin/commit;
(even though, I don't know how to send "declare c1 cursor WITH HOLD for select * from t; fetch n c1; " as one query in psql shell)
2. with a bit of more normal case:
a). declare c1 cursor WITH HOLD for select * from t; // declare the cursor with HOLD option. the transaction is started implicitly and commit implicitly. during the commit, "ExecutePlan" is called first time and "GET ALL THE RECORDS" and store ALL OF them (what if it is very big, write to file）？
b). fetch 10 c1; // will not run ExecutePlan any more.
even though, "GET ALL THE RECORDS" at the step 1 is expensive.
3). without hold option
a) begin;
b). declare c1 cursor for select * from t; .// without hold option.
c). fetch 1 c1; // this only scan 1 row.
d). commit;
if so, the connection can't be used for other transactions until I commit the transaction for cursor (which is something I dislike for now).
Could you help to me confirm my understandings are correct regarding the 3 topics? Thanks
1. The HOLD option isn't really being used, ie you just read and
close the cursor within the original transaction. This is important
because applications are frequently sloppy about marking cursors as
WITH HOLD.
2. You declare the cursor and persist it before reading anything from it.
(This is really the typical use-case for held cursors, IMV.)
FWIW, I don't see any intermediate tuplestore in there when
dealing with a PORTAL_ONE_SELECT query, which is the only
case that's possible with a cursor no?
regards, tom lane