Re: proposal: set GUC variables for single query

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

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= <wulczer(at)wulczer(dot)org> writes:
> 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.

Doesn't SET LOCAL cover this use-case pretty well already?

regards, tom lane

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

On 16/10/11 17:49, Tom Lane wrote:
> =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= <wulczer(at)wulczer(dot)org> writes:
>> 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.
>
> Doesn't SET LOCAL cover this use-case pretty well already?

It does to a certain degree. If you have a bunch of statements in a
transaction and want to execute one of them with a different timezone
setting, you have to do the SET/RESET dance. In theory you should also
first grab the current value to set it back afterwards, in case someone
else did SET LOCAL before you, but I'll admin that's far-fetched.

The main use case would be apps running behing pgbouncer and using
statement pooling, and plain convenience.

I'd find it useful myself, but for now I'm making do with SET LOCAL and
it works fine. I'm bringing it up because it appears in the TODO created
at the PL Summit:

* Further discussion of per-statement config parameters for things
like timezone - Jan Urbanski

Tryin' to do my bit and all ;)

Jan

Jan Urbański <wulczer(at)wulczer(dot)org> writes:
> 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).

+1 on the use case, allowing to do that in the statement itself would be
a nice convenience.

> 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?

I think it would fit quite well within our extending of the WITH syntax.

WITH
work_mem = '1GB',
timezone = 'Europe/Amsterdam',
foo AS (
SELECT something
)
SELECT toplevel FROM foo;

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> I think it would fit quite well within our extending of the WITH syntax.

> WITH
> work_mem = '1GB',
> timezone = 'Europe/Amsterdam',
> foo AS (
> SELECT something
> )
> SELECT toplevel FROM foo;

That looks pretty non-future-proof to me. WITH is a SQL-standard
syntax, it's not an extension that we control.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> That looks pretty non-future-proof to me. WITH is a SQL-standard
> syntax, it's not an extension that we control.

Now that you mention it, the following might actually already work:

WITH settings AS (
SELECT set_config('timezone', 'Europe/Amsterdam', t),
set_config('work_mem', '1 GB', t)
),
foo AS (
SELECT …
)
INSERT INTO bar SELECT * FROM foo;

So maybe what we need is to only change the is_local parameter to the
function set_config() so that we can have the setting last for only the
current statement?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Now that you mention it, the following might actually already work:

> WITH settings AS (
> SELECT set_config('timezone', 'Europe/Amsterdam', t),
> set_config('work_mem', '1 GB', t)
> ),
> foo AS (
> SELECT
> )
> INSERT INTO bar SELECT * FROM foo;

Only for small values of "work" ... you won't be able to affect planner
settings that way, nor can you assume that that WITH item is executed
before all else. See recent thread pointing out that setting values
mid-query is unsafe.

regards, tom lane

On Sun, Oct 16, 2011 at 4:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
>> Now that you mention it, the following might actually already work:
>
>>  WITH settings AS (
>>    SELECT set_config('timezone', 'Europe/Amsterdam', t),
>>           set_config('work_mem', '1 GB', t)
>>  ),
>>       foo AS (
>>    SELECT …
>>  )
>>  INSERT INTO bar SELECT * FROM foo;
>
> Only for small values of "work" ... you won't be able to affect planner
> settings that way, nor can you assume that that WITH item is executed
> before all else.  See recent thread pointing out that setting values
> mid-query is unsafe.

I previously floated the idea of using a new keyword, possibly LET,
for this, like this:

LET var = value [, ...] IN query

I'm not sure if anyone bought it, but I'll run it up the flagpole
again and see if anyone salutes. I tend to agree with the idea that
SET LOCAL isn't always what you want; per-transaction is not the same
as per-query, and multi-command query strings have funny semantics,
and multiple server round-trips are frequently undesirable; and it
just seems cleaner, at least IMHO.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I previously floated the idea of using a new keyword, possibly LET,
> for this, like this:

> LET var = value [, ...] IN query

> I'm not sure if anyone bought it, but I'll run it up the flagpole
> again and see if anyone salutes. I tend to agree with the idea that
> SET LOCAL isn't always what you want; per-transaction is not the same
> as per-query, and multi-command query strings have funny semantics,
> and multiple server round-trips are frequently undesirable; and it
> just seems cleaner, at least IMHO.

Well, syntax aside, the real issue here is that GUC doesn't have
any notion of a statement-lifespan setting, and adding one would require
adding per-statement overhead; not to mention possibly adding
considerable logical complexity, depending on exactly what you wanted to
define as a "statement". I don't think an adequate case has been
made that SET LOCAL is insufficient.

regards, tom lane

On 10/16/2011 08:59 PM, Tom Lane wrote:
> Robert Haas<robertmhaas(at)gmail(dot)com> writes:
>> I previously floated the idea of using a new keyword, possibly LET,
>> for this, like this:
>> LET var = value [, ...] IN query
>> I'm not sure if anyone bought it, but I'll run it up the flagpole
>> again and see if anyone salutes. I tend to agree with the idea that
>> SET LOCAL isn't always what you want; per-transaction is not the same
>> as per-query, and multi-command query strings have funny semantics,
>> and multiple server round-trips are frequently undesirable; and it
>> just seems cleaner, at least IMHO.
> Well, syntax aside, the real issue here is that GUC doesn't have
> any notion of a statement-lifespan setting, and adding one would require
> adding per-statement overhead; not to mention possibly adding
> considerable logical complexity, depending on exactly what you wanted to
> define as a "statement". I don't think an adequate case has been
> made that SET LOCAL is insufficient.
>
>

I agree. But if we are going to go there I vastly prefer Robert's
suggestion of a separate syntactical structure. Mixing this up with WITH
would just be an awful mess, and cause endless confusion.

cheers

andrew

On Sun, Oct 16, 2011 at 8:59 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I previously floated the idea of using a new keyword, possibly LET,
>> for this, like this:
>
>> LET var = value [, ...] IN query
>
>> I'm not sure if anyone bought it, but I'll run it up the flagpole
>> again and see if anyone salutes.  I tend to agree with the idea that
>> SET LOCAL isn't always what you want; per-transaction is not the same
>> as per-query, and multi-command query strings have funny semantics,
>> and multiple server round-trips are frequently undesirable; and it
>> just seems cleaner, at least IMHO.
>
> Well, syntax aside, the real issue here is that GUC doesn't have
> any notion of a statement-lifespan setting, and adding one would require
> adding per-statement overhead; not to mention possibly adding
> considerable logical complexity, depending on exactly what you wanted to
> define as a "statement".  I don't think an adequate case has been
> made that SET LOCAL is insufficient.

Would it require adding per-statement overhead in every case, or just
when the feature gets used? I suspect the latter, which is no
different from anything else we have. We do already have at least one
other case that seems similar to me: you can apply a setting using
ALTER FUNCTION .. SET; the new value is applied when you enter the
function and restored on exit. I'd imagine that this would have
similar semantics. In terms of what qualifies as a statement, I would
rather imagine that it would only be worthwhile to apply this to
queries rather than fooling around with utility statements. I mean,
it would be nice if it Just Worked Anywhere, but that's likely to be a
lot more work (and grammar conflicts) than we want to deal with.

Anyway, the judgement of whether or not SET LOCAL is sufficient is in
the end a value judgement, and I'm not going to pretend that my
opinion is superior to all others. My personal experience, however,
is that I've never used or wanted SET LOCAL, but I've wanted a
single-statement equivalent a few times. So for me personally, having
this in lieu of SET LOCAL would be an improvement from a usability
perspective. YMMV, of course.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 17/10/11 02:53, Robert Haas wrote:
> On Sun, Oct 16, 2011 at 4:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
>>> Now that you mention it, the following might actually already work:
>>
>>> WITH settings AS (
>>> SELECT set_config('timezone', 'Europe/Amsterdam', t),
>>> set_config('work_mem', '1 GB', t)
>>> ),
>>> foo AS (
>>> SELECT …
>>> )
>>> INSERT INTO bar SELECT * FROM foo;
>>
>> Only for small values of "work" ... you won't be able to affect planner
>> settings that way, nor can you assume that that WITH item is executed
>> before all else. See recent thread pointing out that setting values
>> mid-query is unsafe.
>
> I previously floated the idea of using a new keyword, possibly LET,
> for this, like this:
>
> LET var = value [, ...] IN query

LET was something I thought about, although you'd have to use something
like parenthesis around the GUC assignements because "value" can contain
commas, leading to shift/reduce conflicts (that sucks, unfortunately).

But before whipping out the paint bucket I wanted to see if there's
enough buy-in to justify rehashing the syntax details.

Cheers,
Jan

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= <wulczer(at)wulczer(dot)org> writes:
> On 17/10/11 02:53, Robert Haas wrote:
>> I previously floated the idea of using a new keyword, possibly LET,
>> for this, like this:
>>
>> LET var = value [, ...] IN query

> LET was something I thought about, although you'd have to use something
> like parenthesis around the GUC assignements because "value" can contain
> commas, leading to shift/reduce conflicts (that sucks, unfortunately).

Probably better to forbid commas --- people can always put such values
inside a quoted literal.

regards, tom lane