Hi,
this idea has cropped up last PGCon - the ability to set GUC variables
for the duration of a single query. It would work by setting the GUCs
for the duration of the query and setting them back to what they were
after it has terminated. By "setting them back" I mean respecting the
previously set values, regardless of their source (set in run-time,
per-role settings, postgresql.conf settings).
An example of where this would be useful: an application maintains a
persistent connection to the database and answers requests for data from
a bunch of clients. Each connected client has a preferred timezone and
would like to get results in that timezone. Currently the application
has to either sprinkle each query with AT TIME ZONE or wrap the queries
in "BEGIN; SET LOCAL TIMEZONE ..; <query>; COMMIT". It gets more complex
when things like pgbouncer come into play.
Another example is a one-off query that should use a different
statement_timeout than the server has configured or a REINDEX command
that would like to use more maintenance_work_mem.
It mostly falls into the realm of syntax sugar, but as more than one
person felt it's a good idea, I thought I'd float it around here.
I poked a little bit at the grammar to see where could it fit and didn't
have much success of doing it without a new reserved keyword. Supposing
the idea gets some traction, any suggestions for the syntax?
Cheers,
Jan