BUG #16248: ALTER SYSTEM quoting of values does not work as expected
The following bug has been logged on the website:
Bug reference: 16248
Logged by: gabrielle roth
Email address: gabrielle@pdx.postgresql.us
PostgreSQL version: 11.6
Operating system: Ubuntu
Description:
Hi all!
I tried out `ALTER SYSTEM` for the first time last week, and ran into
something that confused me:
tl;dr:
ALTER SYSTEM SET log_statement = 'all'; -- quotes required around the new
value
vs
ALTER SYSTEM SET shared_preload_libraries = pg_stat_statements,plprofiler;
-- requires the new value *not* be quoted, this is what's confusing me
How I got there:
Initially, I tried quotes around the value, as that's the way I'd write it
in postgresql.conf:
ALTER SYSTEM SET shared_preload_libraries =
'pg_stat_statements,plprofiler';
When I restarted my database to apply the change, it failed with this error
(note the value now has double quotes)
FATAL: could not access file "pg_stat_statements,plprofiler": No such file
or directory
And this is what ended up in postgresql.auto.conf:
shared_preload_libraries = '"pg_stat_statements,plprofiler"'
From the docs for ALTER SYSTEM
(https://www.postgresql.org/docs/11/sql-altersystem.html):
"Values can be specified as string constants, identifiers, numbers, or
comma-separated lists of these, as appropriate for the particular
parameter."
From the docs for "Setting parameters"
(https://www.postgresql.org/docs/11/config-setting.html):
"String: In general, enclose the value in single quotes, doubling any single
quotes within the value. Quotes can usually be omitted if the value is a
simple number or identifier, however."
I eventually got help from a friend, who tipped me off that the value for
shared_preload_libraries should not be quoted.
That seems weird to me because of the requirement for quoting on other
settings, e.g. log_statement.
Is this expected behavior? If so, can we get some examples in the docs to
help folks figure out the correct quoting rules?
Thank you!
gabrielle
PG Bug reporting form <noreply@postgresql.org> writes:
tl;dr:
ALTER SYSTEM SET log_statement = 'all'; -- quotes required around the new
value
The reason for that is that ALL is a reserved word in SQL.
ALTER SYSTEM SET shared_preload_libraries = pg_stat_statements,plprofiler;
-- requires the new value *not* be quoted, this is what's confusing me
Well, you could quote the list elements individually, eg
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements','plprofiler';
But this:
ALTER SYSTEM SET shared_preload_libraries =
'pg_stat_statements,plprofiler';
says you just want one list element that happens to include a comma.
No, the syntax rules here are not the same as they are in postgresql.conf.
Is this expected behavior? If so, can we get some examples in the docs to
help folks figure out the correct quoting rules?
AFAICS the documentation statements you quoted are accurate.
regards, tom lane