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

[Alon Goldshuv]

Hi Mark,

>> 1/ Identifying statement start and stop appears to be a little subtle,
>> as the code works with Lists of statements that are parsed and planned
>> and executed - we want to be careful not to take a lock for side-effect
>> statements such as triggered or selected SQL procedures (log_statement
>> and log_duration logic will help here). In addition cursors need to be
>> handled (suggest taking lock on the EXECUTE event, and holding the lock
>> across all the FETCHes until the cursor is closed)? Also some simple
>> sanity checking is required - we don't want to take a lock for
>> statements like BEGIN or COMMIT.
>>   
> 
> I've been looking at this and made some progress. I'm thinking that the
> places to do this are:
> 
> 1/ Simple query message receipt by the backend (exec_simple_query()).
> 2/ Execution message receipt by the backend (exec_execute_message()).
> 
> In both cases, the point to actually take the actual lock is immediately
> before the corresponding portal is run (i.e before the PortalRun()
> call). Similarly, the lock release will happen just after the portal is run.
> 
> This seems to work quite well, in that side-effect statements, functions
> and triggers do *not* cause us to re-take the locks.
> 
> Clients submitting multiple statements in one message *do* end up
> requesting the lock for each one in turn - this is ok I think, as it is
> in keeping with the way the backend works (i.e. a PortalRun() per
> *actual* statement).
> 
> Thoughts?

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

Alon.