Protocol question regarding Portal vs Cursor

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

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

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

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.

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

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

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

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

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

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

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