Обсуждение: 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