0

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.

  • What makes you think you can "set the work_mem for the current connection/session"? It's a server, not session configuration parameter. – mustaccio May 7 at 16:25
  • 1
    @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. – Erwin Brandstetter May 7 at 17:18
  • 1
    Just run set work_mem=... after your connection has been established. Or if you want that permanently change the user with alter user – a_horse_with_no_name May 7 at 18:08
  • @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 – bmvr May 8 at 10:21
0

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"
  • I don't think a connection string for Node.js supports the -c parameter – a_horse_with_no_name May 7 at 18:09
  • 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. – bmvr May 8 at 10:20
  • 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. – jjanes May 8 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.