Re: proposal: set GUC variables for single query

On 16 October 2011 16:44, Jan Urbański <wulczer@wulczer.org> wrote:
> 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?

What about SET LOCAL?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company