4 ms·
By default, psql will fetch the entire result first then print it out. This is usually fine until you need to fetch more rows than your client's RAM. To fix tha
by mac-chaffee 4y ago
By default, psql will fetch the entire result first then print it out. This is usually fine until you need to fetch more rows than your client's RAM. To fix that, you can run "\set FETCH_COUNT 10000". Then psql will use a cursor which only fetches 10000 rows at a time, using a constant amount of RAM.
This can be handy if e.g. you typically run psql on the server/container running postgres itself and you want to avoid an accidentally large query from oomkilling your database. You can set FETCH_COUNT per database and per user with ALTER: https://www.postgresql.org/docs/current/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION https://www.postgresql.org/docs/current/config-setting.html#...
- anarazel 4y agoFETCH_COUNT is a psql side setting, not something you can configure server side.
- koolba 4y agoI think GP is referring to running psql server side on the database server itself (i.e., connecting to localhost). Running a gigantic result will keep allocating memory for the result and potentially OOM the server as it’s competing for resources with the database itself right?
- anarazel 4y agoMy point is just that you can't set FETCH_COUNT with ALTER etc (as the post I was replying to suggested), because the server doesn't know anything about the parameter, as it just affects psql.
- mac-chaffee 4y agoOh yeah seems that FETCH_COUNT is special and is not a regular setting. All normal settings that you can SET can also be set per-DB and per-user: https://www.postgresql.org/docs/current/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION https://www.postgresql.org/docs/current/config-setting.html#...
- anarazel 4y agoIt's just a clientside knob for psql. You can do something like it in any client.