Обсуждение: 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
Jan Urbański <wulczer@wulczer.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@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
On 16/10/11 17:49, Tom Lane wrote: > Jan Urbański <wulczer@wulczer.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@wulczer.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@2ndQuadrant.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@sss.pgh.pa.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@2ndQuadrant.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@sss.pgh.pa.us> wrote: > Dimitri Fontaine <dimitri@2ndQuadrant.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@gmail.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@gmail.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@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.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@sss.pgh.pa.us> wrote: >> Dimitri Fontaine <dimitri@2ndQuadrant.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
Jan Urbański <wulczer@wulczer.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