Re: proposal: set GUC variables for single query

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, Jan Urbański <wulczer(at)wulczer(dot)org>, Postgres - Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: set GUC variables for single query
Date: 2011-10-17 00:53:14
Message-ID: CA+TgmoYD7jE21jJ0==M-x_wZrYthTM9sX46iwbvT9R1A0U91SA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2011-10-17 00:59:30 Re: proposal: set GUC variables for single query
Previous Message Hitoshi Harada 2011-10-16 23:47:29 Re: Underspecified window queries in regression tests