Re: Question about the holdable cursor

On Thu, Apr 18, 2019 at 10:09 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andy Fan <zhihui(dot)fan1213(at)gmail(dot)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
>