Re: PL/pgSQL cursors should get generated portal names by default

On 11/4/22 03:22, Pavel Stehule wrote:
> Hi
>
>
> st 2. 11. 2022 v 0:39 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> napsal:
>
> There's a complaint at [1] about how you can't re-use the same
> cursor variable name within a routine called from another routine
> that's already using that name.  The complaint is itself a bit
> under-documented, but I believe it is referring to this ancient
> bit of behavior:
>
>          A bound cursor variable is initialized to the string value
>          representing its name, so that the portal name is the same as
>          the cursor variable name, unless the programmer overrides it
>          by assignment before opening the cursor.
>
> So if you try to nest usage of two bound cursor variables of the
> same name, it blows up on the portal-name conflict.  But it'll work
> fine if you use unbound cursors (i.e., plain "refcursor" variables):
>
>          But an unbound cursor
>          variable defaults to the null value initially, so it will
> receive
>          an automatically-generated unique name, unless overridden.
>
> I wonder why we did it like that; maybe it's to be bug-compatible with
> some Oracle PL/SQL behavior or other?  Anyway, this seems non-orthogonal
> and contrary to all principles of structured programming.  We don't even
> offer an example of the sort of usage that would benefit from it, ie
> that calling code could "just know" what the portal name is.
>
> I propose that we should drop this auto initialization and let all
> refcursor variables start out null, so that they'll get unique
> portal names unless you take explicit steps to do something else.
> As attached.
>
> (Obviously this would be a HEAD-only fix, but maybe there's scope for
> improving the back-branch docs along lines similar to these changes.)
>
>
> I am sending an review of this patch
>
> 1. The patching, compilation without any problems
> 2. All tests passed
> 3. The implemented change is documented well
> 4. Although this is potencial compatibility break, we want this feature.
> It allows to use cursors variables in recursive calls by default, it
> allows shadowing of cursor variables
> 5. This patch is short and almost trivial, just remove code.
>
> I'll mark this patch as ready for commit

I need to do some testing on this. I seem to recall that the naming was
originally done because a reference cursor is basically a named cursor
that can be handed around between functions and even the top SQL level
of the application. For the latter to work the application needs to know
the name of the portal.

I am currently down with Covid and have trouble focusing. But I hope to
get to it some time next week.

Regards, Jan