Re: Protocol question regarding Portal vs Cursor

Dave Cramer

On Sat, 27 Jul 2024 at 01:55, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:

> > So while the API's are "virtually" identical AFAICT there is no way to
> > create a "WITH HOLD" portal ?
>
> I am not sure if I fully understand your question but I think you can
> create a portal with "WITH HOLD" option.
>
> BEGIN;
> DECLARE c CURSOR WITH HOLD FOR SELECT * FROM generate_series(1,10);
>
> (of course you could use extended query protocol instead of simple
> query protocol here)
>
> After this there's portal named "c" in the backend with WITH HOLD
> attribute. And you could issue a Describe message against the portal.
> Also you could issue an Execute messages to fetch N rows (N can be
> specified in the Execute message) with or without in a transaction
> because WITH HOLD is specified.
>
> Here is a sample session. The generate_series() generates 10 rows. You
> can fetch 5 rows from portal "c" inside the transaction. After the
> transaction closed, you can fetch remaining 5 rows as expected.
>
> FE=> Query (query="BEGIN")
> <= BE CommandComplete(BEGIN)
> <= BE ReadyForQuery(T)
> FE=> Query (query="DECLARE c CURSOR WITH HOLD FOR SELECT * FROM
> generate_series(1,10)")
> <= BE CommandComplete(DECLARE CURSOR)
> <= BE ReadyForQuery(T)
> FE=> Describe(portal="c")
> FE=> Execute(portal="c")
> FE=> Sync
> <= BE RowDescription
> <= BE DataRow
> <= BE DataRow
> <= BE DataRow
> <= BE DataRow
> <= BE DataRow
> <= BE PortalSuspended
> <= BE ReadyForQuery(T)
> FE=> Query (query="END")
> <= BE CommandComplete(COMMIT)
> <= BE ReadyForQuery(I)
> FE=> Execute(portal="c")
> FE=> Sync
> <= BE DataRow
> <= BE DataRow
> <= BE DataRow
> <= BE DataRow
> <= BE DataRow
> <= BE PortalSuspended
> <= BE ReadyForQuery(I)
> FE=> Terminate
>
> Best reagards,
>

Yes, sorry, I should have said one can not create a with hold portal using
the BIND command

Dave