Thread: synchronous_commit: Developer's View
Async Commit is a useful feature, yet it requires some additional application code to be added to appropriate transactions. That code is then clearly version dependent, which may not always be desirable. It would be good if there was a way to make that a DBA-controllable setting, much the same as we might execute the following command: ALTER USER jimbob SET work_mem = ... The above commmand allows application SQL to be tuned without changes to the application code itself. So I'm thinking, is there a way to decorate a transaction in such a way that only that transaction knows to do SET LOCAL synchronous_commit = off Perhaps it would be possible to do this ALTER FUNCTION fubar SET synchronous_commit = off; So that any invocation of the function would automatically set all of the appropriate parameters prior to execution. Why do I mention this now? Well, on the 8.3 patch status list is the concept of "per function search_path". search_path is a parameter, so per function search path would seem to imply setting parameters on a per function basis, exactly what I'm suggesting for enhancing async commit. Per function parameters could then also be used for other "Statement Behaviour" parameters and other related ones, such as ... default_tablespace, temp_tablespaces, default_transaction_isolation, statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings, regex_flavour, xmlbinary and xmloption. To do this, we would need to add a column to pg_proc named and defined:procconfig text[] named similarly to the rolconfig column of pg_authid This would then give us the flexibility to implement per function search_path as well as the above mentioned uses. Thoughts? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
"Simon Riggs" <simon@2ndquadrant.com> writes: > Perhaps it would be possible to do this > > ALTER FUNCTION fubar SET synchronous_commit = off; > > So that any invocation of the function would automatically set all of > the appropriate parameters prior to execution. The problem here is that functions don't do commit. So this wouldn't have any effect unless the setting stayed in effect after the end of the function call. > Why do I mention this now? Well, on the 8.3 patch status list is the > concept of "per function search_path". search_path is a parameter, so > per function search path would seem to imply setting parameters on a per > function basis, exactly what I'm suggesting for enhancing async commit. There's a big difference since search_path affects the semantics of the function itself. The point of having per-function search_path is to ensure that the function is correctly interpreted in a consistent fashion. Not to give extra flexibility to change the search_path later. > Per function parameters could then also be used for other "Statement > Behaviour" parameters and other related ones, such as ... > > default_tablespace, temp_tablespaces, default_transaction_isolation, > statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings, > regex_flavour, xmlbinary and xmloption. Of those standard_conforming_strings and regex_flavour (and no idea about the xml ones) seem like good matches for binding per-function based on the defining context. The others are more DBA-type things which you would expect to be global. If you want a specific function to create tables in a specific tablespace then presumably you would just write it to do so. A DBA would be pretty surprised to find his setting for default_tablespace doesn't have any effect on pre-existing functions which didn't specify a tablespace. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > Async Commit is a useful feature, yet it requires some additional > application code to be added to appropriate transactions. That code is > then clearly version dependent, which may not always be desirable. > > It would be good if there was a way to make that a DBA-controllable > setting, much the same as we might execute the following command: > > ALTER USER jimbob SET work_mem = ... I don't understand that example - ALTER USER his_work_is_not_that_important SET synchronous_commit=off is possible in CVS HEAD... > The above commmand allows application SQL to be tuned without changes to > the application code itself. > > So I'm thinking, is there a way to decorate a transaction in such a way > that only that transaction knows to do > SET LOCAL synchronous_commit = off > > Perhaps it would be possible to do this > > ALTER FUNCTION fubar SET synchronous_commit = off; > > So that any invocation of the function would automatically set all of > the appropriate parameters prior to execution. But functions cannot commit toplevel transactions, and subtransaction commits are not WAL-logged anyway, so what would that do? > Why do I mention this now? Well, on the 8.3 patch status list is the > concept of "per function search_path". search_path is a parameter, so > per function search path would seem to imply setting parameters on a per > function basis, exactly what I'm suggesting for enhancing async commit. > > Per function parameters could then also be used for other "Statement > Behaviour" parameters and other related ones, such as ... > > default_tablespace, temp_tablespaces, default_transaction_isolation, > statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings, > regex_flavour, xmlbinary and xmloption. You can already do BEGIN; SET LOCAL myguc=tempvalue <some query> END; in pl/pgsql, and the value will be restored to it's original value after the END. Or at least this is how I interpret the docs - I can't remember ever trying that though. So at least for the pl/pgsql case, it seems easy enough to temporarily change GUCs already. For other PLs, things might be different though - I wouldn't know, I have never really used them... greetings, Florian Pflug
"Florian G. Pflug" <fgp@phlo.org> writes:
> ... So at least for the pl/pgsql case, it seems easy enough to temporarily
> change GUCs already. For other PLs, things might be different though -
> I wouldn't know, I have never really used them...
It's definitely possible, but it's inconvenient and slow (slow because
you have to run a subtransaction, which ain't cheap). I think Simon
might have a good point about generalizing the proposed "set the search
path" facility to instead be "set any GUC for the duration of this
function". He's definitely all wet about the usefulness of that for
synchronous_commit, but as Greg pointed out, there are other GUCs
besides search_path that can break a function's expectations.
regards, tom lane
On Thu, Aug 30, 2007 at 10:34:42PM +0100, Simon Riggs wrote: > Async Commit is a useful feature, yet it requires some additional > application code to be added to appropriate transactions. That code is > then clearly version dependent, which may not always be desirable. > > It would be good if there was a way to make that a DBA-controllable > setting, much the same as we might execute the following command: > > ALTER USER jimbob SET work_mem = ... > > The above commmand allows application SQL to be tuned without changes to > the application code itself. > > So I'm thinking, is there a way to decorate a transaction in such a way > that only that transaction knows to do > SET LOCAL synchronous_commit = off > > Perhaps it would be possible to do this > > ALTER FUNCTION fubar SET synchronous_commit = off; > > So that any invocation of the function would automatically set all of > the appropriate parameters prior to execution. > > Why do I mention this now? Well, on the 8.3 patch status list is the > concept of "per function search_path". search_path is a parameter, so > per function search path would seem to imply setting parameters on a per > function basis, exactly what I'm suggesting for enhancing async commit. > > Per function parameters could then also be used for other "Statement > Behaviour" parameters and other related ones, such as ... > > default_tablespace, temp_tablespaces, default_transaction_isolation, > statement_timeout, gin_fuzzy_search_limit, standard_conforming_strings, > regex_flavour, xmlbinary and xmloption. > > To do this, we would need to add a column to pg_proc named and defined: > procconfig text[] > named similarly to the rolconfig column of pg_authid > > This would then give us the flexibility to implement per function > search_path as well as the above mentioned uses. > > Thoughts? While we're at it, it would be very nice to be able to set default per-role, per-database settings. One obvious example of this is search_path, where in general no two databases in a cluster need share any common user-defined schema names. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> While we're at it, it would be very nice to be able to set default
> per-role, per-database settings.
Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET?
regards, tom lane
On Sat, Sep 01, 2007 at 10:51:48PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > While we're at it, it would be very nice to be able to set default > > per-role, per-database settings. > > Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET? You can't set both at once. Let's imagine that you have two databases in a cluster: foo with schemas foo_1 and foo_2, and bar with schemas bar_1 and bar_2. There's no way (currently) to set a ROLE's foo search_path to foo_1,foo_2 and its bar search_path to bar_1, bar_2. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Sat, Sep 01, 2007 at 10:51:48PM -0400, Tom Lane wrote:
>> Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET?
> You can't set both at once.
Oh, you mean the cross-product case. Sorry, that was on the wish-list
already, but no one saw it as a security issue, so it's not happening
for 8.3.
regards, tom lane
On Sat, Sep 01, 2007 at 11:07:55PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Sat, Sep 01, 2007 at 10:51:48PM -0400, Tom Lane wrote: > >> Er ... what deficiency do you see in ALTER ROLE SET and ALTER DATABASE SET? > > > You can't set both at once. > > Oh, you mean the cross-product case. Sorry, that was on the wish-list > already, but no one saw it as a security issue, so it's not happening > for 8.3. Sorry. I assumed we were talking about 8.4 stuff. Cheers, David -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
On Thu, 2007-08-30 at 21:00 -0400, Tom Lane wrote: > "Florian G. Pflug" <fgp@phlo.org> writes: > > ... So at least for the pl/pgsql case, it seems easy enough to temporarily > > change GUCs already. For other PLs, things might be different though - > > I wouldn't know, I have never really used them... > > It's definitely possible, but it's inconvenient and slow (slow because > you have to run a subtransaction, which ain't cheap). I think Simon > might have a good point about generalizing the proposed "set the search > path" facility to instead be "set any GUC for the duration of this > function". > He's definitely all wet about the usefulness of that for > synchronous_commit, but as Greg pointed out, there are other GUCs > besides search_path that can break a function's expectations. Not too sure what "all wet" means, but the imagery is great. :-) As I said, I'm looking for a way to decorate a specific transaction without changing application code. Setting it on a function works fine as long as the function was invoked as a top-level procedure call in its own implicit transaction, which is common usage. Clearly, this doesn't really make sense for non-procedural functions such as md5(), though it does for things like record_vehicle_position() or ad_impression(). -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com