Horton

Thread

  1. Protocol question regarding Portal vs Cursor

    Dave Cramer <davecramer@gmail.com> — 2023-11-07T11:38:18Z

    Greetings,

If we use a Portal it is possible to open the portal and do a describe and
then Fetch N records.

Using a Cursor we open the cursor. Is there a corresponding describe and a
way to fetch N records without getting the fields each time. Currently we
have to send the SQL  "fetch <direction> N" and we get the fields and the
rows. This seems overly verbose.

Dave Cramer

  2. Re: Protocol question regarding Portal vs Cursor

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-11-07T15:26:42Z

    Dave Cramer <davecramer@gmail.com> writes:
> If we use a Portal it is possible to open the portal and do a describe and
> then Fetch N records.

> Using a Cursor we open the cursor. Is there a corresponding describe and a
> way to fetch N records without getting the fields each time. Currently we
> have to send the SQL  "fetch <direction> N" and we get the fields and the
> rows. This seems overly verbose.

Portals and cursors are pretty much the same thing, so why not use
the API that suits you better?

			regards, tom lane

  3. Re: Protocol question regarding Portal vs Cursor

    Dave Cramer <davecramer@gmail.com> — 2023-11-08T11:02:56Z

    Dave Cramer


On Tue, 7 Nov 2023 at 10:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Dave Cramer <davecramer@gmail.com> writes:
> > If we use a Portal it is possible to open the portal and do a describe
> and
> > then Fetch N records.
>
> > Using a Cursor we open the cursor. Is there a corresponding describe and
> a
> > way to fetch N records without getting the fields each time. Currently we
> > have to send the SQL  "fetch <direction> N" and we get the fields and the
> > rows. This seems overly verbose.
>
> Portals and cursors are pretty much the same thing, so why not use
> the API that suits you better?
>

So in this case this is a refcursor. Based on above then I should be able
to do a describe on the refcursor and fetch using the extended query
protocol

Cool!

Dave

  4. Re: Protocol question regarding Portal vs Cursor

    Dave Cramer <davecramer@gmail.com> — 2024-07-25T20:14:24Z

    Hi Tom,





On Wed, 8 Nov 2023 at 06:02, Dave Cramer <davecramer@gmail.com> wrote:

>
> Dave Cramer
>
>
> On Tue, 7 Nov 2023 at 10:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Dave Cramer <davecramer@gmail.com> writes:
>> > If we use a Portal it is possible to open the portal and do a describe
>> and
>> > then Fetch N records.
>>
>> > Using a Cursor we open the cursor. Is there a corresponding describe
>> and a
>> > way to fetch N records without getting the fields each time. Currently
>> we
>> > have to send the SQL  "fetch <direction> N" and we get the fields and
>> the
>> > rows. This seems overly verbose.
>>
>> Portals and cursors are pretty much the same thing, so why not use
>> the API that suits you better?
>>
>
> So in this case this is a refcursor. Based on above then I should be able
> to do a describe on the refcursor and fetch using the extended query
> protocol
>

Is it possible to describe a CURSOR

Testing out the above hypothesis

2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
sendSimpleQuery  FE=> SimpleQuery(query="declare C_3 CURSOR WITHOUT HOLD
FOR SELECT * FROM testsps WHERE id = 2")
2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
sendDescribePortal  FE=> Describe(portal=C_3)
2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
sendExecute  FE=> Execute(portal=C_3,limit=10)
2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
sendSync  FE=> Sync

gives me the following results

2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
receiveErrorResponse  <=BE ErrorMessage(ERROR: portal "C_3" does not exist
  Location: File: postgres.c, Routine: exec_describe_portal_message, Line:
2708
  Server SQLState: 34000)

Note Describe portal is really just a DESCRIBE message, the log messages
are misleading

Dave

>

  5. Re: Protocol question regarding Portal vs Cursor

    David G. Johnston <david.g.johnston@gmail.com> — 2024-07-25T20:19:03Z

    On Thursday, July 25, 2024, Dave Cramer <davecramer@gmail.com> wrote:

May not make a difference but…


> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
> sendSimpleQuery  FE=> SimpleQuery(query="declare C_3 CURSOR WITHOUT HOLD
> FOR SELECT * FROM testsps WHERE id = 2")
>

You named the cursor c_3 (lowercase due to SQL case folding)


> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
> sendDescribePortal  FE=> Describe(portal=C_3)
>

The protocol doesn’t do case folding


>
> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
> receiveErrorResponse  <=BE ErrorMessage(ERROR: portal "C_3" does not exist
>

As evidenced by this error message.

  Location: File: postgres.c, Routine: exec_describe_portal_message, Line:
> 2708
>
>

David J.

  6. Re: Protocol question regarding Portal vs Cursor

    Dave Cramer <davecramer@gmail.com> — 2024-07-25T21:52:41Z

    On Thu, 25 Jul 2024 at 16:19, David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Thursday, July 25, 2024, Dave Cramer <davecramer@gmail.com> wrote:
>
> May not make a difference but…
>
>
>> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
>> sendSimpleQuery  FE=> SimpleQuery(query="declare C_3 CURSOR WITHOUT HOLD
>> FOR SELECT * FROM testsps WHERE id = 2")
>>
>
> You named the cursor c_3 (lowercase due to SQL case folding)
>
>
>> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
>> sendDescribePortal  FE=> Describe(portal=C_3)
>>
>
> The protocol doesn’t do case folding
>
>
>>
>> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
>> receiveErrorResponse  <=BE ErrorMessage(ERROR: portal "C_3" does not exist
>>
>
> As evidenced by this error message.
>
>   Location: File: postgres.c, Routine: exec_describe_portal_message, Line:
>> 2708
>>
>>
>
> You would be absolutely correct! Thanks for the quick response

Dave

>

  7. Re: Protocol question regarding Portal vs Cursor

    Dave Cramer <davecramer@gmail.com> — 2024-07-26T12:22:50Z

    On Thu, 25 Jul 2024 at 17:52, Dave Cramer <davecramer@gmail.com> wrote:

>
>
> On Thu, 25 Jul 2024 at 16:19, David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Thursday, July 25, 2024, Dave Cramer <davecramer@gmail.com> wrote:
>>
>> May not make a difference but…
>>
>>
>>> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
>>> sendSimpleQuery  FE=> SimpleQuery(query="declare C_3 CURSOR WITHOUT HOLD
>>> FOR SELECT * FROM testsps WHERE id = 2")
>>>
>>
>> You named the cursor c_3 (lowercase due to SQL case folding)
>>
>>
>>> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
>>> sendDescribePortal  FE=> Describe(portal=C_3)
>>>
>>
>> The protocol doesn’t do case folding
>>
>>
>>>
>>> 2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl
>>> receiveErrorResponse  <=BE ErrorMessage(ERROR: portal "C_3" does not exist
>>>
>>
>> As evidenced by this error message.
>>
>>   Location: File: postgres.c, Routine: exec_describe_portal_message,
>>> Line: 2708
>>>
>>>
>>
>> You would be absolutely correct! Thanks for the quick response
>
>
So while the API's are "virtually" identical AFAICT there is no way to
create a "WITH HOLD" portal ?

Dave

>

  8. Re: Protocol question regarding Portal vs Cursor

    Tatsuo Ishii <ishii@postgresql.org> — 2024-07-27T05:55:04Z

    > 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,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

  9. Re: Protocol question regarding Portal vs Cursor

    Dave Cramer <davecramer@gmail.com> — 2024-07-27T16:05:00Z

    Dave Cramer


On Sat, 27 Jul 2024 at 01:55, Tatsuo Ishii <ishii@postgresql.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

  10. Re: Protocol question regarding Portal vs Cursor

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-07-27T19:18:43Z

    Dave Cramer <davecramer@gmail.com> writes:
> On Sat, 27 Jul 2024 at 01:55, Tatsuo Ishii <ishii@postgresql.org> wrote:
>>> So while the API's are "virtually" identical AFAICT there is no way to
>>> create a "WITH HOLD" portal ?

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

Yeah.  The two APIs (cursors and extended query protocol) manipulate
the same underlying Portal objects, but the features exposed by the
APIs aren't all identical.  We've felt that this isn't high priority
to sync up, since you can create a Portal with one API then manipulate
it through the other if need be.

			regards, tom lane

  11. Re: Protocol question regarding Portal vs Cursor

    Tatsuo Ishii <ishii@postgresql.org> — 2024-07-27T23:06:00Z

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

Ok.

It would be possible to add a new parameter to the BIND command to
create such a portal. But it needs some changes to the existing
protocol definition and requires protocol version up, which is a major
pain.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

  12. Re: Protocol question regarding Portal vs Cursor

    Dave Cramer <davecramer@gmail.com> — 2024-07-28T10:30:02Z

    On Sat, 27 Jul 2024 at 19:06, Tatsuo Ishii <ishii@postgresql.org> wrote:

> > Yes, sorry, I should have said one can not create a with hold portal
> using
> > the BIND command
>
> Ok.
>
> It would be possible to add a new parameter to the BIND command to
> create such a portal. But it needs some changes to the existing
> protocol definition and requires protocol version up, which is a major
> pain.
>

I'm trying to add WITH HOLD to the JDBC driver and currently I would have
1) rewrite the query
2) issue a new query ... declare .. and bind variables to that statement
3) execute fetch

vs

1) bind variables to the statement
2) execute fetch

The second can be done much lower in the code.

However as you mentioned this would require a new protocol version which is
unlikely to happen.

Dave