Re: [HACKERS] proposal: set GUC variables for single query
On 17/10/11 02:53, Robert Haas wrote:
On Sun, Oct 16, 2011 at 4:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Dimitri Fontaine dimi...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
On 16 October 2011 16:44, Jan Urbański wulc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
On 16/10/11 17:49, Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
Jan Urbański wulc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
Dimitri Fontaine dimi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
Tom Lane t...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
Dimitri Fontaine dimi...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
On Sun, Oct 16, 2011 at 4:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Dimitri Fontaine dimi...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
On 10/16/2011 08:59 PM, Tom Lane wrote: Robert Haasrobertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: set GUC variables for single query
On Sun, Oct 16, 2011 at 8:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
