Re: Can not cancel a call to a function that has opened a refcursor

On Thu, 5 Aug 2021 at 11:19, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mike Knowsley <mike(dot)knowsley(at)bidorbuy(dot)co(dot)za> writes:
> > A request to cancel a call to a function does not cancel the function if
> the function has opened a refcursor which it is returning.
>
> As far as I can see, there is no such bug; you're just misunderstanding
> when the query referenced by the cursor will execute. The function
> test_delay_in_refcursor() sets up the cursor and returns basically
> immediately (certainly in much less than 2 seconds), so the timeout
> you have in the JDBC code will never fire. The expected pg_sleep(5)
> won't execute till you try to fetch something from the refcursor.
>
> Trying this manually in psql, I get
>
> ...
> CREATE FUNCTION
> regression=# \timing
> Timing is on.
> regression=# begin;
> BEGIN
> Time: 0.306 ms
> regression=*# select test_delay_in_refcursor();
> test_delay_in_refcursor
> -------------------------
> <unnamed portal 1>
> (1 row)
>
> Time: 0.314 ms
> regression=*# fetch from "<unnamed portal 1>";
> ?column? | pg_sleep
> ----------+----------
> test |
> (1 row)
>
> Time: 5005.475 ms (00:05.005)
>
> I could have canceled the FETCH (and doing so works);
> but I'm way too old and slow to cancel the submillisecond
> setup step.
>
>
Ya, that was the other idea I had.

Dave