Change work_mem for one user

Wenjun Che <wenjun@openfin.co> writes:

We are running pg 10.10 on AWS RDS. I want to increase work_mem for one
user with following command:
ALTER ROLE test_user SET work_mem TO '50 MB';
After I run the command and log in as test_user, "show work_mem" still
shows the default 4MB.

Hmm, works for me:

regression=# create user test_user;
CREATE ROLE
regression=# ALTER ROLE test_user SET work_mem TO '50 MB';
ALTER ROLE
regression=# show work_mem;
work_mem
----------
4MB
(1 row)

regression=# \c - test_user
You are now connected to database "regression" as user "test_user".
regression=> show work_mem;
work_mem
----------
50MB
(1 row)

Perhaps you also have a setting at the per-database level, or
per-user-and-database level? The latter would definitely override
a per-user setting; I don't recall offhand whether per-database
overrides per-user. Look into pg_db_role_setting to see what
there is.

regards, tom lane

Hello Tom

Thank you very much for the quick response.

I just realized the issue is caused by the tool, DataGrid, I am using. In
DataGrid, when I close a tab, it does not actually disconnect from the
database so it does not re-login when I open a new tab.

Again. thank you for the help.

On Tue, Jan 26, 2021 at 9:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Wenjun Che <wenjun@openfin.co> writes:

We are running pg 10.10 on AWS RDS. I want to increase work_mem for one
user with following command:
ALTER ROLE test_user SET work_mem TO '50 MB';
After I run the command and log in as test_user, "show work_mem" still
shows the default 4MB.

Hmm, works for me:

regression=# create user test_user;
CREATE ROLE
regression=# ALTER ROLE test_user SET work_mem TO '50 MB';
ALTER ROLE
regression=# show work_mem;
work_mem
----------
4MB
(1 row)

regression=# \c - test_user
You are now connected to database "regression" as user "test_user".
regression=> show work_mem;
work_mem
----------
50MB
(1 row)

Perhaps you also have a setting at the per-database level, or
per-user-and-database level? The latter would definitely override
a per-user setting; I don't recall offhand whether per-database
overrides per-user. Look into pg_db_role_setting to see what
there is.

regards, tom lane

--
Wenjun Che
VP of Engineering | OpenFin
wenjun@openfin.co

*Move Fast. Break Nothing.*
www.openfin.co | @openfintech