Re: synchronous_commit: Developer's View

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