[Bizgres-general] Statement Queuing take II - Resource Scheduling

[Mark Kirkwood]

Josh Berkus wrote:
> Jim,
>
>   
>> I'm thinking that for the first pass maybe we should only look at
>> whatever role you have active at the time. 
>>     
>
> Yes.  By "lowest role in the tree" I meant "partents of the role currently 
> active".  As in, do not try to do anything fancy with recursive analysis of 
> the parent roles.
>
> Hmm.  I can see some problems with that too.  How are parameters set for 
> parent roles (like SEARCH_PATH) currently handled?
>
>   

Looks to me like login role overrides role settings in that case (i.e 
user A does SET ROLE b - but search_path stays at what user A has 
configured).

Also, I'm thinking that having to set limit parameters individually for 
each (login) role is painful - plus it becomes hard to know what your 
effective management limits actually are. 

A possibility is to create a new object (say) "resource pool", and 
attach the limits to *them*, and roles can specify one resource pool - 
and only the current role's resource pool is *ever* used by the 
scheduler code.

This means instead of having to specify limits for each (login) role (at 
least), you can have a few resource pools and specify one of them for 
each role . This gets us back to a sane amount of management, plus 
reasonable flexibility. A new catalog object ("resource pool" or 
something) is then required.

>> Or, take the minimum of all 
>> limits that apply to roles that are directly INHERITed by the current
>> role, if SET ROLE hasn't been used.
>>     
>
> No.  Too complicated, for no real benefit.
>
>   
Yeah - I'm thinking that too.
>> Right - The SET ROLE is a bit alarming, as it means users can 'switch
>> off' their resource limits in some cases. 
>>     
>
> I see this as a feature and not a flaw.  Think of the smart DBA who wants to 
> deliberately run some queries as "joe user" and others as "report writer".  
> He can SET ROLE, run some queries, SET ROLE again, and run the others.
>
> It does mean that you have to be careful what roles your users belong to, but 
> when is that not the case?
>
>   
Yeah, agreed.

Cheers

Mark