Re: Question about the holdable cursor
| От | Andy Fan |
|---|---|
| Тема | Re: Question about the holdable cursor |
| Дата | |
| Msg-id | CAKU4AWoz-AAKYLi0T=cBWLPxRM068s9dQW2daQDg-hmqkConwQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Question about the holdable cursor (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: