[Bizgres-general] [ENG] Re: Statement Queuing take II - Resource Scheduling

[Mark Kirkwood]

Alon Goldshuv wrote:
> Hi Mark,
>   
>
> As far as I recall regarding cursors (I may need to refresh my memory a bit)
> exec_simple_query() followed by a PortalRun() later on will be executed once
> per utility statement and will take care of running both the utility
> statement (i.e: FETCH) and the actual inner statement (SELECT). That
> includes the transactional statements that must surround a cursor and the
> actual cursor commands.
>
> For example (only main routines included):
>
> DECLARE->Exec_simple_query->PortalRun->PortalStart->executorStart
> FETCH->Exec_simple_query->PortalRun->PortalRunSelect->executorRun
> FETCH->Exec_simple_query->PortalRun->PortalRunSelect->executorRun
> FETCH->Exec_simple_query->PortalRun->PortalRunSelect->executorRun
> ..
> ..
> ..
> CLOSE->Exec_simple_query->PortalRun->executorEnd
>
>
> So during the life of a cursor PortalRun will be executed several times.
> This is probably what you meant but I am just making sure...
>  
>
>   

Thanks Alon - in fact very timely, as I'm delving into cursors in more 
detail today!

The original comment was referring to the situation where multiple 
statements are passed in one go to the backend (e.g. in Java):

  st = db.createStatement();
  st.execute("select 1 from tenk1 a cross join tenk1 b; select 1 from 
tenk1");

In this case PortalRun is executed twice - and the resource lock is 
obtained twice.


Now with respect to cursors, as you noted PortalRun is run several times 
(e.g. DECLARE CURSOR, FETCH, CLOSE). I am currently coping with this by:

- taking the resource lock at DECLARE, and *not* releasing it at the end 
of PortalRun
- not taking a lock for FETCH/MOVE
- also not taking a lock for CLOSE - but setting a flag , so that the 
lock held from DECLARE will be released after *this* PortalRun.

This logic seems to work fine, however there is a catch:

As cursors are handled as UTILITY statements, they are not planned by 
the time PortalRun begins (unlike SELECT, UPDATE, INSERT, DELETE) - this 
is done *much* later - part way through PortalRun by ProcessUtility. As 
I take the locks just *before* PortalRun, it means that I can't get any 
plan related info for them.

I'm currently working around this by calling the planner in the lock 
function for the DECLARE CURSOR - of course this means that it gets done 
*again* in ProcessUtility...

FWIW - I don't see why cursors can't be planned at the same time as  
other  (non utility)  statements - the code currently calls 
pg_plan_queries, which just returns NULL for all utility type 
statements, but it does not seem unreasonable to be a bit smarter here...

Cheers

Mark