Thread: Reuse of REF Cursor

Has anybody faced a problem with reusing a ref cursor?

C1 refcursor;
....
   stmt := 'select1 ' ;
   open C1 for execute  stmt;
   fetch C1 into rc1;
   close C1;

   stmt := 'select2 ...' ;
   open C1 for execute  stmt;
   fetch C1 into rc2;
   close C1;

???

I am getting inconsistent errors:   IDTCONTEXT:  PL/pgSQL function ...... ) line 42 at FETCH

PG Version 9.5.5 on Linux.

Thanks

Danny

PG Version 9.5.5 on Linux.

"Abraham, Danny" <danny_abraham@bmc.com> writes:
> Has anybody faced a problem with reusing a ref cursor?

Your fragment looks like it ought to work, but it's hard to say
where the problem is without seeing a complete example.

> PG Version 9.5.5 on Linux.

9.5.x is EOL, and the last release in that series was 9.5.25,
so you *really* oughta think about an update.

            regards, tom lane

  stmt := 'select count(*) from ' || table_name;
   open C1 for execute  stmt;
   fetch C1 into rc;
   close C1;

   if (debug_level = 1) then
      if rc > 0 then
         perform diag_print(func_name,format('Counted %s records in table %s',rc,table_name) );
      else
         perform diag_print(func_name,format('Table %s is empty.',table_name) );
         return 0;
      end if;
   end if;

   stmt := 'select count(*) from ' || table_name || ' where orderno not in ( select orderno from cmr_ajf) ' ;
   if lower(table_name) = 'cmr_setvar'  then
        stmt := stmt || ' and orderno <> 0';
   end if;
   open C1 for execute  stmt;
   fetch C1 into rc;  <========================= Sometimes fail here
   close C1;
   if rc>0 then


Should I use a different variable for every refcursor usage ?

"Abraham, Danny" <danny_abraham@bmc.com> writes:
>   stmt := 'select count(*) from ' || table_name;
>    open C1 for execute  stmt;
>    fetch C1 into rc;
>    close C1;
>    ...

That still isn't a self-contained example; perhaps more usefully,
you've not told us exactly what error you're seeing, either.

FWIW, I tried executing a fragment like the above in a loop,
and it seemed fine.

            regards, tom lane

2021-04-09 08:00:08.692 IDTERROR:  canceling statement due to statement timeout
2021-04-09 08:00:08.692 IDTCONTEXT:  PL/pgSQL function orhpans_active_clean_table(character varying,integer) line 42 at
FETCH
        PL/pgSQL function orhpans_active_removal() line 31 at assignment
        PL/pgSQL function ajf_backup(integer) line 39 at assignment

Can a FETCH fail if the table is locked? The FETCH is stuck for the <statement_timeout> time.

Should I  lock all tables involved with the query?

Any specific time-out on the fetch? Or should I use the general statement-timeout?

I mean move from regular programming mode to paranoidic mode....

The failure is inconsistent.. Never fails in PG 11.5, but fails in PG9.5.5 about once a week...

I need a full understanding of the problem in order to force big,slow customers to migrate to PG11.5.

Thanks

Danny

"Abraham, Danny" <danny_abraham@bmc.com> writes:
> 2021-04-09 08:00:08.692 IDTERROR:  canceling statement due to statement timeout

I don't know why you would think that a statement timeout is somehow
the fault of the refcursor variable you used.  You need to look at
whether your timeout is a sane value, and if so, why it is that
something else is blocking your query for longer than that.
Looking into pg_locks might help identify what the "something else"
is.

            regards, tom lane

I speculate that I am in the good old problem .. of a very slow plan of NOT IN (We used to convert it to NOT EXISTS in
V8....).

Is this planner issue still in V9? Has the planner fixed for it in V10?

Thanks

Danny

On 4/11/21 1:02 PM, Abraham, Danny wrote:
> 2021-04-09 08:00:08.692 IDTERROR:  canceling statement due to statement timeout
> 2021-04-09 08:00:08.692 IDTCONTEXT:  PL/pgSQL function orhpans_active_clean_table(character varying,integer) line 42
atFETCH
 
>          PL/pgSQL function orhpans_active_removal() line 31 at assignment
>          PL/pgSQL function ajf_backup(integer) line 39 at assignment
>
> Can a FETCH fail if the table is locked? The FETCH is stuck for the <statement_timeout> time.
>
> Should I  lock all tables involved with the query?
>
> Any specific time-out on the fetch? Or should I use the general statement-timeout?
>
> I mean move from regular programming mode to paranoidic mode....
>
> The failure is inconsistent.. Never fails in PG 11.5, but fails in PG9.5.5 about once a week...
>
> I need a full understanding of the problem in order to force big,slow customers to migrate to PG11.5.

9.5.21 would be an important step.  Heck, it might solve the problem.

-- 
Angular momentum makes the world go 'round.