Skip site navigation (1) Skip section navigation (2)

Re: proposal: set GUC variables for single query

From: Thom Brown <thom(at)linux(dot)com>
To: Jan Urbański <wulczer(at)wulczer(dot)org>
Cc: Postgres - Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: set GUC variables for single query
Date: 2011-10-16 15:50:57
Message-ID: CAA-aLv7EzhrEpWK=GCy3kFPhKgb7K=VPX1DmAHwD-ZZk5BbJig@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 16 October 2011 16:44, Jan Urbański <wulczer(at)wulczer(dot)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

In response to

pgsql-hackers by date

Next:From: Jan UrbańskiDate: 2011-10-16 15:57:06
Subject: Re: proposal: set GUC variables for single query
Previous:From: Florian PflugDate: 2011-10-16 15:50:28
Subject: Re: LIMITing number of results in a VIEW with global variables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group