Thread: Protocol question regarding Portal vs Cursor
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
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 CramerOn 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 folding2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveErrorResponse <=BE ErrorMessage(ERROR: portal "C_3" does not existAs 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 folding2024-07-25 15:55:39 FINEST org.postgresql.core.v3.QueryExecutorImpl receiveErrorResponse <=BE ErrorMessage(ERROR: portal "C_3" does not existAs evidenced by this error message.Location: File: postgres.c, Routine: exec_describe_portal_message, Line: 2708You 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