Re: Should SET ROLE inherit config params?

Gregory Stark wrote:
> Guillaume Smet <guillaume.smet@gmail.com> writes:
> 
>> On Fri, Mar 13, 2009 at 2:39 AM, Josh Berkus <josh@agliodbs.com> wrote:
>>> SET ROLE special WITH SETTINGS
>>>
>>> ... or similar; I'd need to find an existing keyword which works.
>> Perhaps something like "SET ROLE special NEW SESSION;".
>>
>> It solves a problem mentioned by Tom as it's very clear that it's a
>> new session so when you reset the settings to what they were at
>> session start, you take the default settings of special.
> 
> So this is just syntactic sugar for 
> 
> SET ROLE;
> RESET ALL;
> 
> Or is it more or less?

No, actually, since RESET ALL does not adopt the config settings of your 
current group role, but only the login role you logged in with, e.g.:

postgres=# alter role manson set work_mem = '1MB';
ALTER ROLE
postgres=# \c - charles
You are now connected to database "postgres" as user "charles".
postgres=> show work_mem; work_mem
---------- 2MB
(1 row)

postgres=> set role manson;
SET
postgres=> reset all;
RESET
postgres=> show work_mem; work_mem
---------- 2MB


I'd like to have that 2nd work_mem call to show "manson's" work_mem, or 1MB.

What I want to be able to do is to set different bunches of resource 
management settings for various non-login inherited roles, and be able 
to choose profiles via a SET ROLE.  The reason to do this, btw, instead 
of defining various login roles, is that different login roles can't 
share the same connection pool.

--Josh