Skip site navigation (1) Skip section navigation (2)

Re: synchronous_commit: Developer's View

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: synchronous_commit: Developer's View
Date: 2007-08-31 00:35:01
Message-ID: 46D76235.50601@phlo.org (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-08-31 01:00:39
Subject: Re: synchronous_commit: Developer's View
Previous:From: Gregory StarkDate: 2007-08-30 22:33:32
Subject: Re: synchronous_commit: Developer's View

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group