Re: problems with set_config, work_mem, maintenance_work_mem, and sorting

Quoting Jon Nelson:
"""
The config shows 128MB for work_mem and 2GB for maintenance_work_mem.
Why does PostgreSQL /sometimes/ use the globally-configured values and
sometimes use the values that come from the connection?
Am I wrong in misunderstanding what 'session' variables are? I thought
that session (versus transaction) config items were set for /all/
transactions in a given backend, until changed or until that backend
terminates. Is that not so?
"""

Could it be that the transaction which does the set_config is rolled back? If that is
the case, the set_config is rolled back, too. However, if the transaction commits,
then the set_config should be in effect for the whole session. It seems this is not
documented at all for set_config, just for SET SQL command.

I think it would be nice to have a way to force the connection to use the provided
settings even if the transaction in which they are done is rolled back. In single statement
if possible. Otherwise you might be forced to do a transaction just to be sure the SET
is actually in effect for the connection's life-time.

Django was bitten by this for example, it is now fixed by using this:
https://github.com/django/django/blob/master/django/db/backends/postgresql_psycopg2/base.py#L189

- Anssi