On Wed, May 4, 2022, at 8:12 AM, Bharath Rupireddy wrote:
How about we provide a sample extension (limiting some important
parameters say shared_buffers, work_mem and so on to some
"reasonable/recommended" limits) in the core along with the
set_config_option_hook? This way, all the people using open source
postgres out-of-the-box will benefit and whoever wants, can modify
that sample extension to suit their needs. The sampe extension can
also serve as an example to implement set_config_option_hook.
I agree with Robert that providing a feature for it instead of a hook is the
way to go. It is not just one or two vendors that will benefit from it but
almost or if not all vendors will use this feature. Hooks should be used for
niche features; that's not the case here.
The commit a0ffa885e47 introduced the GRANT SET ON PARAMETER command. It could
be used for this purpose. Despite of accepting GRANT on PGC_USERSET GUCs, it
has no use. It doesn't mean that additional properties couldn't be added to the
current syntax. This additional use case should be enforced before or while
executing set_config_option(). Is it ok to extend this SQL command?
The syntax could be:
GRANT SET ON PARAMETER work_mem (MIN '1MB', MAX '512MB') TO bob;
NULL keyword can be used to remove the MIN and MAX limit. The idea is to avoid
a verbose syntax (add an "action" to MIN/MAX -- ADD MIN 1, DROP MAX 234, SET
MIN 456).
The other alternative is to ALTER USER SET and ALTER DATABASE SET. The current
user can set parameter for himself and he could adjust the limits. Besides that
the purpose of these SQL commands are to apply initial settings for a
combination of user/database. I'm afraid it is out of scope to check after the
session is established.