Working memory on the connection string for postgres instance

Question

I'm trying to understand how can I set the work_mem setting on the Postgres(v10) for a single connection/session.

Currently I'm connection to my Postgres Instance in the cloud through a NodeJs app using a query string as showing bellow:

postgres://<usr>:<pwd>@<host>:5432/<db_instance>;

I know that you can set parameters in the connection string such as connection_timeout and so on, but I can't find anyware how to set the work_mem for the current connection/session.

• mustaccio: What makes you think you can "set the work_mem for the current connection/session"? It's a server, not session configuration parameter.
• Erwin Brandstetter: @mustaccio: Many parameters can be set per session. See: postgresql.org/docs/current/config-setting.html#id-1.6.6.4.5 I just don't know how to do this for NodeJs.
• user1822: Just run set work_mem=... after your connection has been established. Or if you want that permanently change the user with alter user
• bmvr: @a_horse_with_no_name I've done this, as a workround, but I did want to put it directly on the connection string, looks like it's impossible for nodejs connectors. Thx

Answer 1

Only a select list of parameters are allowed directly in the connection string. Others must be imbedded via the "options" parameter. This leads to much ugliness and escaping and quoting challenges.

Here is an example that sets work_mem to 1234 kB:

psql "postgresql://jjanes@localhost/jjanes?connect_timeout=10&options=-c work_mem%3D1234"

• user1822: I don't think a connection string for Node.js supports the -c parameter
• bmvr: Hello, I've tried it, didn't work. I had to set work_mem = 'xxMB' before when I opened the connection, this was not what I wanted but well.. it works.
• jjanes: Ok, -c might be libpq specific. Can you tell what library within nodejs you are using to connect? There seems to be several. Abstracting the connection logic to a routine which can do housekeeping commands before handing the connection back is probably a better design anyway. Rather than stuffing all the logic into a connection string which then gets fired from multiple parts of the code.