Re: synchronous_commit: Developer's View

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(at)fetter(dot)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