Re: Should SET ROLE inherit config params?

Gregory Stark wrote:
> Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com> writes:
>
>> On Fri, Mar 13, 2009 at 2:39 AM, Josh Berkus <josh(at)agliodbs(dot)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