Re: dynamic result sets support in extended query protocol

Hi

pá 14. 10. 2022 v 9:12 odesílatel Peter Eisentraut <
peter(dot)eisentraut(at)enterprisedb(dot)com> napsal:

> On 01.02.22 15:40, Peter Eisentraut wrote:
> > On 12.01.22 11:20, Julien Rouhaud wrote:
> >> Since you mentioned that this patch depends on the SHOW_ALL_RESULTS
> >> psql patch
> >> which is still being worked on, I'm not expecting much activity here
> >> until the
> >> prerequirements are done. It also seems better to mark this patch as
> >> Waiting
> >> on Author as further reviews are probably not really needed for now.
> >
> > Well, a review on the general architecture and approach would have been
> > useful. But I understand that without the psql work, it's difficult for
> > a reviewer to even get started on this patch. It's also similarly
> > difficult for me to keep updating it. So I'll set it to Returned with
> > feedback for now and take it off the table. I want to get back to it
> > when the prerequisites are more settled.
>
> Now that the psql support for multiple result sets exists, I want to
> revive this patch. It's the same as the last posted version, except now
> it doesn't require any psql changes or any weird test modifications
> anymore.
>
> (Old news: This patch allows declaring a cursor WITH RETURN in a
> procedure to make the cursor's data be returned as a result of the CALL
> invocation. The procedure needs to be declared with the DYNAMIC RESULT
> SETS attribute.)
>

I did a quick test of this patch, and it is working pretty well.

I have two ideas.

1. there can be possibility to set "dynamic result sets" to unknown. The
behaviour of the "dynamic result sets" option is a little bit confusing. I
expect the number of result sets should be exactly the same as this number.
But the warning is raised only when this number is acrossed. For this
implementation the correct name should be like "max dynamic result sets" or
some like this. At this moment, I see this feature "dynamic result sets"
more confusing, and because the effect is just a warning, then I don't see
a strong benefit. I can see some benefit if I can declare so CALL will be
without dynamic result sets, or with exact number of dynamic result sets or
with unknown number of dynamic result sets. And if the result is not
expected, then an exception should be raised (not warning).

2. Unfortunately, it doesn't work nicely with pagers. It starts a pager for
one result, and waits for the end, and starts pager for the second result,
and waits for the end. There is not a possibility to see all results at one
time. The current behavior is correct, but I don't think it is user
friendly. I think I can teach pspg to support multiple documents. But I
need a more robust protocol and some separators - minimally an empty line
(but some ascii control char can be safer). As second step we can introduce
new psql option like PSQL_MULTI_PAGER, that can be used when possible
result sets is higher than 1

Regards

Pavel