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

[Alon Goldshuv]

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

Take a look at the design doc on the intranet (under the mpp kernel team
page) - it includes some notes about cursors in postgresql and MPP, it may
save you some time.

> 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.

True. I am not sure it's relevant but in some cases a single (and simple)
statement may behave differently in different places. For example PL/pgSQL
FOR loops automatically use a cursor internally to avoid memory problems,
even if a cursor was not declared. I guess when you support resource
scheduling for cursors you automatically take care of that so it's ok.

> 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.

Keep in mind that:

- in between FETCHes other non related cursor commands may be executed.
- there can be many cursors running, just waiting to be retrieved from
shared memory when it's their turn to FETCH, and then wait for the next one.
I guess this will mean 1 lock for every live cursor.
- cursor doesn't always get closed by a CLOSE command. If the transaction is
finished the cursor is closed automatically. So maybe PortalDrop is a
strategic place to look at (?)

> 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.

That's correct. The DECLARE/FETCH/CLOSE are utility statements that run in
an "unnamed" or "outer" portal. That portal runs PortalRun() and sees that
this is a cursor and then is starts (in the case of DECLARE) a new "named"
or "inner" portal (or in the case of FETCH it just retrieves the existing
one from shared memory) to run the actual SELECT. I talk about it in more
detail in the design doc if you're interested.

> 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...

Good question. Maybe they just wanted to make a clear separation between the
utility statement execution and the actual SELECT, so that the plan for the
SELECT is done only in the inner portal and kept in memory for future
fetches. Just a guess...

Regards,
Alon.